'Azure SQL Azure AD Only Authentication enforcement

I'm trying to enforce Azure AD Only Autentication on Azure SQL Server. There is already an Built-In Policy which enforces it only for newly created Resources but there is still the possibility after creation to change it back to Local SQL Admin Authentication, this gap I want to close with Azure Policy.

I tried already to create a Policy with "azureADOnlyAuthentication" property. but this did not work and I don't get it. Does someone have any idea?

    "policyRule": {
      "if": {
        "allOf": [
          {
            "field": "type",
            "equals": "Microsoft.Sql/servers"
          },
          {
            "field": "Microsoft.Sql/servers/administrators.azureADOnlyAuthentication",
            "notequals": true
          }
        ]
      },
      "then": {
        "effect": "[parameters('effect')]"
      }
    }
  },
azureazure-active-directoryazure-policy


Solution 1:[1]

While creating a policy for Azure AD only authentication in SQL server, make sure to include below attributes:

  • The Policy Effect: DeployIfNotExist : If condition is not met it will generate a deployment.
  • The ExistenceCondition : If Azure SQL Server does not accept Azure AD Authentication only, then it executes a deployment.
  • Deployment : Deployment property contains the ARM template, which is incremental. The parameter is filled with the expression [field(‘name’)]

Please check the below sample if helpful:

"policyRule":{
      "if":{
         "allOf":[
            {
               "field":"type",
               "equals":"Microsoft.Sql/servers"
            }
         ]
      },
      "then":{
         "effect":"deployIfNotExists",
         "details":{
            "type":"Microsoft.Sql/servers/azureADOnlyAuthentications", 
            "existenceCondition":{
               "allOf":[
                  {
                     "field":"Microsoft.Sql/servers/azureADOnlyAuthentications/azureADOnlyAuthentication",
                     "equals":true
                  }
               ]
            },
            "deployment":{
               "properties":{
                  "mode":"incremental",
                  "name":"Default",}}

For more in detail please refer below link:

Azure SQL: Enforcing Azure AD Only Authentication - Simple Talk (red-gate.com)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RukminiMr-MT

Related Questions