0

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.

Thomas
  • 24,234
  • 6
  • 81
  • 125
Lucky
  • 81
  • 6

2 Answers2

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
    }
  }
}
Thomas
  • 24,234
  • 6
  • 81
  • 125
  • I had tried with principal id for sid and now I even updated principalType but to no avail. I still have the same error – Lucky Mar 29 '23 at 05:46
  • I am now trying to use Microsoft.Sql/servers/administrators as a child resource and have added all the above as param, I will update if it works – Lucky Mar 29 '23 at 06:16
  • It works! I will post that as answer. – Lucky Mar 29 '23 at 06:54
  • I do have one question why the update to Application for principal type? The reason I am asking is I exported the arm template after manually doing what I wanted and it was Group, if you can point me to any documentation where it is clear please? – Lucky Mar 29 '23 at 06:55
  • i think it s kind of a bug, if you configure the admin to be a service principal / app , the type has to be an app. Group is for group. But whne you configure that via the portal it always set as a Group which is weird. – Thomas Mar 29 '23 at 09:05
  • Thank you for clarification, it sets Group from portal and that is why I went with it in my template as well. Anyways thanks again for edits and helping me reach the solution! – Lucky Mar 29 '23 at 09:25
  • 1
    Another observation even if I remove principalType parameter it works! – Lucky Mar 29 '23 at 09:42
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"
 }
}
Lucky
  • 81
  • 6