Assigning an Active Directory Administrator to an Azure SQL instance through ARM Templates

Question

Is it possible to assign an Active Directory Administrator to an Azure SQL Instance within an ARM resource template? I'm trying to automate the deployment of a database server, but I only seem to be able to specify the local server administration credentials.

        "properties": {
            "administratorLogin": "[parameters('databaseAdministratorLogin')]",
            "administratorLoginPassword": "[parameters('databaseAdministratorPassword')]",
            "version": "12.0"
        },

There doesn't seem to be anywhere that I can specify a particular Azure AD Administrator beyond that.

score 27 · Accepted Answer · answered Oct 14 '16 at 14:12

Microsoft reached out to me and provided a sample resource template to accomplish this:

{
    "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "SQL Administrator Login": {
            "type": "String"
        },
        "SQL Administrator Password": {
            "type": "SecureString"
        },
        "AAD Admin Login": {
            "type": "String"
        },
        "AAD Admin ObjectID": {
            "type": "String"
        },
        "AAD TenantId": {
            "type": "String"
        },
        "Location (Region)": {
            "type": "String"
        },
        "Server Name": {
            "type": "String"
        }
    },
    "variables": {},
    "resources": [
        {
            "type": "Microsoft.Sql/servers",
            "name": "[parameters('Server Name')]",
            "apiVersion": "2014-04-01-preview",
            "location": "[parameters('Location (Region)')]",
            "properties": {
                "administratorLogin": "[parameters('SQL Administrator Login')]",
                "administratorLoginPassword": "[parameters('SQL Administrator Password')]",
                "version": "12.0"
            },
            "resources": [
                {
                    "type": "firewallrules",
                    "name": "AllowAllWindowsAzureIps",
                    "apiVersion": "2014-04-01-preview",
                    "location": "[parameters('Location (Region)')]",
                    "properties": {
                        "endIpAddress": "0.0.0.0",
                       "startIpAddress": "0.0.0.0"
                    },
                    "dependsOn": [
                        "[concat('Microsoft.Sql/servers/', parameters('Server Name'))]"
                    ]
                },
                {
                    "type": "administrators",
                    "name": "activeDirectory",
                    "apiVersion": "2014-04-01-preview",
                    "location": "[parameters('Location (Region)')]",
                    "properties": {
                        "administratorType": "ActiveDirectory",
                        "login": "[parameters('AAD Admin Login')]",
                        "sid": "[parameters('AAD Admin ObjectID')]",
                        "tenantId": "[parameters('AAD TenantID')]"
                    },
                    "dependsOn": [
                        "[concat('Microsoft.Sql/servers/', parameters('Server Name'))]"
                    ]
                }
            ]
        }
    ]
}

Do you know if it is possible to create a non-administrative login? Can I create roles and assign a user to those roles in an ARM template? — Paul, May 26 '17 at 23:18
I think that non-admin logins are just handled at the database level. This isn't something you'd put in an ARM template, but would rather just be a part of the database's schema. — mclark1129, May 30 '17 at 16:33
any way to look up the SID (or just provide a user / group name?) — Murray Foxcroft, Jun 01 '18 at 12:38
Assuming you have permissions to view user information, you should be able to see this information in the Azure portal, Azure Powershell, or via the Azure AD Graph Explorer. — mclark1129, Jun 01 '18 at 15:05

score 0 · Answer 2 · answered Jun 29 '18 at 01:14

Modification: The above won;t run due to:

CloudException: Deployment template validation failed: 'The template resource 'activeDirectory' for type 'Microsoft.Sql/servers/administrators' at line '168' and column '9' has incorrect segment lengths. A nested resource type must have identical number of segments as its resource name. A root resource type must have segment length one greater than its resource name. Please see https://aka.ms/arm-template/#resources for usage details.'.

{
  "type": "Microsoft.Sql/servers/administrators",
  "name": "[concat(parameters('servers_tbg_backoffice_name'), '/', 'activeDirectory' )]",
  "apiVersion": "2014-04-01-preview",
  //"location": "[parameters('Location (Region)')]",
  "properties": {
    "administratorType": "ActiveDirectory",
    "login": "[parameters('AAD Admin Login')]",
    "sid": "[parameters('AAD Admin ObjectID')]",
    "tenantId": "[parameters('AAD TenantID')]"
  },
  "dependsOn": [
    "[concat('Microsoft.Sql/servers/', parameters('servers_tbg_backoffice_name'))]"
  ]
},

Any chance one of your parameters has a `/` in the name? – mclark1129 Jun 29 '18 at 17:32 — mclark1129, Jun 29 '18 at 17:32

Assigning an Active Directory Administrator to an Azure SQL instance through ARM Templates

2 Answers2