Question 103196 in Azure Question posted in
The official documentation to get you started can be found here.

Azure SQL with Managed Identity – Bicep

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Please refer this link https://www.codez.one/azure-sql-with-managed-identities-part-2/

    Adding the Microsoft.Sql/servers/administrators@2021-11-01 as child resource worked for me

    resource sqlAdmin 'Microsoft.SQl/servers/administrators@2021-11-01' = {
 parent: sqlServer
 name: 'ActiveDirectory'
 properties: administrators//passed from main.bicep param file
}

    param file

    "administrators": {
 "value": {
   "administratorType: "ActiveDirectory",
   "azureADOOnlyAuthentication: true,
   "tenantId": "your user assigned managed id's tenant id",
   "sid": "your user assigned managed id's principal id"
   "login": "user assigned managed identity name"
 }
}

  2. 0

    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
    }
  }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search