Managed Identity Sql Auth with EF Core - Login failed for user '<token-identified principal>'

Question

I have a dotnet 5 (isolated) Azure Function app that needs to access an Azure Sql Server database via EF Core 5. I would like to use the managed identity of the function app when making the sql server requests.

What I tried

I followed the instructions here.

I created a new AD account called "smsrouterdb" and made this the Azure Sql Admin. The name of my function app is "func-smsrouter-msdn-01". So after logging into the DB via SSMS as "smsrouterdb", I created a contained user as below:

CREATE USER [func-smsrouter-msdn-01] FROM EXTERNAL PROVIDER
ALTER ROLE db_datawriter ADD MEMBER [func-smsrouter-msdn-01]
ALTER ROLE db_datareader ADD MEMBER [func-smsrouter-msdn-01]

I then triggered my function app via an http request.

What happened

I got the following error from the function app:

One or more errors occurred. (Invalid value for key 'authentication'.) ---> System.ArgumentException: Invalid value for key 'authentication'. at Microsoft.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(String keyword, Object value)

I realised that this was because an old version of the nuget package Microsoft.Data.SqlClient was being referenced. So, I explicitly added a reference to v3.0.0.

I then got the following error

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

However, if I change the connection string's authentication property to "Active Directory Interactive" and 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 <tg name> --server-name <server name> --display-name MSIAzureAdmin --object-id "id of managed identity here"

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

Config

The nuget packages of the project containing the dbcontext are:

"Microsoft.Azure.Services.AppAuthentication" Version="1.6.1"
"Microsoft.EntityFrameworkCore" Version="5.0.6"
"Microsoft.EntityFrameworkCore.Design" Version="5.0.6"
"Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.6"
"WindowsAzure.Storage" Version="9.3.3" />

From the main Azure Function project, I have references to the following nuget packages:

Microsoft.Data.SqlClient 3.0.0
Serilog.Sinks.MSSqlServer 5.6.0

The only code in my db context is:

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            SqlConnection connection = new SqlConnection();
            logger.LogInformation($"SqlSvrConString=[{sharedConfig[ConfigConstants.SqlSvrConnString]}]");
            connection.ConnectionString = sharedConfig[ConfigConstants.SqlSvrConnString];
            optionsBuilder.UseSqlServer(connection);
        }

My connection string is:

Server=servernamehere.database.windows.net;Initial Catalog=dbnamehere;Authentication=Active Directory Managed Identity;

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

Answer 1

I think the root cause of the problem was that when I'd issued the command: CREATE USER [function name here] FROM EXTERNAL PROVIDER, although the function name was spelled correctly, the case was incorrect.