Troubleshooting azure sql connection through token w/ sqlclient.

Hi Whit Rooke
Thanks for laying out the setup clearly.

Since you're using a token with a service principal across tenants and getting Login failed for user '', even though the token appears to be valid (correct aud, appid, oid), here are a few things worth checking:

• SQL user creation: In the target database (tenant B), the user should be created using CREATE USER [appId] FROM EXTERNAL PROVIDER; – make sure it maps directly to the service principal’s appId, not display name or object ID.
• Connection string: With token auth, you don’t need to pass a username in the connection string. Just set the AccessToken property on SqlConnection.
• Permissions: Double-check that the created SQL user in the DB actually has required roles (e.g., db_datareader, db_datawriter). Contributor/Reader roles on the SQL Server resource in Azure don’t grant access inside the database itself.
• Allow Azure services: You mentioned it’s enabled - that’s good. But for inter-tenant access, firewall rules and the presence of an Entra admin still matter. Consider adding the service principal explicitly as a SQL AAD user.

If all of that checks out and it’s still failing, you can test the token by decoding it (via jwt.ms) and confirming claims like oid, appid, and tid match what you expect. Also, worth verifying the token is for https://database.windows.net and hasn’t expired.
Useful references:

• Use Microsoft Entra service principals to connect to Azure SQL
• Grant permissions to managed identities in Azure SQL

For a similar discussion and resolution steps, you might also find this helpful: https://learn.microsoft.com/en-us/answers/questions/1665815/error-attempting-to-create-user-from-external-prov

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Hey there Smaran, thanks for your input, as well as all the resources provided.

I was creating the user through the display name, and on your suggestion tried to create with a statement saying CREATE USER [app id] FROM EXTERNAL PROVIDER; This, however, resulted in an error stating "Principal 'app id' (with the actual app id present) could not be found or this principal type is not supported."

This seems like it might be related, due to the inability to map the token to the service principal's user. I also looked into trying to add the service principal explicitly as a SQL AAD user, but believe that that is the case, with the display name generated service principal database user created, though if I'm misunderstanding, I wouldn't be suprised!

Could this be an issue with the sql server needing directory reader access? Could that be why it can't match the appID to any service principal when trying to generate a database user that way?

Hi Whit Rooke

Upon our discussion via remote session, you're initially attempting to connect using this service principal, but it results in a "login failed for user" error, followed by an empty response.

Even though the service principal has both Contributor and Data Reader roles assigned at the SQL Server and database levels, the connection still fails.

Interestingly, when accessed via Azure Data Factory, the connection works perfectly. There's another connection configured through Azure Data Factory that can successfully insert data and create tables in the same SQL Server—no issues there. The problem seems to arise specifically when connecting using token-based authentication through the SQL client.

The service principal exists in both tenants via resource sharing, since cross-tenant service principals aren't supported. Please capture the tokens used in both the successful and failed connection scenarios to assist with further investigation.

The solution was not anything to do with the token. All was fine there, though after opening the sqlConnection, when generating the sqlCommand, a new sqlConnection was used to generate it, one without the token parameter. Thus the command couldn't authenticate, and was giving the '' user because it had nothing to base it's authentication on.