connect to Sql Server with Active Directory from nodejs on a linux machine

Question

I have a nodejs service which runs on a linux machine. I need to connect to Sql Server (Mssql).

I am Using mssql package and I don't see any support in connecting with AD.

There is also an Azure Keyvault which we are able to connect to with the MSI connected to the machine by calling:

import * as msRestAzure from 'ms-rest-azure'
msRestAzure.loginWithVmMSI({ resource: this.azureKeyVaultResourceName })

Is there a way to use the credentials I get from loginWithVmMSI and connect to Sql Server? Is there a way to call Sql Server directly with AD?

Is there a support for it in a different driver? tedious or nodemssql?

Answer 1

If you can use Tedious (supports Azure AD from [email protected]).

There's a top-level authentication option that allows specifying with authentication method to use:

new Connection({
  'config': {
    'server': '<server>',
    'authentication': {
      'type': 'azure-active-directory-password',
      'options': {
        'userName': '<userName>',
        'password': '<password>'
      }
    },
    'options': {
      'encrypt': true
    }
  }
})

As for the integrated security part (MSI authentication support) there is currently (19.5.2019) a pull request pending on github. If it gets approved/accepted you will get the support - you can add it manually too.

The configuration would look like this

Simple connection config:

var connectionADMSI = {
    server: [Server Name], 
    options: {
        database:[Database Name],
        encrypt: true
    },
    authentication: {
        type: "azure-active-directory-MSI",
        // Option client id, if provided, then the token will be only valid for that user
        options: {
        clientID: [Client ID For User Assigned Identity]
        }
    }
};

If you use the msnodesqlv8 you are out of luck. This is windows only solution and not yet supported on linux. For information purposes I'm including how to connect with it:

// Init connection string
var dbConfig = {    
    driver: 'msnodesqlv8',
    connectionString:'Driver={SQL Server Native Client 11.0};Server={localhost\\SQLNode};Database={nodedb};Trusted_Connection={yes};'
};