authenticating to azure sql db using entra credentials Login failed for token-identified-principal

Question

The goal is a WPF app that allows external users in an Entra external tenant to authenticate to an Azure SQL DB. I am following https://learn.microsoft.com/en-us/entra/external-id/customers/sample-desktop-wpf-dotnet-sign-in carefully, and can use it authenticate users. That is, the method SignInButton_Cllick successfully signs in an external user, say [joe@xyz.com] correctly.

I have a Azure sql db (in a subscription which has directory the same tenant) and I can successfully create the user in the db (not master) via CREATE USER [joe@xyz.com] FROM EXTERNAL PROVIDER successfully, and add datawriter, reader privileges. With that in place the method ContactDB_Click should complete, but instead returns "Login failed for token-identified principal". I don't think the problem is at the db level: if I change the connection string to "Authentication=Active Directory Interactive;" (after supplying the user info and password again) the connection will open.

Any idea what's going on?

using Microsoft.Data.SqlClient;
using Microsoft.Identity.Client;
private async void SignInButton_Click(object sender, RoutedEventArgs e)
{    
    var app = App.PublicClientApp;
    ResultText.Text = string.Empty;
    TokenInfoText.Text = string.Empty;    IAccount firstAccount;
    
    var accounts = await app.GetAccountsAsync();
    firstAccount = accounts.FirstOrDefault();

    try
    {
        // Try to sign in silently the previously signed-in user
        authResult = await app.AcquireTokenSilent(s_scopes, firstAccount)
            .ExecuteAsync();
    }
    catch (MsalUiRequiredException ex) { \suppressed to save space}    
    if (authResult != null)
    {
        ResultText.Text = "Sign in was successful.";
        DisplayBasicTokenInfo(authResult);
        this.SignInButton.Visibility = Visibility.Collapsed;
        this.SignOutButton.Visibility = Visibility.Visible;    }
}
private void ContactDB_Click(object sender, RoutedEventArgs e)
{
string cstring ="Server=tcp:entraexperimentserver.database.windows.net,1433;Database=EntraExperimentDB;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Default;";                    
    
  using var conn = new SqlConnection(cstring);
   {                   
    conn.Open();
    MessageBox.Show("connection now open");
    conn.Close();
   }       
}

Answer 1

Hello @greg schlitt

Let’s troubleshoot this step by step.

1. Authentication Configuration:
   • Ensure that your app is registered in Azure Active Directory (AAD) and has the necessary permissions to access the Azure SQL Database. You’ve already successfully signed in external users using the SignInButton_Click method, which means your authentication setup is working.
   • Make sure you’ve registered your app with AAD and granted it the appropriate permissions. Specifically, you need to add the “Azure SQL Database” permission with “User impersonation” delegated permission.
   • Verify that the user you’re trying to authenticate has been added to the Azure SQL Database. You mentioned creating the user via CREATE USER [joe@xyz.com] FROM EXTERNAL PROVIDER, but ensure that the user is correctly mapped to the database and has the necessary roles (e.g., db_datareader, db_datawriter).
   Connection String:
   • Your connection string in the ContactDB_Click method should specify the correct authentication method. Since you’re using AAD authentication, set it to "Authentication=Active Directory Default;".
     • Double-check the connection string for any typos or missing parameters. It should include the server name, database name, and other relevant settings.

Token-Identified Principal Error:

• The error message “Login failed for token-identified principal” indicates that the user’s token is not being properly recognized during authentication.
  • This issue might be related to the SID (Security Identifier) mismatch between the AAD user and the SQL Server user. Ensure that the SID of the AAD user matches the SID of the corresponding SQL user.
  • To verify if this is the issue, run the following query in the database you’re trying to connect to:

     SELECT name, sid FROM sys.database_principals WHERE name = 'joe@xyz.com';
  

  Compare the retrieved SID with the AAD user’s SID.

SSMS Connection:

• You mentioned that connecting via SSMS works after specifying the database name. This is because Azure SQL Databases are self-contained, and users don’t have permission to connect to the master database by default.
  • Make sure your app specifies the correct database name in the connection string. If it doesn’t, SSMS won’t connect to the desired database.

Troubleshooting:

• If you encounter further issues, consider enabling diagnostic logging for Azure SQL Database. This can provide additional details about authentication failures.
• Review the Azure AD authentication configuration and ensure that the user’s UPN (user principal name) is used for authentication.

Remember to handle exceptions gracefully in your code to provide better error messages and troubleshoot effectively.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.

Answer 2

Needed to remove "Authentication = Active Directory Default" , move the definition of authResult outside of SignInButton_Click so it's accessible in ContactDB_Click, in that method use

conn.AccessToken = authResult.AccessToken;

and include a new scope in s_scopes (not shown in the original question text) via

string[] scopes = new string[] { "https://database.windows.net//.default" };

Answer 3

@greg schlitt That is Awesome, I'm glad that you were able to resolve your issue and very much appreciate for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue: you are trying to authenticate external users in an Entra external tenant to an Azure SQL DB. You have followed the instructions in the Microsoft documentation but are encountering an error when trying to connect to the database. The error message is "Login failed for token-identified principal".

Solution: As you mentioned you needed to remove "Authentication = Active Directory Default" , move the definition of authResult outside of SignInButton_Click so it's accessible in ContactDB_Click, in that method use conn.AccessToken = authResult.AccessToken; and include a new scope in s_scopes (not shown in the original question text) via

string[] scopes = new string[] { "https://database.windows.net//.default" };

If you have any other questions or are still running into more issues, please let me know.

Please remember to "Accept Answer"so that others in the community facing similar issues can easily find the solution.

Regards

Geetha