When I first started speaking about cloud technologies over a decade ago, the most common question I heard was around just how secure the platform was. Security was top of mind for people when all of their data resided in data centers that they owned and maintained up to that point. Almost everyone that had access to their data worked directly for the company. They controlled where the data lived, who had access to it, and even who was running the networking cables the data traveled across. As cloud technologies advanced and became more mainstream, fewer questions came up about security. That doesn’t mean that people aren’t still concerned about security; it just means that there are many more answers to the questions and a good deal of options on how to secure your data. This article explains how Azure Policies and Management Groups can simplify Azure SQL security.

Features like Always Encrypted, Transparent Data Encryption (TDE) with optional customer-managed keys, private endpoints, and auditing are just a few of the many options available to secure data stored in Azure SQL Database and Azure SQL Managed Instance. Having the options is one thing, but ensuring they are used is another, especially when many databases are scattered across multiple subscriptions. Each organization must decide which options they are going to implement and then not only put them in place but also ensure that they aren’t circumvented or modified later. This is where tools in Azure such as Azure Policy and Management Groups come in handy.

What is Azure Policy?

At its core, Azure Policies are defined rules that you create to enforce certain settings or configurations in your Azure deployed resources. You create a policy and then apply that policy to some scope, such as a subscription or resource group. The Azure Platform will enforce that policy against all resources within the assigned scope. Enforcement could mean that the Azure platform flags a resource that is not meeting the policy as non-compliant so that you can remediate the issue, or it could mean that the platform simply refuses to deploy a resource that doesn’t meet the policy. You have a lot of control over how the platform enforces your selected policies.

For Azure SQL Database and Azure SQL Managed Instance, there are a ton of scenarios where having policies to enforce your security approach will be extremely helpful. Some ideas are:

• Restricting the physical locations that databases are stored in order to meet data sovereignty requirements or simply to limit where your databases are located. You can restrict your databases to only be created in specific Azure regions, for example.
• Ensure that Private Endpoint connections are configured so that you don’t have a database accessible directly through the public endpoint.
• Enforcing customer-managed keys for encrypting your data at rest if you choose to use that option.
• Enforcing auditing to be turned on.
• Enforcing that an Azure Active Directory administrator is assigned at the server level.

Essentially, almost anything that you can define as part of your deployment can be placed into a policy for the platform to enforce, or at least report when you have non-compliant databases.

Here is an example of a policy that ensures that an Azure Active Directory administrator is provisioned for Azure SQL Database servers. This particular policy is one that Microsoft provides as a built-in policy to choose from, which is why you see the policyType set to BuiltIn.

A policy is made up of some metadata, parameters, and then the actual rule. In this example, the rule is checking to see if the settings for Microsoft.Sql/servers/administrators are set on any resource that is of type Microsoft.Sql/servers. The policy provides a parameter named effect. When you apply the policy to a scope, you can supply a value for the parameter to indicate how the system should respond if the administrator settings are provided or not. The options for the parameter in this policy are constrained by allowedValues and are defined as either Disabled (which means no action is taken) or AuditIfNotExists (which means the resource will be flagged as non-compliant). By providing you the ability to set the parameter in this way, you can toggle the action of this policy to be enforced or not without having to remove the policy entirely from a scope if you need to turn it off for a short time.

Here is a screenshot from the Azure Portal showing a database that is non-compliant with the defined policy above. Note that the deployed system has two Azure SQL Servers, but only one of them has Azure Active Directory Administrators assigned, which is why you see a 50% compliance. The screen is currently filtered to only show you the non-compliant resources.

Image: Azure Portal showing non-compliant resources for defined policies

To learn more about Azure Policy, including how to create your own, check out the documentation provided by Microsoft. The documentation details how to create policies, deploy them, and monitor the compliance status.

You can end up with many different policies defined to create the rules you want to be enforced in your organization. To help organize these policies, you can create a collection of policies into what is known as an Initiative (also called a policySet). By grouping policies into an initiative, you can simplify the assignment and administration of your chosen rules. Like with a single policy, you can assign an initiative to a scope, and then all rules assigned to that initiative will be enforced across that scope.

What are Management Groups?

A management group is a mechanism for organizing your Azure subscriptions. Each management group can contain zero or more Azure Subscriptions, and the management groups can be hierarchical so that you can nest management groups within other management groups. Management Groups allow you to create a collection of subscriptions that can then be managed in a like manner without having to go to each subscription. For example, you can assign role-based access control permissions at a management group level, and all subscriptions beneath that group will inherit those permissions.

By default, there is an implicit root management group on all Azure Active Directory tenants. You can then add new management groups, including nesting them, under the root in order to organize your subscriptions in a manner that makes sense for your company. This capability lets you group subscriptions by deployment environment (like dev, test, or production), region, department, or something completely bespoke to meet your needs. A subscription can only belong to one management group at a time. However, remember that you can nest the management groups.

Management Groups can be a selected scope for Azure Policies and Initiatives which means you can assign a policy in one place, such as the root management group, and ensure that it is implemented across all your subscriptions. You can also have policies assigned at specific levels in your management group hierarchy so that the rules are only applied to subscriptions within that management group and down the hierarchy from there.

There is a lot of power in using management groups. If you are not familiar with them, you can read more in the Microsoft documentation. You can find tutorials and more in the docs, as well as some guidance on organizing your management groups effectively in the Cloud Adoption Framework articles.

Putting Azure Policy into action

Much like any project you work on, the first step in getting a handle on your security and governance is to gather requirements. If your organization does not currently have any rules around securing your data, then now is the time to sit down and get them defined. If you already have rules but are doing manual enforcement, now is the time to look at what you can automate using Azure Policy and Management Groups. Since you can have a policy around almost any aspect of a deployment, the possibilities are nearly endless, and it may take some time to determine what all makes sense for you to put into place.

In addition to defining the policies and initiatives you will enforce, you will also need to decide how they will be applied to your subscriptions. If you weren’t already using Management Groups, you’d want to spend some time thinking through the organization of your subscriptions. This task can potentially be trickier than it sounds and not always straightforward. You may find that some subscriptions are easily grouped while others are outliers that need special consideration.

As you are gathering your plans, here are some suggestions that you may wish to take into consideration:

• Different environments, such as development, test, preview, or production, may have other policy requirements, so use subscriptions to help mitigate this. You could also use tags if you need to mix environments within a subscription; however, if you do this, you need to enforce the use of tags of specific values. It is also suggested to enforce the use of a default tag to have the most stringent policy for fail-safe so that any resources deployed without specific tags defined end up being locked down the hardest until someone makes a conscious decision on what should be applied to it.
• Make sure to source control your policies and automate them as much as possible. Look at tools such as scripts using the PowerShell Az module, Azure CLI, terraform, or ARM.
• Document your policies to detail why each one is required and at what scopes. Documentation is beneficial if you are a Software-as-a-Service (SaaS) provider to answer security questions from your customers who will want to know how their data is being handled.
• While Azure Policy is powerful, not all things can be completely enforced via policy. For example, Azure SQL Firewall rules or SQL logins are defined within the databases themselves and not as metadata about the resources. Therefore, they can’t be monitored as part of a policy. For these scenarios, you may find that a script that does routine checks on these types of items will need to be put in place.
• Define a procedure to perform periodic reviews on all non-compliant databases and remediate as necessary as part of your regular operations. Set aside time to do this routine so that it doesn’t get put off.
• Management Groups can define a specific group as the default management group for new subscriptions. By default, it is the Root Management Group; however, that may not be the best place for new subscriptions depending on what policies you want in place at that level. Check out the documentation on how to set your default management group. When choosing where a new subscription will be placed, think about failing safe as it applies to what security you want on your databases automatically without additional effort.
• Don’t wait until you have all possible policies you want to enforce defined before getting them applied. Once you have one or two policies that you know you’ll want to enforce, go ahead and get them implemented. This will allow you to work through your process of managing your policies and initiatives while the number of policies is small.
• Microsoft has created a lot of Azure Policy definitions for specific compliance standards policies, which can easily be pulled into your own initiatives. You can find a list of specific compliance standards (such as FedRAMP, HIPAA, NIST, and more ) in their documentation, along with links to Azure Policies defined on GitHub to use. They also have an Azure Security Benchmark built-in initiative you can use as a baseline if you wish. These built-in initiatives would be a great place to start, especially if there is a specific compliance standard you need to follow and there is already a policy defined.
  
  Image: Azure Portal showing the configuration for a built-in policy for ensuring Azure SQL Servers have disabled public access

Azure Policy and Management Groups

Using Azure Policies and Management Groups can help you get a good handle on the security of your data. You may still need to provide additional measures, but these tools will help you automate the enforcement of rules you define and provide a baseline level of security and compliance for all of your Azure SQL Databases and Azure SQL Managed Instances.

If you liked this article, you might also like Performance of querying blob storage with SQL – Simple Talk (red-gate.com).