Creating User managed identity and server administrator using ARM Template

Question

I want to add a user managed identity as admin to a sql server resource in azure. I can create the user identity using ARM Templates like this:

{
    "type": "Microsoft.ManagedIdentity/userAssignedIdentities",
    "name": "[variables('identityName')]",
    "apiVersion": "2018-11-30",
    "location": "[resourceGroup().location]"
},

and I can create a administrator resource from ARM template as well. But how do I reference the above created identity in the administrator resource? :

{
    "name": "[concat(variables('serverName'),'/ActiveDirectory')]",
    "type": "Microsoft.Sql/servers/administrators",
    "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', variables('serverName'))]"
    ],
    "apiVersion": "2019-06-01-preview",
    "properties": {
        "administratorType": "ActiveDirectory",
        "login": "[parameters('identityName')]",
        "sid": "<How do I add reference here>",
        "tenantId": "<How do I add reference here>"
    }
}

We cannot set service principal as Azure AD admin in Azure SQL database. We just can add service principal as AD group's member then set the group as AD admin. For more details, please refer to https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview#azure-ad-features-and-limitations — Jim Xu, Dec 15 '20 at 08:59

score 0 · Answer 1 · answered Dec 15 '20 at 09:02

I tried something and the following seems to be working fine:

{
            "name": "[concat(variables('serverName'),'/ActiveDirectory')]",
            "type": "Microsoft.Sql/servers/administrators",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', variables('serverName'))]",
                "[resourceId('Microsoft.ManagedIdentity/userAssignedIdentities', variables('identityName'))]"
            ],
            "apiVersion": "2019-06-01-preview",
            "properties": {
                "administratorType": "ActiveDirectory",
                "login": "[variables('identityName')]",
                "sid": "[reference(concat('Microsoft.ManagedIdentity/userAssignedIdentities/', variables('identityName'))).clientId]",
                "tenantId": "[reference(concat('Microsoft.ManagedIdentity/userAssignedIdentities/', variables('identityName'))).tenantId]"
            }
        },

Creating User managed identity and server administrator using ARM Template

1 Answers1