Verify certificate from SQL Server in node

Question

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'
)
*/

It looks like `tedious` currently does not support certificate verification - https://github.com/pekim/tedious/issues/282 — Simon at The Access Group, Jun 24 '16 at 08:17
I have submitted a pull request to tedious that will add the option trustServerCertificate - will update with an answer if that gets accepted https://github.com/pekim/tedious/pull/399 — Simon at The Access Group, Jun 27 '16 at 08:31

score 1 · Accepted Answer · answered Aug 12 '16 at 10:57

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(
    ...
);

Verify certificate from SQL Server in node

1 Answers1