Rajendra Gupta
Compliance dashboard

Identify and remediate non-compliant Azure SQL Databases for auditing on SQL Server

September 16, 2021 by

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.

Azure Policy - Azure SQL Database

If you already configured the Azure policy, you get its dashboard with a compliance report. Compliance Report Dashboard

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.

Policy Definitions

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.

Sort by 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.

Long term geo redundant backup policy

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.

JSON Policy definition - Azure SQL Database

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.

Configure SQL Server audit

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 scope and exclusions

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.

Policy Enforcement

Parameters

In the parameter section, it has effect, retention days and resource group name. By default, the audit data retention period is 180 days.

Policy parameters

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.

Parameter configuration- Azure SQL Database

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.

Remediation step

Policy permissions

Non-compliant messages

In this section, you can write a customized message to help users understand a non-compliant resource.

Non-compliant messages

Review+ Create

Review your Azure policy configuration and make a change to any issue.

Review policy- Azure SQL Database

Click on create, and it configures policy assignment, role assignment, remediation task.

Policy remediation task status

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.

Azure Policy Dashboard

For my lab environment Azure SQL Server, server-level auditing is disabled. Therefore, the azure policy compliance result should be Non-compliant.

Policy compliance state

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.

Get policy compliance details

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.

Policy compliance

In the remediation task, its status is Complete. It remediated one resource against the policy definition.

Remediation task scope

Click on the start time to get the remediation task details. Its status is succeeded for the resource type Microsoft.sql/servers.

Click on start time

Click on the deployment ID to check the resource deployment status.

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.

Enable audit

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.

Enable server level azure audit

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.

Tags

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.

Storage explorer

Open the folder, and it has subfolders for Master and individual Azure SQL Database.

Navigate through storage explorer

Inside the DB folder, it has a server audit folder named – SqlDbAuditing_ServerAudit.

Database audit folder

Azure collects and stores audit data into date wise folder. For example, it shows a folder for my audit- created date.

View existing data In the folder, it has extended events files in the XEL extension.

Existing XEL files

Now, you can go back to the Azure policy dashboard and view the policy compliance. Its status changed to Compliant.

Policy status change

Currently, I have one Azure SQL Database in my lab environment. Therefore, it shows 100 % compliance from the single Azure server.

Compliance dashboard

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.

Azure Policy documentations

Rajendra Gupta
Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

274 Views