connection/login issue after migrating from 6.3.2 to 7.3.0

[pharrukh]

After migrating to the 7.3.0 the connection was failing to get established.

Expected behaviour:

Connection is established and fetching works as usual.

Actual behaviour:

Connection fails with: ConnectionError: Failed to connect to [SERVER_IP]4:1433 - self signed certificate at

Interestingly, when TrustServerCertificate=True flag is used, the error changes to Login failed for user.

Configuration:

Driver=msnodesqlv8
Encrypt=true

Software versions

• NodeJS: 12.x
• node-mssql: 7.3.0
• SQL Server: 2019

[dhensby]

1. if your server is presenting a self signed certificate then it is expected behaviour to receive an error to that affect in v7
2. if the login has failed after allowing the server certificate, then that is usually a credential issue.

Can you connect using msnodesqlv8 directly?

[thorsten]

I have the same issue in a NestJS application with a MS SQL servier with a self signed certificate, it works with v6.4.0, but not with v7.3.0.

[dhensby]

Thanks @thorsten. Have you been able to do any debugging or testing to see what the problem is? I assume you're using msnodesqlv8 driver too?

[thorsten]

@dhensby I will debug it on Monday. How can I check which driver I'm using?

[dhensby]

If you don't know what driver you're using then you're probably using tedious.

Do you import mssql or mssql/msnodesqlv8?

[thorsten]

We import mssql

[dhensby]

Then it is the tedious driver. Interesting this is affecting both.

Can you both supply the actual config being used (sensitive details redacted)

[thorsten]

return <TypeOrmModuleOptions>{
      type: 'mssql',
      host: configService.get('DATABASE_HOST'),
      port: +configService.get('DATABASE_PORT'),
      username: configService.get('DATABASE_USER'),
      password: configService.get('DATABASE_PASSWORD'),
      database: configService.get('DATABASE_NAME'),
      entities: [],
      //logging: 'all',
      synchronize: true,
      pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000,
      },
      options: {
        encrypt: false, // for Azure
        trustServerCertificate: true, // true for local dev / self-signed certs
      },
    };

[dhensby]

It looks like you're using typeorm, so I'm not sure what their support is regarding v7 (I can see their tests run against v6)

The only thing I can see is you have encrypt set to false when maybe it should be true?

I'd encourage you to attempt to authenticate directly with tedious and see if they works, then with mssql and then typeorm to see if there are any errors that are being produced from each of these layers.

[thorsten]

Switched "encrypt" to "true" and tried 7.3.0 again:

ConnectionError: Failed to connect to localhost:1433 - self signed certificate

[thorsten]

TypeORM doesn't support the new mssql/msnodesqlv8 driver: https://github.com/typeorm/typeorm/issues/8063

[dhensby]

@thorsten - now I'm confused; are you trying to use the msnodesqlv8 driver? If you are, then you've had to edit the underlying typreorm coffee as per your linked issue, do your question about how you know if you're using it is not is unusual (I'd have expected it be obvious to you as you were needing coffee changes to typeorm to force the use of it).

As for the error with self-signed certificates, is that when you trust the certificate too?

I think you're going to need to supply me with a reproduction example...

[dhensby]

I've not heard anything from @pharrukh on this to help me resolve the issue - is this now resolved?

As for @thorsten - you're issue is different (different driver, using typeORM, etc), if you need more help please open a new issue with reproduction information

[farrukh-normuradov]

@dhensby thanks for your support. @thorsten thanks for your contribution.

Sorry for my late reply.

@dhensby I will try to connect with msnodesqlv8 now.

[dhensby]

@farrukh-normuradov - let me know how you get on

[farrukh-normuradov]

I still on installing the driver on my MacOS. I am getting Can't open lib 'ODBC Driver 17 for SQL Server' : file not found.

I think I am not stuck but not focussed enough and thus got lost in the "but-s" of installation instructions. I will continue tomorrow, if do not manage in the next half an hour.

[farrukh-normuradov]

Ok, I managed to install the driver, but now I have an issue with OpenSSL. I followed the recommendation here, but still get OpenSSL library could not be loaded, make sure OpenSSL 1.0 or 1.1 is installed.

At this point I still do not expect the help from you, as I attribute the errors - among other things - to my lack of focus, just wanted to share my progress here.

[dhensby]

I'm a bit confused as to why you're having to go through all the setup for the msnodesqlv8 driver if you were already using it through tedious this lib?

[farrukh-normuradov]

Yes, you are right, it looks like we were not using the driver and the connection string attribute was ignored by the package and tedious was used as a default one.

Would you have recommendation for just using tedious?

[dhensby]

Using tedious directly is fairly straightforward. It's all documented against the driver https://github.com/tediousjs/tedious

[farrukh-normuradov]

I think I found the issue. It looks like specifying the database in the connection string generates the error ConnectionError: Login failed for user 'XXX'. Does it sound familiar for you?

[farrukh-normuradov]

So for the version 7.3.0 working connection string looks like Server=SERVER_URL;UID=USER_NAME;PWD=PASSWORD;TrustedConnection=true;TrustServerCertificate=true;Encrypt=true;request timeout=900000 Compare with the failing one: Server=SERVER_URL;Database=DATABASE;UID=USER_NAME;PWD=PASSWORD;TrustedConnection=true;TrustServerCertificate=true;Encrypt=true;request timeout=900000

[dhensby]

Interesting, that should work. What happens if you used Initial Catalog instead of Database? I'm using a connection string that uses the database name (but it's using Initial Catalog, both should work) and I don't get problems...

[dhensby]

Looking at the string, this Database should work if PWD and UID work (these are aliased in a similar way to Database and Initial Catalog). The only thing I can think is that the Database value being given is not correct? Though the error is not particularly clear if that's the case.

[pharrukh]

First of all, we were using tedious and not msnodesqlv8, that was my fault.

Yesterday I found the issue. It turned out that we were using another version of the mssql in the internal library. With this old version I was creating a connection and then called execute with a new version. It produced various unexpected errors, like: Operand type clash: nvarchar is incompatible with xxx

I removed the wrong dependency and the issue was solved. I am closing the issue, thanks for your help.

[dhensby]

I'm glad you got to the bottom of it!