Encrypted connection fails to connect when using SQL Server instance's IP address and custom Common Name (CN) in server certificate.

[jsimonweb]

Expected behavior:

Secure connection should succeed if a valid server certificate is provided along with a custom Common Name (CN) which matches a Common Name (CN) present in the server certificate.

Actual behavior:

Google Cloud SQL for SQL Server generated SSL server certificates include a Common Name (CN) formatted as: project-id:instance-id

Example snippet from a generated SSL server certificate:
CN = my-project:test-sqlserver

When I attempt to make a tedious/node-mssql based connection to the SQL Server instance using its assigned IP Address along with setting encrypt=true and trustServerCertificate=false, while providing a server certificate, the connection fails with the error:

ConnectionError: Failed to connect to [IP Address]:1433 - Hostname/IP does not match certificate's altnames: IP: [IP Address] is not in the cert's list

This is a result of the SSL server certificate having the common name (CN) my-project:test-sqlserver which doesn't match the IP address set for the config.server setting. I attempted setting config.options.serverName = "my-project:test-sqlserver" to provide the common name (CN) to be used for the server certificate verification process but got the same connection error.

Setting trustServerCertificate=true enables the connection to succeed and work as expected (confirming that the other non-SSL configuration values are valid).

Configuration:

const createPool = async () => {
    const config = {pool: {}, options: {}};
    config.user = process.env.DB_USER; 
    config.password = process.env.DB_PASS;
    config.database = process.env.DB_NAME;
    config.port = 1433;
    config.server = process.env.DB_SERVER_IP_ADDRESS;
    config.options.encrypt = true;
    config.options.trustServerCertificate = false;
    config.options.serverName = "my-project:test-sqlserver";
    config.options.cryptoCredentialsDetails = {
        ca: fs.readFileSync(process.env.DB_ROOT_CERT)
    };
    return await mssql.connect(config);
};

Software versions:
NodeJS: >=10.0.0
node-mssql: ^7.0.0 https://github.com/tediousjs/node-mssql
SQL Server: SQL Server 2017 Standard

[MichaelSun90]

Hi @jsimonweb, can you try to set the encrypt=false if this is not a hard restriction for you server? See if that works. I found a thread that may related to why "encrypt = true" may not works here: #903 (comment).
Hi @arthurschreiber , is there anything else based on the information provided here could cause this error?

[MichaelSun90]

Hi @jsimonweb, we did a bit more digging, and find that we do have a serverName. However, it is not really handled properly. We will make a fix for this and will let you know when the change is ready.

[jsimonweb]

Checking back in on this issue.. any updates?

[MichaelSun90]

Hi @jsimonweb, I just raised a PR #1476, can you give it a try, see if this solve the your issue?

[jsimonweb]

I tested this PR but it does not appear to resolve the issue.

Here are the steps I used to test the PR.

Added PR to package.json

$ npm install

$ nano package-lock.json

"node_modules/mssql": {
      "version": "8.1.2",
      "license": "MIT",
      "dependencies": {
        "@tediousjs/connection-string": "^0.3.0",
        "commander": "^9.1.0",
        "debug": "^4.3.3",
        "rfdc": "^1.3.0",
        "tarn": "^3.0.2",
        "tedious": "git://github.com/tediousjs/tedious.git#838a8c118133ff45dbd29ea00f060d7aa227bb75"
      },
      "bin": {
        "mssql": "bin/mssql"
      },
      "engines": {
        "node": ">=10"
      }
    },

Confirm that PR code is being used with npm list

$ npm list --depth=1

│ └── tedious@0.0.0-dev (git+ssh://git@github.com/tediousjs/tedious.git#838a8c118133ff45dbd29ea00f060d7aa227bb75)

Code snippet (from modified connect-tcp.js file) being run

const createTcpPool = async config => {
  const dbConfig = {
    server: process.env.INSTANCE_HOST, // e.g. '[SQL-Server-IP-Address]'
    port: parseInt(process.env.DB_PORT), // e.g. 1433
    user: process.env.DB_USER, // e.g. 'my-db-user'
    password: process.env.DB_PASS, // e.g. 'my-db-password'
    database: process.env.DB_NAME, // e.g. 'my-database'
    options: {
      trustServerCertificate: false,
      serverName : 'my-project-id:my-sql-server-instance-name', // custom CN in SSL cert
      encrypt: true,
      cryptoCredentialsDetails: {
        ca : process.env.DB_ROOT_CERT, // e.g. 'certs/server-ca.pem'
      },
    },
    // ... Specify additional properties here.
    ...config,
  };
  return await mssql.connect(dbConfig);
};

Run the sample app

$ npm start

> start
> node server/server.js

App listening on port 8080
Press Ctrl+C to quit.

{"code":"ESOCKET","level":"error","name":"ConnectionError","originalError":{"code":"ESOCKET"}}
ConnectionError: Failed to connect to [SQLSERVER-IP-ADDRESS]:1433 - unable to verify the first certificate
    at /home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/mssql/lib/tedious/connection-pool.js:70:17
    at Connection.onConnect (/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:1028:9)
    at Object.onceWrapper (node:events:628:26)
    at Connection.emit (node:events:513:28)
    at Connection.emit (/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:1056:18)
    at Connection.socketError (/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:1407:12)
    at /home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:2414:25
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ESOCKET',
  originalError: ConnectionError: Failed to connect to [SQLSERVER-IP-ADDRESS]:1433 - unable to verify the first certificate
      at Connection.socketError (/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:1407:28)
      at /home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:2414:25
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    code: 'ESOCKET',
    isTransient: undefined
  },
  level: 'error',
  [Symbol(level)]: 'error',
  [Symbol(message)]: '{"code":"ESOCKET","level":"error","name":"ConnectionError","originalError":{"code":"ESOCKET"}}'
}
/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/server/server.js:27
    throw err;
    ^

ConnectionError: Failed to connect to [SQLSERVER-IP-ADDRESS]:1433 - unable to verify the first certificate
    at /home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/mssql/lib/tedious/connection-pool.js:70:17
    at Connection.onConnect (/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:1028:9)
    at Object.onceWrapper (node:events:628:26)
    at Connection.emit (node:events:513:28)
    at Connection.emit (/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:1056:18)
    at Connection.socketError (/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:1407:12)
    at /home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:2414:25
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ESOCKET',
  originalError: ConnectionError: Failed to connect to [SQLSERVER-IP-ADDRESS]:1433 - unable to verify the first certificate
      at Connection.socketError (/home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:1407:28)
      at /home/userdir/nodejs-docs-samples/cloud-sql/sqlserver/mssql/node_modules/tedious/lib/connection.js:2414:25
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
    code: 'ESOCKET',
    isTransient: undefined
  },
  level: 'error',
  [Symbol(level)]: 'error',
  [Symbol(message)]: '{"code":"ESOCKET","level":"error","name":"ConnectionError","originalError":{"code":"ESOCKET"}}'
}

Node.js v18.6.0