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?