MSSQL Add Integrated Authentication / Entra Support

[HunterFrisby]

Hi, is it possible to add support for sql server integrated authentication / entra to connections?

[mikeburgh]

From a quick read of the documentation.. Maybe..

A couple of questions:

• The server you want to connect to, it's in Azure right, what they call Azure SQL (MSSQL underneath) ?

• Is the account you want to use the one you signed into VS code as ? We need to test it, but I am wondering if we can use that account... although the tenant/client ID might cause issues.

Tedious, the underlying driver we use, supports a number of different Azure based auth methods (https://tediousjs.github.io/tedious/api-connection.html#function_newConnection) , but I think the two relevant ones for this are: azure-active-directory-password and azure-active-directory-service-principal-secret

azure-active-directory-password

• Downside is this requires you to enter your Azure account password.

azure-active-directory-service-principal-secret

• Downside is this requires you to generate a secret somewhere, and use that.

[HunterFrisby]

Hi

• Yes, I believe they are 'Azure' SQL servers.
• Yes, the account is that which I am currently logged into VSCode with.
• The 'azure-active-directory-password' seems like the correct method and seems to align with what I've seen done in other extensions (e.g. SQL Server (MSSQL)).

I appreciate your efforts in exploring this request.

Thank you.

[mikeburgh]

Thanks for confirming, we will setup a test Azure server and see if we can get this going, would be nice to leverage the VSCode signed in account if we can.

[mikeburgh]

Support for Entra ID has been added to the Azure connection type in 0.16.0, which should be available now.

We also added Azure as a cloud provider, since a lot of the functionality overlapped as was on the road map to support Azure as a cloud provider.

This means you can add the Azure cloud provider and browse all your databases without needing to make individual connections for them. Authentication to the databases found via the cloud provider is done via Entra ID only, including for MySQL and Postgres databases.

[HunterFrisby]

I appreciate you adding this support! I have updated to the latest version and have tried connecting with the new settings (see below). Though when I try to connect I get an "Error connecting to Jitterbit-DEV, error: Login failed for user ''." error. I was able to select my Entra account and authorize access appropriately.

Settings

Error

P.S. I have also tried connecting through the Azure Cloud connection, but I believe that since in my case the server is not on the same host address (e.g. xxxx.azuresynapes.net) as my username (User@company.com) it doesn't apply correctly.

[mikeburgh]

Thanks, digging into this.. we used a default Azure AD environment, so guessing there is something more to it..

[mikeburgh]

Did you use a connection that you had already created by chance ? Can you delete it, and add a new connection and see if that works ? Hopefully we identified the issue.

If that does not fix it, we will release a build with extra logging so we can try and see what's happening inside the connection creation.. It will show the trace details and require you to paste them here, so you can review first for any information you want to omit (eg host names etc)

[HunterFrisby]

I have retired with removing the connection and adding it again, as well as adding a different connection. Which still results in the "Error connecting to Jitterbit-DEV, error: Login failed for user ''." error.

I am still on v0.16.0 and don't show any update, if the issue you mentioned is supposed to be on a later/new version?

[mikeburgh]

I did some more digging into Azure, the xxxx.azuresynapes.net is one of their hosted services, it's SQL Server underneath (among other things) but it does present and work as SQL Server.

I created one to test it out, and was able to find and connect to it via the cloud provider as well as a manual connection using Entra ID, so it's not specific to that service.

I also tried creating other tenants and accessing resources across tenant, and was not able to get any errors like yours..

Heres one screenshot of the various connections and tenants we tested, note the synapse connection manually at the bottom.. it's also in the synapseworkspace.

A new version 0.16.1 is out now with some updated drivers, I am doubtful it fixes your issue, but hoping it gives us a better error response. Can you give it a try.

MS is also in the middle of changing over the approach for token auth for the SQL driver (https://github.com/tediousjs/tedious/pull/1641), as soon as that's out I am going to try it to see if it's any better.

Assuming this new version does not fix it for you, can you reach out to help@dbcode.io and we can send you a build with more tracing so we can try and identify where the issue in your Azure environment is.