Connect Azure CA to SQL Server with user assigned managed identity

Question

I have setup a resource group on Azure in which I have a SQL Server with one DB.

I'm trying to connect a container app I have in this same resource group to the this DB by using a UAI I have setup for this container by using this connection string:

env: [
    { name: 'AZURE_CLIENT_ID', value: identity.properties.clientId }
    { name: 'AZURE_TENANT_ID', value: tenant().tenantId }
    { name: 'ConnectionString', value: Server=${databaseHost},1433;Database=${databaseName};Authentication=Active Directory Managed Identity;User ID=${identity.properties.principalId}' } 
]

All of this is done via bicep files as you can see in this snippet.

When my deployment is done, everything deploys fine and I can see that the identity of my container app is set properly. This UAI is also member of a DbAdmin security group I have created and assigned as owner of my SQL Server.

Here is the setup for my SQL Server and DB:

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: serverName
  location: location
  tags: tags
  properties: {
    administrators: {
      administratorType: 'ActiveDirectory'
      azureADOnlyAuthentication: true
      principalType: 'Group'
      login: sqlAdminGroupName
      sid: sqlAdminGroupObjectId
      tenantId: subscription().tenantId
    }
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Enabled'
    restrictOutboundNetworkAccess: 'Disabled'
  }  
  identity: {
    type: 'SystemAssigned'
  }
}

resource sqlDB 'Microsoft.Sql/servers/databases@2022-05-01-preview' = {
  parent: sqlServer
  name: sqlDBName
  location: location
  tags: tags
  sku: {
    name: 'Basic'
    tier: 'Basic'
    capacity: 5
  }
  properties: {
    requestedBackupStorageRedundancy: 'Zone'
  }
}

With all this setup properly and deployed successfully, I still get this error in the logs of my container app:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): ManagedIdentityCredential authentication failed: Service request failed.

I can't figure out what is missing in my config/setup for it to work. I would really like for this to work since not having to setup a password seems to be the best option to me. I tried multiple tweaks in my configs, bicep files and directly on the portal without success.

Answer 1

Apparently, I had two problems with my connection string:

1. I had to use the clientId instead of principalId of my UAI for the User Id,
2. Encrypt=True was missing from my connection string

My final ConnectionString looks like this: Server=${databaseHost},1433;Database=${databaseName};Authentication=Active Directory Managed Identity;User ID=${identity.properties.clientId}'; Encrypt=True

I also updated all of my nuget packages to use their latest versions