Azure SQL with Managed Identity (user-assigned) failed to login

Question

I'm using .NET Framework (not Core!) 4.7.2 and am trying to use a User-Assigned Managed Identity to connect to an Azure SQL Server. The Managed Identity object has been added to db_datareader, db_datawriter roles on the Azure SQL server and has permissions to connect. (independently verified with our ASP.NET Core app).

However it fails when we try to do it from our command-line .NET Framework app running on a VM that has been enabled on the managed identity. The code to connect to Azure SQL looks roughly like this:

// .NET Framework 4.7.2
using(SqlConnection connection = new SqlConnection("Server=tcp:myserver.database.windows.net,1433;Database=MyDb;")){
    var credential = new ManagedIdentityCredential(ConfigurationManager.AppSettings["ManagedIdentityId"]);
    conn.AccessToken = await credential.GetTokenAsync(
                          new TokenRequestContext(new[] { "https://database.windows.net/.default" })
                       );
    ....
}

It manages to grab a token just fine (from the logs below), but fails with this error

System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''

What should I check?

(the very same code works fine from ASP.NET Core)

Appendix

Very similar to this issue, but in our case not all forms of auth failed on the Azure SQL server. We were able to login manually with AAD accounts. It's only the User-Assigned Managed Identity called from .NET Framework 4.7.2 that is failing (it works fine from our ASP.NET Core program, same code)
Logs here

2021-06-15 14:39:28.7200|INFO|Logger|Received TOKEN - [xxxx real token here xxxx]
2021-06-15 14:39:28.9387|ERROR|Logger|Exception occurred: 
   System.Data.SqlClient.SqlException (0x80131904): Login failed for user ''.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, 
      SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, 
      SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, 
      Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, 
                                                                  Object poolGroupProviderInfo, DbConnectionPool pool, 
                                                                  DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, 
                                                                          DbConnectionOptions options, DbConnectionPoolKey poolKey, 
                                                                          DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, 
                                                                 Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, 
                                                                 DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()

Jim Xu Jim Xu · Accepted Answer · 2021-06-16T07:13:37

According to my test, It is Ok for me. My steps are as below.

Assign MSI to the VM.
Configure Azure AD auth

DROP USER IF EXISTS [the msi name]
GO
CREATE USER [the msi name] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_datareader ADD MEMBER [the msi name];
ALTER ROLE db_datawriter ADD MEMBER [the msi name];
GRANT EXECUTE TO [the msi name]
GO

Install package

Install-Package Azure.Identity -Version 1.4.0
Install-Package System.Data.SqlClient -Version 4.8.2

Code

static async Task Main(string[] args)
        {
            string conStr = "Server=tcp:testsql09.database.windows.net,1433;Database=test;";
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                var credential = new ManagedIdentityCredential("ed5f***823");
                connection.AccessToken = (await credential.GetTokenAsync(
                              new TokenRequestContext(new[] { "https://database.windows.net//.default" })
                    )).Token;
                String sql = "SELECT * FROM Blogs";

                using (var sqlCommand = new SqlCommand(sql, connection))
                {
                    connection.Open();
                    using (SqlDataReader reader = sqlCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine("{0} {1}", reader.GetInt32(0), reader.GetString(1));
                        }
                    }
                }

            }
            Console.Read();
        }

Azure SQL with Managed Identity (user-assigned) failed to login

1 Answers