When migrating on-premises systems to Microsoft Azure, security and confidentiality concerns often arise. However, with the right techniques, you can control and monitor the status and access to your Azure-resident services, alleviating most of these concerns. One such technique is SQL Database auditing.

SQL Database auditing is a relatively new feature that allows you to track database-level security-related events. By enabling auditing, you can identify unauthorized changes and access attempts, ensuring regulatory compliance. Auditing can be selectively turned on or off based on event types such as Data Access, Schema Changes, Data Changes, Security Failures, and Grant/Revoke Permissions.

Once enabled, these events are automatically recorded and stored in a designated auditing table within an Azure storage account. It’s important to note that there is an added monetary cost associated with using auditing, as well as a slight latency impact. However, there are no negative implications on database performance.

When designating the storage account, you have the option to specify whether to use the primary or secondary key for secure access to its content. This allows for periodic changes of secrets protecting stored content without interrupting the auditing of security-related events.

In addition to database-level auditing, you can also specify event types to be logged on the SQL Server level. This allows for consistent settings to be applied to all SQL Databases hosted on the same SQL Server.

In order for auditing to take effect, you must modify the connection strings used by client applications and users to interact with the SQL Database. The modified connection string includes the word “secure” to indicate that auditing is enabled.

While auditing provides a way to track security-related events, it’s important to ensure full compliance by disabling the original connection strings. This can be done by toggling the Security Enabled Access switch to “Required” in the Azure portal.

Once auditing is enabled and enforced, the events will accumulate in the designated storage account. You can view the raw content using utilities that provide direct access to Azure table storage. Additionally, you can take advantage of preconfigured dashboard templates to simplify analysis of the audit log data.

SQL Database auditing is a powerful tool for controlling and monitoring your Azure-resident services. By implementing auditing, you can track security-related events, identify unauthorized access attempts, and ensure regulatory compliance.

Stay tuned for our upcoming articles where we will explore other enhancements to SQL Database auditing.