How to add the Azure Active Directory Administrator on a SQL Server to an Azure group once the SQL server deploys

Question

I am trying to create an automated process that adds the Azure Active Directory on a SQL Server to an Azure group once the SQL server deploys. The group I want to add too is a security group. I am thinking about using Azure policy to implement this. What would the policy rule look like? If there is a better Azure service/feature to implement my task what is it?

Answer 1

@Dimeji Olayinka Here is sample policy definition that has worked for me which evaluates and provides the complaint and non-complaint resources on the existing resources. Also kindly note, during an evaluation cycle, policy definitions with a "DeployIfNotExists" effect that match resources are marked as non-compliant, but no action is taken on that resource. Existing non-compliant resources can be remediated with a remediation task.

{  
  "mode": "All",  
  "policyRule": {  
    "if": {  
      "allOf": [  
        {  
          "field": "type",  
          "equals": "Microsoft.Sql/servers"  
        }  
      ]  
    },  
    "then": {  
      "effect": "deployIfNotExists",  
      "details": {  
        "type": "Microsoft.Sql/servers/administrators",  
        "existenceCondition": {  
          "allOf": [  
            {  
              "field": "Microsoft.Sql/servers/administrators/administratorType",  
              "equals": "ActiveDirectory"  
            },  
            {  
              "field": "Microsoft.Sql/servers/administrators/login",  
              "equals": "xxxx@xxxxxx.com"  
            },  
            {  
              "field": "Microsoft.Sql/servers/administrators/sid",  
              "equals": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx"  
            },  
            {  
              "field": "Microsoft.Sql/servers/administrators/tenantId",  
              "equals": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx"  
            }  
          ]  
        },  
        "deployment": {  
          "properties": {  
            "mode": "incremental",  
            "template": {  
              "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",  
              "contentVersion": "1.0.0.0",  
              "parameters": {  
                "location": {  
                  "type": "string"  
                },  
                "sqlServerName": {  
                  "type": "string"  
                }  
              },  
              "variables": {},  
              "resources": [  
                {  
                  "name": "[parameters('sqlServerName')]",  
                  "type": "Microsoft.Sql/servers",  
                  "apiVersion": "2019-06-01-preview",  
                  "location": "[parameters('location')]",  
                  "resources": [  
                    {  
                      "type": "Microsoft.Sql/servers/administrators",  
                      "apiVersion": "2019-06-01-preview",  
                      "name": "[concat(parameters('sqlServerName'), '/ActiveDirectory')]",  
                      "dependsOn": [  
                        "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"  
                      ],  
                      "properties": {  
                        "administratorType": "ActiveDirectory",  
                        "login": "xxxx@xxxxxx.com",  
                        "sid": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx",  
                        "tenantId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxx"  
                      }  
                    }  
                  ]  
                }  
              ]  
            },  
            "parameters": {  
              "sqlServerName": {  
                "value": "[field('Name')]"  
              },  
              "location": {  
                "value": "[field('Location')]"  
              }  
            }  
          }  
        },  
        "roleDefinitionIds": [  
          "/providers/Microsoft.Authorization/roleDefinitions/b24988ac-6180-42a0-ab88-20f7382dd24c",  
          "/providers/Microsoft.Authorization/roleDefinitions/8e3af657-a8ff-443c-a75c-2fe8c4bcb635"  
        ]  
      }  
    }  
  },  
  "parameters": {}  
}