Azure AD authentication to Azure SQL Server Not Working

Question

I have created an app registration in the portal, let's say it's named MyRegistration with clientID 12345 and tenantId 678910

Then I created a user in the DB:
..........Create user MyRegistration from external provider

And I assigned typical permissions/roles to that user - db_datareader, db_datawriter, even db_owner.

In my C# application, I acquire a token using said clientID, plus a client secret.

string resourceId = $"https://database.windows.net/";
string authority = $"https://login.windows.net/{TenantId}/";
AuthenticationContext authenticationContext = new AuthenticationContext(authority);
ClientCredential clientCredential = new ClientCredential(ClientID, ClientSecret);
AuthenticationResult authenticationResult = Task.Run(async () => await authenticationContext.AcquireTokenAsync(resourceId, clientCredential)).Result;
_DBAccessToken = authenticationResult.AccessToken;

But when I create a System.Data.SqlClient.SqlConnection:
........cn.AccessToken = strToken;
........cn.Open();

I get an error message - a message that contains my clientId and TenantID (separated by an @-sign)

"Login Failed for User 12345@678910"

That's all it says. What am I doing wrong?

Answer 1

Ok maybe I found a pattern of some kind. On a failed DB, I upgraded from Basic Tier to Standard Tier and it worked. Then I verified by downgrading to Basic Tier (got the err msg again) and then I upgraded to Standard Tier (worked fine again).

Maybe this is documented somewhere and I overlooked it?

Answer 2

If I create another DB in the same server, it works fine, using same clientID for both. These are both brand-new DBs. Apparently the first DB is corrupt somehow? I have no idea.

Answer 3

And then I created a third DB. Failed on that one.

Answer 4

Hello @JAL , Thanks for sharing your findings here. However, I will double check this with our product group and update you about same. Thanks

Answer 5

I'm sorry were you asking me to check with product group? (I don't know how to do that). Or are you doing this yourself?