{"id":92938,"date":"2021-12-06T17:00:10","date_gmt":"2021-12-06T17:00:10","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=92938"},"modified":"2021-12-07T23:13:15","modified_gmt":"2021-12-07T23:13:15","slug":"azure-sql-enforcing-sql-authentication-type","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/azure-sql-enforcing-sql-authentication-type\/","title":{"rendered":"Azure SQL: Enforcing Azure AD Only Authentication"},"content":{"rendered":"

Some time ago I wrote a blog<\/a> about the just released feature to allow only Azure Authentication<\/strong> in Azure SQL Databases.<\/strong><\/p>\n

 <\/p>\n

\"\"<\/p>\n

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

From that time to today, a lot of things happened. The feature was in preview. The structure of the ARM<\/strong> 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.<\/p>\n

The policy from the previous blog post can only to check if the Azure SQL<\/strong> is using Azure Authentication only or if it is accepting Azure SQL Authentication<\/strong> as well.<\/p>\n

This time, let’s analyse a policy not only to check, but to change this configuration.<\/p>\n

The policy is this one:<\/p>\n

<\/p>\n

\n
{\r\n   \"parameters\"<\/span>:{\r\n      \r\n   },\r\n   \"policyRule\"<\/span>:{\r\n      \"if\"<\/span>:{\r\n         \"allOf\"<\/span>:[\r\n            {\r\n               \"field\"<\/span>:\"type\"<\/span>,\r\n               \"equals\"<\/span>:\"Microsoft.Sql\/servers\"<\/span>\r\n            }\r\n         ]\r\n      },\r\n      \"then\"<\/span>:{\r\n         \"effect\"<\/span>:\"deployIfNotExists\"<\/span>,\r\n         \"details\"<\/span>:{\r\n            \"type\"<\/span>:\"Microsoft.Sql\/servers\/azureADOnlyAuthentications\"<\/span>,\r\n            \"roleDefinitionIds\"<\/span>:[\r\n               \"\/providers\/Microsoft.Authorization\/roleDefinitions\/056cd41c-7e88-42e1-933e-88ba6a50c9c3\"<\/span>,\r\n               \"\/providers\/Microsoft.Authorization\/roleDefinitions\/8e3af657-a8ff-443c-a75c-2fe8c4bcb635\"<\/span>\r\n            ],\r\n            \"existenceCondition\"<\/span>:{\r\n               \"allOf\"<\/span>:[\r\n                  {\r\n                     \"field\"<\/span>:\"Microsoft.Sql\/servers\/azureADOnlyAuthentications\/azureADOnlyAuthentication\"<\/span>,\r\n                     \"equals\"<\/span>:true<\/span>\r\n                  }\r\n               ]\r\n            },\r\n            \"deployment\"<\/span>:{\r\n               \"properties\"<\/span>:{\r\n                  \"mode\"<\/span>:\"incremental\"<\/span>,\r\n                  \"name\"<\/span>:\"Default\"<\/span>,\r\n                  \"template\"<\/span>:{\r\n                     \"$schema\"<\/span>:\"https:\/\/schema.management.azure.com\/schemas\/2015-01-01\/deploymentTemplate.json#\"<\/span>,\r\n                     \"contentVersion\"<\/span>:\"1.0.0.0\"<\/span>,\r\n                     \"parameters\"<\/span>:{\r\n                        \"fullServerName\"<\/span>:{\r\n                           \"type\"<\/span>:\"string\"<\/span>\r\n                        }\r\n                     },\r\n                     \"resources\"<\/span>:[\r\n                        {\r\n                           \"name\"<\/span>:\"[concat(parameters('fullServerName'), '\/Default')]\"<\/span>,\r\n                           \"apiVersion\"<\/span>:\"2021-02-01-preview\"<\/span>,\r\n                           \"type\"<\/span>:\"Microsoft.Sql\/servers\/azureADOnlyAuthentications\"<\/span>,\r\n                           \"properties\"<\/span>:{\r\n                              \"azureADOnlyAuthentication\"<\/span>:true<\/span>\r\n                           }\r\n                        }\r\n                     ]\r\n                  },\r\n                  \"parameters\"<\/span>:{\r\n                     \"fullServerName\"<\/span>:{\r\n                        \"value\"<\/span>:\"[field('name')]\"<\/span>\r\n                     }\r\n                  }\r\n               }\r\n            }\r\n         }\r\n      }\r\n   }\r\n}\r\n<\/pre>\n<\/div>\n
There are some interesting points to analyse about this policy.<\/p>\n
The Policy Effect: DeployIfNotExist<\/h2>\n
It’s using the effect called deployIfNotExist<\/strong>, it will generate a deployment in case the condition is not met<\/p>\n
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<\/strong>, a property of this effect.<\/p>\n
\nThe ExistenceCondition<\/h2>\n
The condition checks if\u00a0Azure SQL Server<\/strong>\u00a0accepts\u00a0Azure AD Authentication<\/strong>\u00a0only and if not, the effect executes a deployment.<\/p>\n
Deployment<\/h2>\n
The deployment<\/strong> property,\u00a0 another property of the deployIfNotExist<\/strong> 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.<\/p>\n
This policy is possible because the property to allow only Azure AD Authentication<\/strong> is in an isolated object, making it easy for a policy to deploy this object.<\/p>\n
The object needs to be related to the server, that’s why the deployment has a parameter called fullServerName<\/strong> and we use this parameter as the name of the deployed object.<\/p>\n
The parameter is filled with the expression [field(‘name’)]<\/em> . This expression retrieves the name of the object the policy is evaluating, in this case, the Azure SQL Server<\/strong>.<\/p>\n
\nRole Definitions<\/h2>\n
The policy will execute the deployment of the template. Any change in Azure objects,\u00a0in our scenario, Azure SQL Servers, will trigger the policy check.<\/p>\n
It’s not so simple. This triggers many questions.<\/p>\n
    \n
  • Which identity will be used to make the deployment?<\/li>\n
  • What permissions this identity has?<\/li>\n
  • Will it be the same identity used everywhere?<\/li>\n<\/ul>\n
    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.<\/p>\n
    During the assignment, we create a link between the policy and one object, which can be a Resource Group<\/strong>, a Subscription<\/strong> or a Management Group<\/strong>. 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.<\/p>\n
    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.<\/p>\n
     <\/p>\n
    \"\"<\/p>\n
    The roleDefinitionIds<\/strong> property has the permissions for the managed identity on the level of the object assigned to the policy (Resource Group<\/strong>, Subscription<\/strong> or Management Group<\/strong>).<\/p>\n
    Remediation Task<\/h2>\n
    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<\/strong>.<\/p>\n
    \"\"<\/p>\n
    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.<\/p>\n
     <\/p>\n
    \"\"<\/p>\n
     <\/p>\n
    \"\"<\/p>\n
     <\/p>\n
    \"\"<\/p>\n
     <\/p>\n
    \"\"<\/p>\n
    Summary<\/h2>\n
    The possibility to enable only Azure AD Authentication<\/strong> in Azure SQL Databases<\/strong> is already great. Adding to this\u00a0 the possibility to enforce this rule in many different levels using the policies is even better.<\/p>\n
    This policy is not perfect. It will fail if the Azure SQL Server<\/strong> doesn’t have an administrator configured. But this blog gives you the overall idea about what’s possible to achieve.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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,……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[5364,145787,145786,136322],"coauthors":[6810],"class_list":["post-92938","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-azure","tag-azure-ad-authentication","tag-azure-policy","tag-azure-sql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92938","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=92938"}],"version-history":[{"count":12,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92938\/revisions"}],"predecessor-version":[{"id":93010,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/92938\/revisions\/93010"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=92938"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=92938"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=92938"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=92938"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}