Managed Identity Sql Auth with EF Core - Login failed for user ''

Question

I have a dotnet 5 (isolated) Azure Function app that needs to access an Azure Sql Server database via EF Core 5.

If I follow the instructions here then I get the following error from the function app:

Login failed for user '<token-identified principal>'

However, if, I promote the object id of the managed identity for the function app to be Sql Admin using the following command:

az sql server ad-admin create --resource-group rg-smsrouter-msdn --server-name sql-smsrouter-msdn --display-name MSIAzureAdmin --object-id "id of managed identity here"

The the rows are written correctly. My concern is that the managed identity should not need to be a sql admin.

The only code in my db context is:

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            SqlConnection connection = new SqlConnection();
            string? envConString = Environment.GetEnvironmentVariable(ConfigConstants.SqlSvrConnString);
            connection.ConnectionString = envConString ?? "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=SmsRouter";
            optionsBuilder.UseSqlServer(connection);
        }

I also make use of the Authentication Provider from the blog post:

 public class SqlAppAuthenticationProvider : SqlAuthenticationProvider
    {
        private static readonly AzureServiceTokenProvider _tokenProvider = new AzureServiceTokenProvider();

        /// <summary>
        /// Acquires an access token for SQL using AzureServiceTokenProvider with the given SQL authentication parameters.
        /// </summary>
        /// <param name="parameters">The parameters needed in order to obtain a SQL access token</param>
        /// <returns></returns>
        public override async Task<SqlAuthenticationToken> AcquireTokenAsync(SqlAuthenticationParameters parameters)
        {
            var authResult = await _tokenProvider.GetAuthenticationResultAsync("https://database.windows.net/").ConfigureAwait(false);

            return new SqlAuthenticationToken(authResult.AccessToken, authResult.ExpiresOn);
        }

        /// <summary>
        /// Implements virtual method in SqlAuthenticationProvider. Only Active Directory Interactive Authentication is supported.
        /// </summary>
        /// <param name="authenticationMethod">The SQL authentication method to check whether supported</param>
        /// <returns></returns>
        public override bool IsSupported(SqlAuthenticationMethod authenticationMethod)
        {
            return authenticationMethod == SqlAuthenticationMethod.ActiveDirectoryInteractive;
        }
    }

Any anyone explain why this fails unless the managed identity is made sql admin?

Answer 1

@asifkhana14 , you don't have to make the managed identity a SQL admin. You can grant the managed identity guid db_datareader role or whatever role you wish. Use managed identity connectivity walks you through how to add the managed identity to an Azure AD group and then add that group to the Azure SQL instance.