Encrypted connection fails to connect when using SQL Server instance's IP address and custom Common Name (CN) in server certificate. #1388
Description
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