Question 104762 in Azure Question posted in
The official documentation to get you started can be found here.

login failed for user '<token-identified principal>' when connecting to Azure sql using managed identity locally

I have mvc 5 application that is targeting .net framework 4.8. I am trying to connect to the Azure sql db using managed identity locally but getting the following error:

login failed for user token-identified principal>

I’ve spent many hours but was unable to find any good solution.

Steps I’ve performed:

  1. Set the AD group (which I am part of) as an Admin of the Sql Server. Also tried my user as ad admin directly.
  2. Set my user under "Azure Service Authentication" in VS 2022
  3. Created my user in the db CREATE USER [myUSer] FROM EXTERNAL PROVIDER; and assigned db_datareader and db_datawriter role
  4. Removed user id and password from the connection and it only has the Server and Database. Also checked that the db name has no typo.
  5. Whitelisted my IP in db firewalls.
  6. Updated code so it uses AccessToken on the SqlConnection

.

using(var connection = new Microsoft.Data.SqlClient.SqlConnection(connString))
{
  var credential = new DefaultAzureCredential(){};
  var token = credential.GetToken(new TokenRequestContext(new[] { "https://database.windows.net/.default" }));
  connection.AccessToken = token.Token;
  connection.Open();
}

But as soon as it calls the connection.Open() I start getting the login failed error. What am I missing here?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So there were actually two issues. I wasn't passing the AD TenantId which was different for SqlServer Admins, so I passed the TenantId with the DefaultAzureCredential:

     new DefaultAzureCredential(new DefaultAzureCredentialOptions
 {
      TenantId = TenantId
 });

    Also i set some Environment variables for accessing KeyVault locally and those variables were overriding some of the config values which I could see in the token generated by DefaultAzureCredential. So deleting the variables and setting up the TenantId solved the problem for me.


  2. 0
    1. Set my user as "Admin" of the SQL Server.
    2. Set my user under "Azure Service Authentication" in VS 2022.
    3. User Creation and granting permissions:
    DROP USER IF EXISTS testuser
GO
CREATE USER testuser FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER testuser
ALTER ROLE db_datawriter ADD MEMBER testuser;
GRANT EXECUTE TO testuser

    1. Install packages:

      Install-Package Azure.Identity -Version 1.4.0

      Install-Package System.Data.SqlClient -Version 4.8.2

    2. Added my IP address in DB firewall.

    Here is the code that worked for me:

    using Azure.Core;  
using Azure.Identity;  
using System.Data.SqlClient;  
public class Program  
{  
  static async Task Main(string[] args)  
    {  
      string conStr ="Server=tcp:xxxxxxserver00.database.windows.net,1433;Database=analyticsdb";  
      using (SqlConnection connection = new SqlConnection(conStr))  
      {  
        var credential = new DefaultAzureCredential();  
        connection.AccessToken = (await credential.GetTokenAsync(new TokenRequestContext(new[] { "(https://database.windows.net//.default"})  
)).Token;  
        String sql = "SELECT * FROM new_persons";
        using (var sqlCommand = new SqlCommand(sql,connection))  
       {  
          connection.Open();  
          using (SqlDataReader reader = sqlCommand.ExecuteReader())  
          {  
             while (reader.Read())  
             {  
                Console.WriteLine("{0} {1} {2}",reader.GetInt32(0), reader.GetInt32(0),reader.GetString(2));  
             }  
          }  
       }
   }  
   Console.Read();  
   }  
}

    Output results from database:

    enter image description here

    Ms Doc Reference

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search