1

I found sample code from Microsoft docs but it doesn't seem to work. If anyone has any insight that would be helpful.

Also the broad question is if that is even possible. As the NodeJS uses Tedious library, it's not clear if Tedious is able to support AAD connection.

Sample code was taken from: https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-nodejs?tabs=windows

It has the code sample with type: azure-active-directory-msi-app-service which I think should work.

The error I am getting is 'Security token could not be authenticated or authorized.'

The managed-identity user is added to the resources, with permissions

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
mrsrizan
  • 301
  • 1
  • 8
  • MSI is just going to give you a shortcut to getting an AccessToken. Not sure if Tedious supports using an AccessToken for authentication. – David Browne - Microsoft Oct 13 '21 at 21:02
  • When people write " doesn't seem to work"... it seems to me that no analysis has been done on the result. What actually happens? What error do you get? – Nick.Mc Oct 13 '21 at 23:59
  • Are you running your node inside a windows app service? That is the managed identity that it's meant to use. Did you grant the managed identity access to the database? I don't see anyone actually proving that it works, except this page where the guys says it slows down your node app. https://github.com/tediousjs/tedious/issues/1021 – Nick.Mc Oct 14 '21 at 00:04
  • Lastly, if you want to write server side code to access SQL Server, I wouldn't recommend something like node.js which clearly has patchy integration capabilities. Why not something like C# which is proven to work? – Nick.Mc Oct 14 '21 at 00:05
  • The error I am getting is 'Security token could not be authenticated or authorized.' Yes the managed-identity user is added to the resources, with permissions. – mrsrizan Oct 14 '21 at 21:04
  • I was able to connect. The missing part in my case was to add role for principle user in sql server. The error message was not so helpful tbh. – mrsrizan Oct 18 '21 at 23:40

1 Answers1

2

The code was working after adding a 'reader' role for principle user in sql server.

The steps that are required to connect SQL Server through AAD - Managed identity for NodeJS project are as below:

  1. Create sql-server with sql-server database in an azure resource.

  2. Create an azure-web app within the same azure resource.

  3. Create a principle user in the web app. This can be done by going to the azure web app > Settings > Identity menu > System-assigned tab and turning on the toggle. enter image description here

  4. In the sql-db create the user with the same name as principle user that was created in step 3. The name is same as web-app.

Also assign db_reader, db_writer, and ddladmin roles to the user.

You may use the below script to create and assign role to the user. (One thing to consider if it fails to create the user, you might need to rename the app-service name as in my case it was not allowing me to create the User saying it already exists).

**CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];**
  1. Add the reader role to the principle user in the sql server. (The one that I was missing).

This can be done by going to the Sql server resource(in Azure)> Access control (IAM) enter image description here

Select Reader and select member/user (principle user with the app name), and assign the role reader to that user/member. enter image description here

  1. After all these configuration part is done, use the code from microsoft docs. For me lower node version was not working so needed to upgrade the node version.(14 in my case).

https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-nodejs?tabs=windows

Also the connection was successful only in the deployed version.

Hope this is helpful !! :)

mrsrizan
  • 301
  • 1
  • 8