This article explores the Azure built-in policies and implements them to check and fix Azure SQL Database and Azure SQL Server compliance.
An Overview of the Azure Policy
Each organization has its own set of database standard configurations depending upon the infrastructure, application requirements. These standards might include governance for security, management, regulatory compliance, resource consistency. It is essential to do a regular audit of all databases to ensure your databases met the standards. It is not feasible to manually connect to each instance\database, perform defined checks and mark them in the compliant or non-compliant category.
Azure is a widespread cloud infrastructure in Microsoft-managed data centers to create, deploy, manage applications and infrastructure. In Azure, you can use Azure policy for enforcing standards and visualize the compliance dashboard. This dashboard enables you to drill down on per-resource and per-policy levels. You can also use it to remediate the existing resources for ensuring compliance. The policy contains business rules in the JSON format, which are known as JSON definitions. The business rule can use the logical operators, functions, conditions, parameters. You can apply these policy definitions to scopes such as management, resource, subscription, or individual resources.
The policy evaluation happens at the following times under the policy assignment scope.
- Once a resource is created, deleted, modified.
- Standard policy compliance evaluation cycle ( every 24 hrs.)
- A policy is newly assigned, or its scope is updated
Azure Policy definition structure
The Azure policy contains compliance conditions along with the effect to take if the condition is satisfied. These conditions might include operators such as equals, notEquals, like, notlike, match, contains, less, greater, lessorEquals, exists.
The Policy definition includes the following fields.
- Display Name
- Description
- Mode
- Metadata
- Parameters
- Rule definition
- Effect
Built-in-Policy definitions in Azure portal
Azure provides several in-built policy definitions for evaluation resources. In the Azure portal, look for the Policy services.
If you already configured the Azure policy, you get its dashboard with a compliance report.
In the left-hand menu bar, click on Definitions. You get the list of definitions, the number of policies, types, and categories. The policies are grouped according to their categories. The following figure shows the built-in policies for all Azure resources.
Suppose we want to check existing policies for Azure SQL Databases. For this purpose, use the search bar. For example, here, I filter the results and show only policies for the SQL category.
You can click on a policy to view its definition in JSON format. For example, the following figure shows the Azure policy definition for the policy named – Long-term geo-redundant backup should be enabled for Azure SQL Databases.
Implement built-in Azure policy – “Auditing on SQL server should be enabled”
Suppose we want to use a built-in policy – Auditing on SQL server should be enabled for compliance reports on existing SQL databases. Click on the policy name, and you get its metadata and definition in JSON format, as shown below.
The policy definition specifies the Azure policy purpose – “To ensure the operations performed against your SQL assets are captured, SQL servers should have auditing enabled. This is sometimes required for compliance with regulatory standards.”
In the Assignments, you can configure the subscription and scope for the resource group.
To implement this policy, click on Assign. It opens a wizard for configuring the Azure policy.
Basics
In the basics, we can define the policy scope, exclusions, description (optional), policy enforcement status (enabled\disabled) and policy assignment owner.
Policy enforcement should be enabled for implementing and validating it for Azure resources. In the assigned by, you can specify the owner or assignee of the policy.
Parameters
In the parameter section, it has effect, retention days and resource group name. By default, the audit data retention period is 180 days.
As shown, it has the DeployIfNotExists effect for the said policy definition. This effect runs approx.. 15 minutes later once the resource is created, updated. The resources are marked as non-compliant if the policy definition evaluation is successful for resources.
Select the Azure resource group name for the storage account and modify the retention period, if required.
Remediation
We can use remediation tasks for making the change in Azure resource to be compliant. For example, if our Azure server does not have audit enabled, it is marked as Non-Compliant during resource evaluation. The remediation task can make it compliant by configuring the audit automatically.
Non-compliant messages
In this section, you can write a customized message to help users understand a non-compliant resource.
Review+ Create
Review your Azure policy configuration and make a change to any issue.
Click on create, and it configures policy assignment, role assignment, remediation task.
Explore Azure policy dashboard
Once you deploy and assign the Azure policy, you can view its status in the policy dashboard. As shown below, for my Azure resource, the current status is Not Started. It takes few minutes (approx. 15-20 minutes) before policy execution starts. The dashboard shows overall resource compliance (percentage), Non-compliant initiatives, Number of Non-compliant policies, and chart for resources by compliance state.
For my lab environment Azure SQL Server, server-level auditing is disabled. Therefore, the azure policy compliance result should be Non-compliant.
Click on the policy name to get more details of the failures. In the compliance details, you get the non-compliance message that we wrote during the configuration.
Remediation Task for Azure policy compliance
In the earlier configuration, we configured a remediation task to ensure none of our Azure Server remains non-compliant against the evaluated Azure policy.
In the remediation task, its status is Complete. It remediated one resource against the policy definition.
Click on the start time to get the remediation task details. Its status is succeeded for the resource type Microsoft.sql/servers.
Click on the deployment ID to check the resource deployment status.
Click on Go to the resource group and view the audit configuration of Azure SQL Auditing at the instance(server) level. As shown below, it is enabled. The logs storage is on the storage account. Azure automatically creates the storage account for storing audit logs.
You can also check audit properties at the Azure SQL Database level. As audit is enabled at the server level, it is replicated to all underlying Azure databases.
In the Azure portal, search for the storage account. We can look at the tags of the storage account to check who created it. As shown below, the tag shows – Created By: Azure Policy – Configure SQL Servers to have auditing enabled.
Click on Storage Explorer (preview) from the storage account menu. Here, you can see blob container – sqldbauditlogs. In that storage account, you have a folder for the Azure SQL Server.
Open the folder, and it has subfolders for Master and individual Azure SQL Database.
Inside the DB folder, it has a server audit folder named – SqlDbAuditing_ServerAudit.
Azure collects and stores audit data into date wise folder. For example, it shows a folder for my audit- created date.
In the folder, it has extended events files in the XEL extension.
Now, you can go back to the Azure policy dashboard and view the policy compliance. Its status changed to Compliant.
Currently, I have one Azure SQL Database in my lab environment. Therefore, it shows 100 % compliance from the single Azure server.
Conclusion
This article explored built-in Azure policy definitions to check compliance for Azure SQL Database against the set of business rules or configuration. You can configure remediation tasks to automatically modify Azure configuration and ensure you are fully compliant with database infrastructure. We will configure a custom JSON Azure policy definition in the following article and verify the compliance report for Azure SQL Database.
You can refer to Microsoft docs for Azure Policy documentations.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023