How do I use a User-Assigned Managed Identity to run an SQL statement on Azure SQL Database?
My goal is to run an application in a Azure DevOps Pipeline that updates an Azure Sql Database.
- The Task running is of type AzureCli@2, which uses a service connection
- The service connection is connected to a User-assigned Managed Identity (UMI) using federated credentials
- In the database I have added the user and roles:
CREATE USER [myManagedIdentityUser] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [myManagedIdentityUser];
ALTER ROLE db_datawriter ADD MEMBER [myManagedIdentityUser];
- The user exists in sys.database_principals, with SID:
- The application uses a connection string like this:
Server=tcp:xxx-xxxxx-sql.database.windows.net,1433;Database=MYDB;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Managed Identity;User Id=3bd0473f-****-****-****-fa4de647126d;. For User Id I have tried:- ObjectId of UMI
- Client Id of UMI
- Name of UMI
When running the task I get a 400 BAD REQUEST with message Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource.
Stacktrace (last few lines cropped to not reveal our own code):
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource.
Status: 400 (Bad Request)
Content:
{"error":"invalid_request","error_description":"Identity not found"}
Headers:
Server: IMDS/150.870.65.1475
x-ms-request-id: ecac674f-c410-4e8d-82dd-124a35329a95
Date: Thu, 27 Feb 2025 07:57:58 GMT
Content-Type: application/json; charset=utf-8
Content-Length: 68
---> Azure.Identity.CredentialUnavailableException: ManagedIdentityCredential authentication unavailable. The requested identity has not been assigned to this resource.
Status: 400 (Bad Request)
Content:
{"error":"invalid_request","error_description":"Identity not found"}
Headers:
Server: IMDS/150.870.65.1475
x-ms-request-id: ecac674f-c410-4e8d-82dd-124a35329a95
Date: Thu, 27 Feb 2025 07:57:58 GMT
Content-Type: application/json; charset=utf-8
Content-Length: 68
at Azure.Identity.ImdsManagedIdentitySource.HandleResponseAsync(Boolean async, TokenRequestContext context, HttpMessage message, CancellationToken cancellationToken)
at Azure.Identity.ManagedIdentitySource.AuthenticateAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
at Azure.Identity.ImdsManagedIdentitySource.AuthenticateAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
at Azure.Identity.ManagedIdentityClient.AuthenticateCoreAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
at Azure.Identity.ManagedIdentityClient.AppTokenProviderImpl(AppTokenProviderParameters parameters)
at Microsoft.Identity.Client.Internal.Requests.ClientCredentialRequest.SendTokenRequestToAppTokenProviderAsync(ILoggerAdapter logger, CancellationToken cancellationToken)
at Microsoft.Identity.Client.Internal.Requests.ClientCredentialRequest.GetAccessTokenAsync(CancellationToken cancellationToken, ILoggerAdapter logger)
at Microsoft.Identity.Client.Internal.Requests.ClientCredentialRequest.ExecuteAsync(CancellationToken cancellationToken)
at Microsoft.Identity.Client.Internal.Requests.RequestBase.<>c__DisplayClass11_1.<<RunAsync>b__1>d.MoveNext()
--- End of stack trace from previous location ---
at Microsoft.Identity.Client.Utils.StopwatchService.MeasureCodeBlockAsync(Func`1 codeBlock)
at Microsoft.Identity.Client.Internal.Requests.RequestBase.RunAsync(CancellationToken cancellationToken)
at Microsoft.Identity.Client.ApiConfig.Executors.ConfidentialClientExecutor.ExecuteAsync(AcquireTokenCommonParameters commonParameters, AcquireTokenForClientParameters clientParameters, CancellationToken cancellationToken)
at Azure.Identity.AbstractAcquireTokenParameterBuilderExtensions.ExecuteAsync[T](AbstractAcquireTokenParameterBuilder`1 builder, Boolean async, CancellationToken cancellationToken)
at Azure.Identity.MsalConfidentialClient.AcquireTokenForClientCoreAsync(String[] scopes, String tenantId, String claims, Boolean enableCae, Boolean async, CancellationToken cancellationToken)
at Azure.Identity.MsalConfidentialClient.AcquireTokenForClientAsync(String[] scopes, String tenantId, String claims, Boolean enableCae, Boolean async, CancellationToken cancellationToken)
at Azure.Identity.ManagedIdentityClient.AuthenticateAsync(Boolean async, TokenRequestContext context, CancellationToken cancellationToken)
at Azure.Identity.ManagedIdentityCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.CredentialDiagnosticScope.FailWrapAndThrow(Exception ex, String additionalMessage, Boolean isCredentialUnavailable)
at Azure.Identity.ManagedIdentityCredential.GetTokenImplAsync(Boolean async, TokenRequestContext requestContext, CancellationToken cancellationToken)
at Azure.Identity.ManagedIdentityCredential.GetTokenAsync(TokenRequestContext requestContext, CancellationToken cancellationToken)
at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.GetTokenAsync(TokenCredentialKey tokenCredentialKey, String secret, TokenRequestContext tokenRequestContext, CancellationToken cancellationToken)
at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.AcquireTokenAsync(SqlAuthenticationParameters parameters)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass149_1.<<GetFedAuthToken>b__1>d.MoveNext()
--- End of stack trace from previous location ---
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, TimeoutTimer timeout, Boolean withFailover)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, Func`3 accessTokenCallback)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
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.ProviderBase.DbConnectionClosed.TryOpenConnection(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.Data.SqlClient.SqlConnection.Open()
at Dapper.SqlMapper.ExecuteScalarImpl[T](IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 3022
UPDATE:
I removed User Id from connection string, since SqlClient cannot use access token if that field is set. This rendered another error message:
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'.
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, TimeoutTimer timeout, Boolean withFailover)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, Func`3 accessTokenCallback)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
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.ProviderBase.DbConnectionClosed.TryOpenConnection(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.Data.SqlClient.SqlConnection.Open()
What am I missing? Why can't I make this work?
Solution
This is what I ended up doing to solve my problem:
The stage is run in a
poolusing a VMSS Build Agent- Running in a hub VNET in Azure
- Hub VNET is peered to the spoke VNET where the DB is running
The AzureCli-task in the pipeline is using a service connection as
azureSubscription- This service connection is connected to a User-Assigned Managed Identity as described in the question
- This identity is added as user to the database, and is granted the roles needed
In the AzureCli-task I get an access token:
- No User Id, Password og Authentication in connection string
- The access token is passed to the application
In the application I use the access token, if it is set.
- task: AzureCLI@2
displayName: 'Run DB Updater application'
inputs:
scriptType: bash
scriptLocation: inlineScript
azureSubscription: 'dbupdate-dev-sc'
inlineScript: |
cd '$(Pipeline.Workspace)/DatabaseUpdater'
accessToken=$(az account get-access-token --resource https://database.windows.net/ --query "[accessToken]" -o tsv)
dotnet DatabaseUpdater.dll upgradedatabase "Server=tcp:mydb.database.windows.net,1433;Database=MYDB;Encrypt=True;TrustServerCertificate=False;" --token $accessToken
private SqlConnection GetSqlConnection()
{
var sqlConnection = new SqlConnection(ConnectionString);
if (!string.IsNullOrEmpty(_accessToken))
{
sqlConnection.AccessToken = _accessToken;
}
return sqlConnection;
}
- "Schema" property not found in Postgres Connector
- Downloading file from Azure blob storage via Memory Stream
- Reading env variable from template.yaml
- HTTP Error 500.30 - ASP.NET Core app failed to start - Azure
- Azure DevOps Pipelines: TerraformTaskV4: init with different subscription and Workflow Identity Federation
- Enabling HTTPS for a Azure blob static website
- How to search across many Azure Devops Git project and find all files which contain specific text AND which filename contains "Resource"
- Hosting SPA with Static Website option on Azure Blob Storage (clean URLs and Deep links)
- Using Managed Identity to Access Azure OpenAI Service
- Azure data factory not loading
- Transfer encrypted dataprotection keys from a windows vm to azure keyvault
- Refresh powerBI data with additional column
- Azure Application Insights AKS - How to Create 1 custom alert rule which will trigger multiple alerts, but with different names (per pod name)?
- gRPC and REST side by side in Azure App Service, Linux Container
- Connecting to a SignalR WSS stream but not getting the response i am looking for
- SignalR prevent user from opening multiple sessions in different tabs
- Application Insights -_logger.LogInformation
- CosmosDB is not allowing serverless capabalities to NoSQL, it says I must use CapacityMode
- Azure Webapp / Website Swap : HTTP Ping Error
- Microsoft Graph API - Update phoneMethods is no longer working
- Use Azure Key Vault secret in Header section of Web Activity
- Azure Storage blob getting the io.netty.channel.StacklessClosedChannelException while generating /downloading the from SasUrl
- How do I save and later load Azure AnalyzeResult object in python?
- Office 365 Exchange Online permission is not visible for registered application in azure active directory
- Azure ADF - HTTP : Table from list
- Self hosted azure devops build agent not getting tool from $PATH
- Azure devops pipeline stages template nesting
- Azure APIM is returning the incorrect HTTP response error when call is missing mandatory querystring parameter
- Azure DevOps build pipeline logid for a specific task - dynamically
- PS script to fetch the list non-compliance resource list with respect to policy from multiple subscriptions