CODEWITHSUNDEEP
azureazure-sql-databaseazure-resource-managerazure-managed-identityazure-bicep
Lucky
Lucky

Reputation: 303

Azure SQL with Managed Identity - Bicep

I am using bicep to create following resources

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.

Upvotes: 0

Views: 3483

Answers (2)

Lucky
Lucky

Reputation: 303

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"
 }
}

Upvotes: 0

Thomas
Thomas

Reputation: 29522

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
    }
  }
}

Upvotes: 3

Related Questions