0

Similar question to Azure.Identity.AuthenticationFailedException: ManagedIdentityCredential authentication failed: Service request failed but that post was about a user-assigned managed identity against a key vault, this is a system-managed identity against an Azure SQL Server, and the answer doesn't seem to apply.

I've got a dev app service that I'm deploying to. I have a SQL Server allowing my app service managed identity as a DB Contributor, and I've done this sort of code against the database:

alter role db_datareader add member [MYCOMPANY-app-dev];
alter role db_datawriter add member [MYCOMPANY-app-dev];
alter role db_ddladmin add member [MYCOMPANY-app-dev];

First thing on startup, the app runs an EF Core migration to set up the db, and I get a series of weird stuff in the logs that I don't understand:

2022-11-04T02:33:40.537974812Z Trying to find the startup DLL name...
2022-11-04T02:33:40.538687218Z Found the startup D name: MYCOMPANY.Reports.App.WebApi.dll
2022-11-04T02:33:40.538850019Z Running the command: dotnet "MYCOMPANY.Reports.App.WebApi.dll"
2022-11-04T02:33:49.203050895Z info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
2022-11-04T02:33:49.203089395Z       Entity Framework Core 6.0.10 initialized 'SqlAppDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer:6.0.10' with options: None
2022-11-04T02:33:51.676052939Z Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): ManagedIdentityCredential authentication failed: Service request failed.
2022-11-04T02:33:51.676089539Z Status: 400 (Bad Request)
2022-11-04T02:33:51.676095839Z 
2022-11-04T02:33:51.676099739Z Content:
2022-11-04T02:33:51.676103539Z 
2022-11-04T02:33:51.676107039Z 
2022-11-04T02:33:51.676110639Z Headers:
2022-11-04T02:33:51.676114439Z Date: Fri, 04 Nov 2022 02:33:50 GMT
2022-11-04T02:33:51.676118339Z Server: Kestrel
2022-11-04T02:33:51.676121940Z Transfer-Encoding: chunked
2022-11-04T02:33:51.676134340Z X-CORRELATION-ID: REDACTED
2022-11-04T02:33:51.676138440Z Content-Type: application/json; charset=utf-8
2022-11-04T02:33:51.676158840Z 
2022-11-04T02:33:51.676162040Z See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/managedidentitycredential/troubleshoot
2022-11-04T02:33:51.676165440Z  ---> Azure.Identity.AuthenticationFailedException: ManagedIdentityCredential authentication failed: Service request failed.
2022-11-04T02:33:51.676169640Z Status: 400 (Bad Request)
2022-11-04T02:33:51.676173340Z 
2022-11-04T02:33:51.676176440Z Content:
2022-11-04T02:33:51.676179640Z 
2022-11-04T02:33:51.676182740Z 
2022-11-04T02:33:51.676185840Z Headers:
2022-11-04T02:33:51.676188940Z Date: Fri, 04 Nov 2022 02:33:50 GMT
2022-11-04T02:33:51.676192140Z Server: Kestrel
2022-11-04T02:33:51.676195440Z Transfer-Encoding: chunked
2022-11-04T02:33:51.676198540Z X-CORRELATION-ID: REDACTED
2022-11-04T02:33:51.676201740Z Content-Type: application/json; charset=utf-8
2022-11-04T02:33:51.676204940Z 
2022-11-04T02:33:51.676208140Z See the troubleshooting guide for more information. https://aka.ms/azsdk/net/identity/managedidentitycredential/troubleshoot
2022-11-04T02:33:51.676211440Z  ---> Azure.RequestFailedException: Service request failed.
2022-11-04T02:33:51.676214740Z Status: 400 (Bad Request)
2022-11-04T02:33:51.676217940Z 
2022-11-04T02:33:51.676221140Z Content:
2022-11-04T02:33:51.676224240Z 
2022-11-04T02:33:51.676227240Z 
2022-11-04T02:33:51.676230340Z Headers:
2022-11-04T02:33:51.676233440Z Date: Fri, 04 Nov 2022 02:33:50 GMT
2022-11-04T02:33:51.676236740Z Server: Kestrel
2022-11-04T02:33:51.676239940Z Transfer-Encoding: chunked
2022-11-04T02:33:51.676243040Z X-CORRELATION-ID: REDACTED
2022-11-04T02:33:51.676246240Z Content-Type: application/json; charset=utf-8
2022-11-04T02:33:51.676249440Z 
2022-11-04T02:33:51.676252640Z    at Azure.Identity.ManagedIdentitySource.HandleResponseAsync(Boolean async, TokenRequestContext context, Response response, CancellationToken cancellationToken)
2022-11-04T02:33:51.676256040Z    at Azure.Identity.ManagedIdentitySource.AuthenticateAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
2022-11-04T02:33:51.676259441Z    at Azure.Identity.ManagedIdentityClient.AuthenticateAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
2022-11-04T02:33:51.676262841Z    at Azure.Identity.ManagedIdentityCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
2022-11-04T02:33:51.676266141Z    --- End of inner exception stack trace ---
2022-11-04T02:33:51.676272841Z    at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage)
2022-11-04T02:33:51.676277441Z    at Azure.Identity.ManagedIdentityCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
2022-11-04T02:33:51.676280941Z    at Azure.Identity.ManagedIdentityCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
2022-11-04T02:33:51.676284341Z    at Azure.Identity.DefaultAzureCredential.GetTokenFromSourcesAsync(TokenCredential[] sources, TokenRequestContext requestContext, Boolean async, CancellationToken cancellationToken)
2022-11-04T02:33:51.676287741Z    at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
2022-11-04T02:33:51.676291041Z    at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage)
2022-11-04T02:33:51.676294341Z    at Azure.Identity.DefaultAzureCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
2022-11-04T02:33:51.676297641Z    at Azure.Identity.DefaultAzureCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
2022-11-04T02:33:51.676300941Z    at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenAsync(SqlAuthenticationParameters parameters)
2022-11-04T02:33:51.676304241Z    at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass147_1.<<GetFedAuthToken>b__1>d.MoveNext()
2022-11-04T02:33:51.676307941Z --- End of stack trace from previous location ---
2022-11-04T02:33:51.676311141Z    at Microsoft.Data.SqlCliesetlnt.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
2022-11-04T02:33:51.676314441Z    at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e)
2022-11-04T02:33:51.676317741Z    at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
2022-11-04T02:33:51.676321041Z    at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
2022-11-04T02:33:51.676324441Z    at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
2022-11-04T02:33:51.676328341Z    at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
2022-11-04T02:33:51.676331941Z    at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
2022-11-04T02:33:51.676335441Z    at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
2022-11-04T02:33:51.676341841Z    at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
2022-11-04T02:33:51.676345541Z    at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
2022-11-04T02:33:51.676348841Z    at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
2022-11-04T02:33:51.676352141Z    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.OpenDbConnection(Boolean errorsExpected)
2022-11-04T02:33:51.676355441Z    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
2022-11-04T02:33:51.676358741Z    at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
2022-11-04T02:33:51.676361941Z    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.<>c__DisplayClass18_0.<Exists>b__0(DateTime giveUp)
2022-11-04T02:33:51.676365541Z    at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
2022-11-04T02:33:51.676369041Z    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
2022-11-04T02:33:51.676372441Z    at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func`2 operation, Func`2 verifySucceeded)
2022-11-04T02:33:51.676375841Z    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists(Boolean retryOnNotExists)
2022-11-04T02:33:51.676379441Z    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerDatabaseCreator.Exists()
2022-11-04T02:33:51.676382741Z    at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
2022-11-04T02:33:51.676385941Z    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
2022-11-04T02:33:51.676389241Z    at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
2022-11-04T02:33:51.676392541Z    at Program.<Main>$(String[] args) in /home/runner/work/MYCOMPANY/MYCOMPANY/MYCOMPANY.Reports.App.WebApi/Program.cs:line 47

My connection string looks like this:

server=MYCOMPANY-sql.database.windows.net;database=MYCOMPANY-api-dev;UID=MYCOMPANY-app-dev;Authentication=Active Directory Default

As far as I can tell, this should work, but clearly it doesn't, and the error message is singularly unhelpful. Can someone tell me what I'm either doing wrong, or in what direction I can troubleshoot?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254
  • So is `UID=MYCOMPANY-app-dev` the User ID of a User-Assigned Managed Identity? Has that managed identity been assigned to the deployment environment? Ref: [Using Active Directory Default authentication](https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication#using-active-directory-default-authentication) – AlwaysLearning Nov 04 '22 at 03:41
  • Use SQL Server Management Studio to connect to database. The server name is shown on the login window which is an server\instance. Check in the explorer under databases that you database name in connection string is correct. Then in explorer Management tab check log file. The log file should show errors when you attempted to connect to the database and reason for failure. – jdweng Nov 04 '22 at 07:04
  • @AlwaysLearning not clear on what you're asking? I set up the app service to use a managed identity, doesn't that "assign" it? – Jeremy Holovacs Nov 04 '22 at 11:48

1 Answers1

0

OK @AlwaysLearning put me on the right track: I needed to remove the UID from my connection string. Even though it was the same user id as the managed identity, the system got confused and gave that frustrating error message. Everything immediately started working properly as soon as I removed that parameter and restarted the app service.

Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254