Azure SQL with Managed Identity - Bicep

Question

I am using bicep to create following resources

• SQL Server with multiple databases
• Multiple App Services that need to access these Azure SQL Db's

I have created a user assigned managed identity resource and assigned to all the app services.

I want to add the identity as admin in Sql server (Portal -> Select Sql Server resource -> Under Settings Select Azure Active Directory -> Set admin) But I am getting following error:

Invalid value given for parameter ExternalAdministraorLoginSid. Specify a valid parameter value.

This is the sql server bicep:

resource sqlserver 'Microsoft.Sql/servers@2021-11-01-preview' = {
  name: 'sqlserver${uniqueString(resourceGroup().id)}'
  location: location
  properties: {
    administratorLogin: sqlAdministratorLogin
    administratorLoginPassword: sqlAdministratorPassword
    version: '12.0'
    administrators: {
     administraorType: 'ActiveDirectory'
     principalType: 'Group'
     azureADOOnlyAuthentication: true
     login: 'userAssignedManagedIdName'
     sid: 'UserAssignedManagedID-Client-ID' // not actual value
     tenantId: 'UserAssignedManagedID-Tenant-ID ' // not actual value
   }
  }
}

I took both the values from the managed identity properties tab under Settings.

Answer 1

You need to use the principalId (objectId of the service principal) property of the managed identity resource. Also the principalType needs to be Application:

resource managedIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2022-01-31-preview' = {
  name: '<userAssignedManagedIdName>'
  location: location
}

resource sqlserver 'Microsoft.Sql/servers@2021-11-01-preview' = {
  name: 'sqlserver${uniqueString(resourceGroup().id)}'
  location: location
  properties: {
    administratorLogin: sqlAdministratorLogin
    administratorLoginPassword: sqlAdministratorPassword
    version: '12.0'
    administrators: {
      administratorType: 'ActiveDirectory'
      azureADOnlyAuthentication: true
      principalType: 'Application'
      login: managedIdentity.name
      sid: managedIdentity.properties.principalId
      tenantId: managedIdentity.properties.tenantId
    }
  }
}