MSSQL Add Integrated Authentication / Entra Support

[HunterFrisby]

Hi, I'd really love to use your extension. However, my corporate sql servers are restricted to only allow connections from integrated authentication / Entra.

Is it possible to have this added? Or to somehow integrate with the SQL Server (MSSQL) extension coonections?

[lundeen-bryan]

I was able to get mine to work by adding what is called a "service account" which is done through your db administrator.

That's just my thoughts and I'm a beginning user of SQL but if I were you, I would ask someone at your company to add a service account with a specific username and password for you to explore the data. With a service account they can control what can and cannot be changed in the database, that's likely why they use integrated authentication in the first place.

[HunterFrisby]

I was able to get mine to work by adding what is called a "service account" which is done through your db administrator.

Unfortunately, the way my IT security operates they don't allow for "service accounts".

[l-v-yonsama]

I am gathering information now.

https://tediousjs.github.io/tedious/api-connection.html#function_newConnection https://github.com/tediousjs/node-mssql?tab=readme-ov-file#formats

[l-v-yonsama]

I have added several methods of authentication to SQLServer. Please try it after upgrading.

1. SQL Server(Default)
2. Azure AD Default
    - type: azure-active-directory-default
3. Azure AD Password
    - type: azure-active-directory-password
4. AAD ServicePrincipal..
    - type: azure-active-directory-service-principal-secret
5. Azure AD MsiVm
    - type: azure-active-directory-msi-vm
6. Use Connect String

- 1. is the authentication by SQLServer that has been used so far.
- 2. to 5. are authentication by Azure Active Directory.
- 6. is a mode in which the connection definition can be freely configured with a string.

Sorry. I only have an authentication environment by SQLServer, so I have only confirmed the operation with 1. and 6.

As for 2.-5. and 6., I have read the manual here so that I can set the parameters, but I am not sure if it works well or not.

• https://tediousjs.github.io/tedious/api-connection.html#function_newConnection
• https://github.com/tediousjs/node-mssql?tab=readme-ov-file#formats

I will have time next month, so if it doesn't work, I will create an Active Directory environment on Azure at that time to investigate.

[l-v-yonsama]

The result of incorporating the "Integrated Authentication" support was that the extension was activated in the compiled environment, but not in the other environments. Since it is highly environment-dependent, we will not implement it.

[l-v-yonsama]

It is now possible to connect using the azure-active-directory-service-principal-secret method.

[HunterFrisby]

I appreciate you adding these authentications. For some reason though I still am unable to connect.

With "Encryption" I get an error about self signed certificate.

Without "Encryption" I get a different error below.

[l-v-yonsama]

I think setting “TrustServerCertificate=True” will resolve the error.

• https://stackoverflow.com/questions/60226370/certificate-error-when-connecting-to-sql-server
• https://github.com/tediousjs/node-mssql?tab=readme-ov-file#longer-example-connect-via-config-object
• https://github.com/tediousjs/node-mssql/issues/1241#issuecomment-842040940

[HunterFrisby]

Hi, thank you.

The "Trust Certificate" setting has resolved the self signed certificate error.

Though I still get this "Login failed for user ''." error.

[l-v-yonsama]

Hi, Glad you were able to resolve the “self signed certificate.” issue.

About “Login failed for user ". "

The error seems to indicate that the username is empty when connecting to Azure SQL Database.

Since the authentication method (azure-ad-default) seems to recognize the username as empty in your current environment, you may need to remedy the problem in your environment or change the authentication method.

Regarding Authentication in SQL Server, in this vscode extension, It is just passing connection parameters along 'https://tediousjs.github.io/tedious/api-connection.html', so I think it would be better to consult that site or your Azure administrator.