This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

Hi everyone,

time passed since my last post. Hope you missed me 

 

Today I am going to cover an interesting aspect on how to capture security audit events from both Azure and non-Azure SQL Server machines. Most of you probably know that SQL Server is capable of auditing security related information, such as access to a given database, record creation or deletion, configuration change and so on) according to the Audit configuration applied to a given instance or database.

 

In this post, we will not dig into SQL Server Audit configuration or capability. We will rather explore the steps and configurations necessary to collect data using Azure Monitor. For those of you who are eager to understand more about the SQL Server Audit, read the following links:

 

• SQL Server Audit (Database Engine)
• Create a Server Audit and Server Audit Specification
• Create a server audit and database audit specification
• SQL Server Audit Action Groups and Actions

 

All the above is a good set of resource for understanding the enablement and the configuration of SQL Server Audit, but unfortunately is not enough to capture the data in Azure Monitor. Moreover, it could result in something complicated the first time. Here why I am going to guide you toward the right direction using a 3 phases approach:

 

1. Verify/Grant the generate security audits permission
2. Verify/Configure the audit object access setting
3. Create and configure Server Audit and Audit Specification on the SQL Server

 

First, you need to decide where to store audit data. You can choose between Security, Application or any other Windows Event Log. This choice will change the scene later on, since data collection configuration differs based on the event log you went for. For instance, if you have configured the audit to write on the Security event log (which is the most proper since we’re dealing with security related data) you will need either Microsoft Defender for Cloud or Microsoft Sentinel to collect and store this information.

Differently if you went for Application or System event logs, then a Log Analytics workspace configuration will be needed to set up the proper Windows Event data source for capturing data.

 

This blog post will show will assume that the SQL Server Audit is configured using the Security event log as destination and that either Microsoft Defender for Cloud or Microsoft Sentinel are already in place and configured properly. Should you need help on how to configure them you can get all the information at Security events via Legacy Agent (Windows)

 

Let’s put our fingers on the keyboard then 

 

Verify/Grant the generate security audits permission

At first, we need to verify if the generate security audits permission has been already granted to the service account configured to run the SQL Server service. If this is not the case, we will configure it. More information can be found on the Write SQL Server Audit Events to the Security Log document.

 

 

NOTE: The configuration of the Generate security audits policy for the SQL Server service account could be also done using Domain Policies. Bear in mind that local settings can be overwritten by a domain policy.

 

 

1. Logon to the SQL Server computer you want to configure

2. Right-click on the Start menu and select Run

 

 

3. Type secpol.msc and click OK

 

 

4. In the Local Security Policy tool, expand Security Settings, expand Local Policies, and then click User Rights Assignment

5. In the results pane, double-click Generate security audits

 

 

6. On the Local Security Setting tab, make sure the SQL Server service account is listed. If not, click Add User or Group to add it

 

 

7. Click OK

 

Verify/Configure the audit object access setting

Our second macro-activity consists in the verification and configuration (where necessary) of the audit object access policy settings.

 

 

NOTE: To reduce the amount of information logged into the Security event log and hence to lower the cost of Azure Monitor, we will configure the audit object access policy subcategories. The configuration of the audit object access policy could be also done using Domain Policies. Bear in mind that local settings can be overwritten by a domain policy.

 

 

1. In the Local Security Policy tool, expand Security Settings, expand Local Policies, and then click Security Options

2. In the results pane, verify that Audit: Force audit policy subcategory setting (Windows Vista or later) to override audit policy category settings is set to Enabled

 

 

If set to Disabled or Not Configured, double-click on it and configure it properly

 

 

3. Click OK

4. In the Local Security Policy tool, expand Security Settings, expand Advanced Audit Policy configuration, expand System Audit Policies and then click Object Access

5. In the results pane, check if Audit Application Generated is set to audit events of type Success and Failure

 

 

If set to Not Configured, double-click on it and configure it properly

 

 

6. Click OK

 

Create and configure Server Audit and Audit Specification on the SQL Server

Finally, we got all the operating system requirements in place, hence it’s now time to configure the SQL Server side. Let’s create then the Server Audit and the Server Audit Specification required to enable the auditing.

 

1. Open an elevated PowerShell (Run as Administrator) and execute the following command (see the comment later in the SQL Script

 

 

[guid]::NewGuid()

 

 

 

2. Take note of the above Guid

3. Open SQL Management Studio and login to the given SQL Server or Instance

4. To create the Server Audit, copy the following SQL query statement into a New Query making sure to have replaced the sample guid with the one generated above:

 

 

USE [master] GO /****** Object: Audit [GDPR_Audit] Script Date: 11/5/2019 8:45:57 PM ******/ CREATE SERVER AUDIT [GDPR_Audit] TO SECURITY_LOG WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' -- Replace with a new Guid using this PowerShell command [guid]::NewGuid() ) ALTER SERVER AUDIT [GDPR_Audit] WITH (STATE = ON) GO

 

 

 

 

5. Right after, we will create the Server Audit Specification, having the bare minimal audit set, using the SQL Query statement below:

 

 

NOTE: The difference between Server Audit Specification and Database Audit Specification is just the scope to which the audit configuration will be applied. Database Audit specification allows for more granularity whilst Server Audit specification allows for less administration.

 

 

 

 

USE [master] GO /****** Object: Audit Specification [GDPR_Audit_Specification] Script Date: 12/7/2021 10:10:57 AM ******/ CREATE SERVER AUDIT SPECIFICATION [GDPR_Audit_Specification] FOR SERVER AUDIT [GDPR_Audit] ADD (AUDIT_CHANGE_GROUP), -- This event is raised whenever any audit is created, modified or deleted. This event is raised whenever any audit specification is created, modified, or deleted. Any change to an audit is audited in that audit. ADD (FAILED_LOGIN_GROUP), -- a successful login to the instance is added ADD (LOGOUT_GROUP), -- Indicates that a principal has logged out of SQL Server. ADD (SERVER_PRINCIPAL_CHANGE_GROUP), --* server principals are created, altered, or dropped. -- a principal issues the sp_defaultdb or sp_defaultlanguage stored procedures or ALTER LOGIN statements -- sp_addlogin and sp_droplogin stored procedures. -- sp_grantlogin or sp_revokelogin stored procedures ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP), -- a login is added or removed from a fixed server role. This event is raised for the sp_addsrvrolemember and sp_dropsrvrolemember stored procedures. ADD (SUCCESSFUL_LOGIN_GROUP), -- a successful login to the instance is added ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP), -- Indicates that a principal successfully logged in to a contained database. ADD (FAILED_DATABASE_AUTHENTICATION_GROUP) -- Indicates that a principal tried to log on to a contained database and failed. WITH (STATE = ON) GO

 

 

 

6. From the left-side menu of SQL Server Management Studio, expand Security, expand Audits and expand Server Audit Specifications and verify that the object you just created are correctly listed and in the Enabled state

 

 

7. Once the above configuration is completed SQL will start logging the performed actions (logon, logoff, new login created, dropped logins, etc.) using the event id 33205 in the Security event log that, on its hand, will be collected and stored into Log Analytics thanks to Microsoft Defender for Cloud (formerly Azure Security Center) or Microsoft Sentinel. You can verify the data is flowing in by using this sample Log Analytics query:

 

 

 

SecurityEvent | where EventID == 33205

 

 

 

 

Now you’re all set. Just remember to verify with bot SQL and Security experts if the sample audit set proposed here is responding to your needs or must be changed. If so, just configure the Audit specification (server or database depending on your earlier choice) accordingly.

 

Wouldn’t it be great if you could create you visualization to enhance the observability? Yes? Formidable, so as other step you could import the corresponding workbook you can find as attachment in this post (remember to remove the .csv extension before use) 

 

Have a great fun auditing you Azure and non-Azure SQL Server servers 

 

Disclaimer
The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.