8

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?

Mithir
  • 2,355
  • 2
  • 25
  • 37
  • You can call a normal stored procedure with node-mssql, then it is the proc that does the work of connecting to AD. But I assume you mean actually authenticating with AD credentials to mssql and not just accessing AD via node-mssql. The latter is possible via a Linked Server to AD. – smoore4 May 13 '19 at 18:22
  • @smoore4 - can you elaborate on how Linked Server to AD will help here? also seems like a security issue... – Mithir May 14 '19 at 11:09

1 Answers1

6

If you can use Tedious (supports Azure AD from tedious@4.1.0).

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};'
};
tukan
  • 17,050
  • 1
  • 20
  • 48
  • Also... seems like an issue as we still need to store the password somewhere...no? – Mithir May 16 '19 at 11:33
  • @Mithir in the `'options'` you are storing the password. – tukan May 16 '19 at 11:36
  • @Mithir I think you did not understand the difference in the drivers. The windows driver supports SSO via the trusted connection. On linux you have different approach, you have to identify your self against AD. SSO is not available in this case and you have to id/passwd to identify the user. – tukan May 18 '19 at 13:11
  • I understand... but I am able to authenticate a specific linux machine using MSI (no username/password) when connecting to Azure KeyVault. The option you are suggesting requires saving the password somewhere... also it means that other users and machines will be able to use that AD identity... so your answer is good in means of connecting to AD but it doesn't give us a fully secured way of doing so. – Mithir May 19 '19 at 05:54
  • @Mithir I see, well Tedious has a patch pending for that. I'll update the answer. But yes, till the patch gets accepted you can either patch it manually or wait till it gets accepted. – tukan May 19 '19 at 07:39
  • how can I check for updates myself about this patch? How could it be "patched" manually? – Mithir May 19 '19 at 08:29
  • @Mithir you simply check the state of the patch (at the link) when it is merged and the release is after the patch merge date. I'm in no way expert building javascript applications, but I would do it the same way the `npm` does it do git checkout (of the patched version) and satisfy the dependecies. Those can be found at: https://www.npmjs.com/package/tedious?activeTab=dependencies – tukan May 19 '19 at 10:03
  • I'm not sure about the link you shared in your comment, but I did find this https://github.com/tediousjs/tedious/pull/896 - which is an implementation of logging in with MSI which is pending approval - this looks exactly what I am looking for! also, it seems like I can already do it now with 2 steps, getting an access token with MSI and then connecting with the token... not sure why I did not see it in any documentation... – Mithir May 19 '19 at 11:23
  • @Mithir that is exactly the link in my answer (not comment!), did you actually read it?! The documentation does not exist as this code has not been merged and tested. The documentation is created aftewards. – tukan May 19 '19 at 12:06
  • @Mihir thank you, I normally put EDIT word but since I was answering on mobile the foematting is pain. – tukan May 19 '19 at 14:05