Azure Monitor: Audit Your Azure and Non-Azure SQL Server

Published Jan 10 2022 12:00 AM 5,759 Views
Microsoft

20220207 - Update: Added necessary step to restart the SQL Instance to avoid error SQL SERVER – Error 33222, Level 16 – Audit ‘MyAudit’ Failed to Start during the Server Audit activation.

 

Hi everyone,

 

Time passed since my last post. Hope you missed me :smile:

 

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:

 

 

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 :cool:

 

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

 

BrunoGabrielli_0-1640101021244.png

 

3. Type secpol.msc and click OK

 

BrunoGabrielli_1-1640101079081.png

 

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

 

BrunoGabrielli_2-1640101133988.png

 

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

 

BrunoGabrielli_3-1640101170950.png

 

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

 

BrunoGabrielli_4-1640101321261.png

 

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

 

BrunoGabrielli_5-1640101321267.png

 

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

 

BrunoGabrielli_6-1640101321276.png

 

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

 

BrunoGabrielli_7-1640101321279.png

 

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.

 

 

NOTE: This part requires a restart of the SQL instance to apply the new permission in line with the policy settings above.

 

 

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

 

 

 

 

 

[guid]::NewGuid()

 

 

 

 

 

BrunoGabrielli_8-1640101523745.png

 

2. Take note of the above Guid

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

4. Right click on the instance name and select Restart to restart the given instance. This is necessary to make sure the service account will be using the new security settings according to the policies setting above

 

Untitled.png

 

5. Click on Yes to restart the instance

 

Untitled2.png

 

6. Click Yes on the message about stopping the SQL Server Agent as part of the restart operation

 

Untitled3.png

 

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

 

 

 

 

 

 

BrunoGabrielli_9-1640101629002.png

 

8. 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), -- Indicates that a principal tried to log on to SQL Server and failed
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

 

 

9. 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

 

BrunoGabrielli_10-1640101836363.png

 

10. 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

 

 

 

 

 

BrunoGabrielli_11-1640101927839.png

 

Now you’re all set. Just remember to verify with both 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) :smile:

 

BrunoGabrielli_12-1640101985203.png

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

 

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.

2 Comments
Co-Authors
Version history
Last update:
‎Feb 07 2022 07:55 AM
Updated by: