Azure Data Studio uses hard-coded service principals for Azure AD authentication

[Dylan-MSFT]

• Azure Data Studio Version: 1.37.0
• OS Version: Windows Server 2019

When connecting with Azure AD authentication to SQL, a FEDAUTHINFO packet is sent which holds the service principal the server uses. If the client gets an access token that does not match this service principal, logins will fail. Since Azure Data Studio uses hard-coded service principals, it will be unable to login to SQL Server instances configured with non-standard service principals.

Steps to Reproduce:

1. Configure AAD for SQL Server following our public docs here: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/azure-ad-authentication-sql-server-overview?view=sql-server-ver16.
2. Go to portal.azure.com a. Click on “Azure active directory” b. Click on “App registrations” c. Click on the app registration you created for SQL Server then “Expose an API” on the left column d. Click "Set" next to "Application ID URL", and set it to an HTTPS URL. I suggest the FQDN of the machine which SQL is running on, but it doesn't really matter. e. Click "Add a scope" 1. Set "Scope name" to "user_impersonation" 2. "Who can Consent?" = "Admins only" 3. Fill in names and descriptions as you see fit 4. "State" = Enabled 5. Click "Add scope" f. Click "Add a client application" and add all the Client IDs listed below and check the "user_impersonation" entry under "Authorized scopes". This will allow our client apps / drivers to connect to this API 1. 7f98cb04-cd1e-40df-9140-3bf7e2cea4db 2. 1950a258-227b-4e31-a9cf-717495945fc2 3. db465503-d247-463e-8a13-95889346b742 4. 2c1229aa-16c5-4ff5-b46b-4f7fe2a2a9c8 5. a94f9c62-97fe-4d19-b06d-472bed8d2bcf 6. 4d079b4c-cab7-4b7c-a115-8fd51b6f8239
3. Open regedit on the SQL Server host machine a. Go to HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQLServer\FederatedAuthentication b. Set ServicePrincipalName and ServicePrincipalNameNoSlash to the application ID URL you set for the app registration. Note: neither of these values should end with a slash
4. • Attempt to connect with ADS using Azure AD authentication (this will fail)

Does this issue occur when all extensions are disabled?: Yes

[kburtram]

@cheenamalhotra could you please help assess the impact of this defect?

[cheenamalhotra]

Hi @Dylan-MSFT

Azure Data Studio uses SqlClient driver to send FEDAUTHINFO to Azure SQL. STS URL and SPN are received from Server when receiving FedAuthInfo token (ref: code), and driver uses the same to request access token from AAD.

SqlClient does not send any SPN information, it only sends token as bytes. Example: Login Request with FedAuthToken (search for "FedAuthToken").

Let me know if I missed something.

[cheenamalhotra]

What you're describing above about App Registrations might be related to AD Service Principal authentication mode, where you can pass Client App ID and Client Secret as credentials to authenticate to server. These credentials would then be used to acquire access token to authenticate with target server.

But this option is currently not supported by Azure Data Studio.

[Dylan-MSFT]

@cheenamalhotra I am talking about the SPN received by the client from the FEDAUTHINFO token. It needs to use that during MFA access token creation (which Azure Data Studio supports) to generate a token with the correct audience / scope. With the current behavior, all tokens generated through Azure Data Studio's MFA process have a scope of "https://database.windows.net/" which will not work once SQL Server instances are properly configured for AAD.

[cheenamalhotra]

Ah! That makes sense.

We can do that by implementing SqlClient's SqlAuthenticationProvider and consuming SPN from SqlAuthenticationParameters.Resource as that's where it goes from FedAuthInfo.

[cheenamalhotra]

@kburtram

To answer your question, yes this is quite important to support wide variety of servers through SqlClient. This design will also solve many token expiry issues as SqlClient's design of pooling and token expiration encapsulates the SqlAuthenticationProvider implementation and will implicitly fetch new tokens from the implementation class when necessary.

SSMS does the same, so it should be a must have for ADS too. Let's discuss offline and in meantime I'll look into its feasibility right away and prioritize this.

[Dylan-MSFT]

@cheenamalhotra What is the status of this?

[cheenamalhotra]

Hi @Dylan-MSFT

We've discussed this internally and it would require significant changes with validations. We've been currently working on adding MSAL library support, which is currently in review. Once the PR #21024 completes, we will get back on this issue to be addressed soon after. Please feel free to reach out to me directly (internally) if you have any timeline related concerns or any other urgent requests regarding this issue.

[meiaestro]

We would also urgently need this functionality not only in Azure Data Studio, but also in SQL Server Management Studio. Is there any timeline available?

[Dylan-MSFT]

@meiaestro, This should already work in the recent versions of SSMS. I currently have SSMS 18.9.1 installed and it respects the server's SPN.

[meiaestro]

@Dylan-MSFT just tested it with the latest SSMS and you're absolutely right. It's working. Thanks for the hint.

[esilgard]

has this been updated in Azure Data Studio? I have several users that could benefit from the update

[cheenamalhotra]

Hi @esilgard

Work is in progress for this one, we'll update the thread as soon as it hits insiders build.

[cheenamalhotra]

Hi everyone,

This feature support is now available in ADS insiders (download link) and can be enabled with below setting:

    "mssql.enableSqlAuthenticationProvider": true

It will be available in the ADS March 2023 release and is currently NOT enabled by default. I will proceed to close this issue as resolved, please open a new GitHub issue if you face any issues.

Thanks!