Azure SQL: Enforcing SQL Authentication type

Some time ago I wrote a blog about the just released feature to allow only Azure Authentication in Azure SQL Databases.

 

On that blog, I explained not only about the feature, but how to use Azure Policies, a great feature for governance, to control which Azure SQL are correctly configured and which is not, even in a world-wide scenario.

From that time to today, a lot of things happened. The feature was in preview. The structure of the ARM template for this feature changed, making the policies I create not work anymore. After a while, Microsoft reverted the changes or something similar and the policies started work again. Now I can publish this new blog post.

The policy from the previous blog post can only to check if the Azure SQL is using Azure Authentication only or if it is accepting Azure SQL Authentication as well.

This time, let’s analyse a policy not only to check, but to change this configuration.

The policy is this one:

{
   "parameters":{
      
   },
   "policyRule":{
      "if":{
         "allOf":[
            {
               "field":"type",
               "equals":"Microsoft.Sql/servers"
            }
         ]
      },
      "then":{
         "effect":"deployIfNotExists",
         "details":{
            "type":"Microsoft.Sql/servers/azureADOnlyAuthentications",
            "roleDefinitionIds":[
               "/providers/Microsoft.Authorization/roleDefinitions/056cd41c-7e88-42e1-933e-88ba6a50c9c3",
               "/providers/Microsoft.Authorization/roleDefinitions/8e3af657-a8ff-443c-a75c-2fe8c4bcb635"
            ],
            "existenceCondition":{
               "allOf":[
                  {
                     "field":"Microsoft.Sql/servers/azureADOnlyAuthentications/azureADOnlyAuthentication",
                     "equals":true
                  }
               ]
            },
            "deployment":{
               "properties":{
                  "mode":"incremental",
                  "name":"Default",
                  "template":{
                     "$schema":"https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                     "contentVersion":"1.0.0.0",
                     "parameters":{
                        "fullServerName":{
                           "type":"string"
                        }
                     },
                     "resources":[
                        {
                           "name":"[concat(parameters('fullServerName'), '/Default')]",
                           "apiVersion":"2021-02-01-preview",
                           "type":"Microsoft.Sql/servers/azureADOnlyAuthentications",
                           "properties":{
                              "azureADOnlyAuthentication":true
                           }
                        }
                     ]
                  },
                  "parameters":{
                     "fullServerName":{
                        "value":"[field('name')]"
                     }
                  }
               }
            }
         }
      }
   }
}

There are some interesting points to analyse about this policy.

The Policy Effect: DeployIfNotExist

It’s using the effect called deployIfNotExist, it will generate a deployment in case the condition is not met

There is an existence criteria in the effect and it needs to be met. In this policy most of the checks are in the effect, not in the main If of the policy. The main If is only checking for the type of the object. The additional conditionals are under existenceCondition, a property of this effect.

The ExistenceCondition

The condition checks if Azure SQL Server accepts Azure AD Authentication only and if not, the effect executes a deployment.

Deployment

The deployment property,  another property of the deployIfNotExist effect, contains an ARM template. The deployment is incremental, this means the policy effect will only deploy the features or properties in the template, nothing else.

This policy is possible because the property to allow only Azure AD Authentication is in an isolated object, making it easy for a policy to deploy this object.

The object needs to be related to the server, that’s why the deployment has a parameter called fullServerName and we use this parameter as the name of the deployed object.

The parameter is filled with the expression [field(‘name’)] . This expression retrieves the name of the object the policy is evaluating, in this case, the Azure SQL Server.

Role Definitions

The policy will execute the deployment of the template. Any change in Azure objects, in our scenario, Azure SQL Servers, will trigger the policy check.

It’s not so simple. This triggers many questions.

• Which identity will be used to make the deployment?
• What permissions this identity has?
• Will it be the same identity used everywhere?

We create and save the policy in an Azure hierarchy and we can assign it at any level below this hierarchy. Only the policy assignment can trigger the policy execution, if we have no assignment, we have no execution.

During the assignment, we create a link between the policy and one object, which can be a Resource Group, a Subscription or a Management Group. This assignment can, in some cases, also creates a managed identity. The created managed identity will need permission to execute the deployment required by the policy.

The permissions and the management identity creation are related: The management identity will be created when the permissions needed by the managed identity are included in the policy.

 

The roleDefinitionIds property has the permissions for the managed identity on the level of the object assigned to the policy (Resource Group, Subscription or Management Group).

Remediation Task

You can force the policy check at any moment using powershell. However, the fix of the problem will only be executed in two situations: Either when the object is created or if we create a Remediation Task.

Using the policy assignment we can create and execute a remediation task to fix the existing problems, executing the remediation already defined in the policy.

 

 

 

 

Summary

The possibility to enable only Azure AD Authentication in Azure SQL Databases is already great. Adding to this  the possibility to enforce this rule in many different levels using the policies is even better.

This policy is not perfect. It will fail if the Azure SQL Server doesn’t have an administrator configured. But this blog gives you the overall idea about what’s possible to achieve.

The post Azure SQL: Enforcing SQL Authentication type appeared first on Simple Talk.

from Simple Talk https://ift.tt/3IsJfYD
via