Azure AD authentication to Azure SQL Server Not Working

JAL 571 Reputation points
2021-09-16T00:06:09.527+00:00

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?

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
    2021-09-16T19:22:28.027+00:00

    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
    2021-09-16T18:32:48.48+00:00

    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
    2021-09-16T18:53:39.61+00:00

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

    0 comments No comments

  3. Siva-kumar-selvaraj 15,576 Reputation points
    2021-09-17T06:59:34.923+00:00

    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
    2021-09-17T17:25:39.73+00:00

    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?