I recently configured our Azure App Service and Azure SQL Server to use managed identity access. The App Service has System assigned managed identity and has the correct assigned role as Owner for the SQL database. When I publish my code to the app service it works just fine and is able to access the database.
My issue is that I cannot connect to the database through my app when running it locally through Visual Studio. My Azure account is set as the Azure Active Directory admin on SQL Server and I'm able to open up the connection and run queries through Azure Data Studio when I'm signed in.
I'm using the same account through Visual Studio, even unlinked and relinked it under the Tools -> Options -> Azure Service Authentication / Account Selection. But when I try and run the app locally I get the error ManagedIdentityCredential authentication unavailable. Multiple attempts failed to obtain a token from the managed identity endpoint.
The error doesn't give me much to work with because I'm not sure how to see the full view of what is being tried in order to authenticate. I'm not using any code on my end to obtain an access token since I'm just letting the Microsoft SQLClient obtain it for me through my connection string.
Here is the full stacktrace of the error, anything that can point me in the right direction would be appreciated. My guess is it has something to do with Visual Studios probably needing to be allowed to get an access token on my behalf, but I figured since I'm signed into my azure account it might not matter.
at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1.Enumerator.MoveNext()
at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
Azure.RequestFailedException: A socket operation was attempted to an unreachable network. (169.254.169.254:80)
---> System.Net.Http.HttpRequestException: A socket operation was attempted to an unreachable network. (169.254.169.254:80)
---> System.Net.Sockets.SocketException (10051): A socket operation was attempted to an unreachable network.
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.ThrowException(SocketError error, CancellationToken cancellationToken)
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
at System.Net.Sockets.Socket.<ConnectAsync>g__WaitForConnectWithCancellation|277_0(AwaitableSocketAsyncEventArgs saea, ValueTask connectTask, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.ConnectToTcpHostAsync(String host, Int32 port, HttpRequestMessage initialRequest, Boolean async, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at System.Net.Http.HttpConnectionPool.ConnectToTcpHostAsync(String host, Int32 port, HttpRequestMessage initialRequest, Boolean async, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.ConnectAsync(HttpRequestMessage request, Boolean async, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.CreateHttp11ConnectionAsync(HttpRequestMessage request, Boolean async, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.AddHttp11ConnectionAsync(HttpRequestMessage request)
at System.Threading.Tasks.TaskCompletionSourceWithCancellation`1.WaitWithCancellationAsync(CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.GetHttp11ConnectionAsync(HttpRequestMessage request, Boolean async, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.SendWithVersionDetectionAndRetryAsync(HttpRequestMessage request, Boolean async, Boolean doRequestAuth, CancellationToken cancellationToken)
at System.Net.Http.DiagnosticsHandler.SendAsyncCore(HttpRequestMessage request, Boolean async, CancellationToken cancellationToken)
at System.Net.Http.HttpClient.<SendAsync>g__Core|83_0(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationTokenSource cts, Boolean disposeCts, CancellationTokenSource pendingRequestsCts, CancellationToken originalCancellationToken)
at Azure.Core.Pipeline.HttpClientTransport.ProcessAsync(HttpMessage message, Boolean async)
--- End of inner exception stack trace ---
at Azure.Core.Pipeline.HttpClientTransport.ProcessAsync(HttpMessage message, Boolean async)
at Azure.Core.Pipeline.HttpPipelineTransportPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline)
at Azure.Core.Pipeline.RequestActivityPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
at Azure.Core.Pipeline.ResponseBodyPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
at Azure.Core.Pipeline.RedirectPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
at Azure.Core.Pipeline.RetryPolicy.ProcessAsync(HttpMessage message, ReadOnlyMemory`1 pipeline, Boolean async)
at Azure.Identity.ImdsManagedIdentitySource.<AuthenticateAsync>d__15.MoveNext()
at Azure.Identity.ManagedIdentityClient.<AuthenticateCoreAsync>d__17.MoveNext()
at Azure.Identity.ManagedIdentityClient.<AppTokenProviderImpl>d__18.MoveNext()
at Microsoft.Identity.Client.Internal.Requests.ClientCredentialRequest.<SendTokenRequestToProviderAsync>d__4.MoveNext()
at Microsoft.Identity.Client.Internal.Requests.ClientCredentialRequest.<FetchNewAccessTokenAsync>d__3.MoveNext()
at Microsoft.Identity.Client.Internal.Requests.ClientCredentialRequest.<ExecuteAsync>d__2.MoveNext()
at Microsoft.Identity.Client.Internal.Requests.RequestBase.<RunAsync>d__12.MoveNext()
at Microsoft.Identity.Client.ApiConfig.Executors.ConfidentialClientExecutor.<ExecuteAsync>d__3.MoveNext()
at Azure.Identity.AbstractAcquireTokenParameterBuilderExtensions.<ExecuteAsync>d__0`1.MoveNext()
at Azure.Identity.MsalConfidentialClient.<AcquireTokenForClientCoreAsync>d__21.MoveNext()
at Azure.Identity.MsalConfidentialClient.<AcquireTokenForClientAsync>d__20.MoveNext()
at Azure.Identity.ManagedIdentityClient.<AuthenticateAsync>d__16.MoveNext()
at Azure.Identity.ManagedIdentityCredential.<GetTokenImplAsync>d__16.MoveNext()
at
Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage)
at Azure.Identity.ManagedIdentityCredential.<GetTokenImplAsync>d__16.MoveNext()
at Azure.Identity.ManagedIdentityCredential.<GetTokenAsync>d__14.MoveNext()
at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.<AcquireTokenAsync>d__17.MoveNext()
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass147_1.<<GetFedAuthToken>b__1>d.MoveNext()
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
Relevant connection DbContext information. Currently using version 7.0.3 of Microsoft.EntityFrameworkCore for the DbContext class
public class DbEntities: DbContext
{
public DbEntities() : base() { }
//All DbSet<...> classes here
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(Environment.GetEnvironmentVariable("DB_CONNECTION_STRING"));
}
}
Relevant AppSettings JSON file
{
"ConnectionStrings": {
"DB_CONNECTION_STRING": "Server=tcp:MY-SERVER.database.windows.net;Initial Catalog=development;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=\"Active Directory Managed Identity\";"
},
}
By using Authentication=\"Active Directory Managed Identity\" you will tell your application to use only managed identity authentication. This can only be used if you are actually running as an Azure resource. You cannot use Managed Identity authentication with your personal account.
By using Active Directory Default as the value, the system will try multiple variants:
Authenticate with an Azure AD identity by using password-less and non-interactive mechanisms including Managed Identities, Visual Studio Code, Visual Studio, Azure CLI, etc.