Azure AD authentication to Azure SQL Server Not Working

JAL 571 Reputation points

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 = $"";
string authority = $"{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: = strToken;;

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?

Azure SQL Database
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
20,067 questions
0 comments No comments
{count} votes

Accepted answer
  1. JAL 571 Reputation points

    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?

    1 person found this answer helpful.
    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. JAL 571 Reputation points

    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.

    0 comments No comments

  2. JAL 571 Reputation points

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

    0 comments No comments

  3. Siva-kumar-selvaraj 15,576 Reputation points

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

    0 comments No comments

  4. JAL 571 Reputation points

    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?