Simon at mso.net Simon at mso.net - 3 months ago 18
Node.js Question

Verify certificate from SQL Server in node

I have a client whose SQL Server database I need to connect to. The FQDN of that SQL Server machine is

db.client.local
and they have set up a self-signed certificate / enabled encryption.

If I connect to this host (adding an entry to the remote IP in my hosts file) using Navicat with encryption flagged as enabled, it rejects the connection as untrusted due to the CA being untrusted, which is what I expect.

In node using
node-mssql
and
tedious
I am able to connect and query the server however no verification seems to happen. How can I get
node-mssql
to verify the certificate? In this case I then need to be able to also provide a custom CA certificate.

Here is my code so far

var sql = require( 'mssql' ),
evilDns = require( 'evil-dns' );

// Set up the mapping so that I can access via their local dns
evilDns.add( 'db.client.local' , '1.2.3.4' );

// Works without erroring

new sql.connect({
user: 'user',
password: 'password',
server: 'db.client.local',
database: 'my-test-database',
port: 1234,
options: {
encrypt: true // seems decorative, connection encrypts without this
}
}).then(
function( connection ) {
return new sql.Request( connection )
.query( `SELECT * FROM TableWithStuffIn` )
.then( function( response ) {
console.log( response );
return connection.close();
} );
},
function( err ) {
console.log( err );
return Promise.reject();
}
)

// This also works without erroring
/*
new sql.connect(
'mssql://user:password@db.client.local:1234/my-test-database?Encrypt=true&TrustServerCertificate=false'
)
*/

Answer

This functionality was unsupported at the time of answering the question. Tedious now has this functionality in its current master branch, through not in the released branch.

Once the update is released, the following example enables certificate verification:

new sql.connect({
    user: 'user',
    password: 'password',
    server: 'db.client.local',
    database: 'my-test-database',
    port: 1234,
    options: {
        encrypt: true,
        trustServerCertificate: false
    }
}).then(
    ...
);

Within our use case due to the amount of SQL servers with internal-only FQDNs and self-signed certificates, I use something more similar to the following which also leverages evilDNS to provide DNS overrides

require( 'evil-dns' ).add( 'db.client.local' , '11.22.33.44' );

new sql.connect({
    user: 'user',
    password: 'password',
    server: 'db.client.local',
    database: 'my-test-database',
    port: 1234,
    options: {
        encrypt: true,
        trustServerCertificate: false,
        cryptoCredentialsDetails: {
            ca: 'PEM Encoded self-signed certificate authority certificate goes here'
        }
    }
}).then(
    ...
);
Comments