Sample - Audit no Azure Active Directory administrator

Audit when there is no Azure Active Directory administrator assigned to the SQL server.

If you don't have an Azure subscription, create a free account before you begin.

Sample template

{
    "properties": {
        "displayName": "Audit If no AAD Admin",
        "description": "Aduit If there is no AAD Admin assigned to this server",
        "parameters": {
           
        },
        "policyRule": {
            "if": {
                "field": "type",
                "equals": "Microsoft.SQL/servers"
            },
            "then": {
                "effect": "auditIfNotExists",
                "details": {
                    "type": "Microsoft.SQL/servers/administrators"
                }
            }
        }
    }
}

You can deploy this template using the Azure portal, with PowerShell or with the Azure CLI.

Deploy with the portal

Deploy the Policy sample to Azure

Deploy with PowerShell

This sample requires Azure PowerShell. Run Get-Module -ListAvailable Az to find the version. If you need to install or upgrade, see Install Azure PowerShell module.

Run the Connect-AzAccount cmdlet to connect to Azure.

$definition = New-AzPolicyDefinition -Name "audit-if-no-sql-active-directory-admin" -DisplayName "Audit If no AAD Admin" -description "Aduit If there is no AAD Admin assigned to this server" -Policy 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/SQL/audit-if-no-sql-active-directory-admin/azurepolicy.rules.json' -Parameter 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/SQL/audit-if-no-sql-active-directory-admin/azurepolicy.parameters.json' -Mode All
$definition
$assignment = New-AzPolicyAssignment -Name <assignmentname> -Scope <scope>  -PolicyDefinition $definition
$assignment

Clean up PowerShell deployment

Run the following command to remove the resource group, VM, and all related resources.

Remove-AzResourceGroup -Name myResourceGroup

Deploy with Azure CLI

To run this sample, install the latest version of the Azure CLI. To start, run az login to create a connection with Azure.

Samples for the Azure CLI are written for the bash shell. To run this sample in Windows PowerShell or Command Prompt, you may need to change elements of the script.

az policy definition create --name 'audit-if-no-sql-active-directory-admin' --display-name 'Audit If no AAD Admin' --description 'Aduit If there is no AAD Admin assigned to this server' --rules 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/SQL/audit-if-no-sql-active-directory-admin/azurepolicy.rules.json' --params 'https://raw.githubusercontent.com/Azure/azure-policy/master/samples/SQL/audit-if-no-sql-active-directory-admin/azurepolicy.parameters.json' --mode All

az policy assignment create --name <assignmentname> --scope <scope> --policy "audit-if-no-sql-active-directory-admin" 

Clean up Azure CLI deployment

Run the following command to remove the resource group, VM, and all related resources.

az group delete --name myResourceGroup --yes

Next steps