SQL Server auditing – how to be alerted about important auditing events

While numerous native auditing methods are available for SQL Server, none of them provides an out-of-the-box feature to generate an alert when a specific SQL Server event is detected. We will look to see how to come close with native solutions and also an out of the box solution, ApexSQL Audit

SQL Server Audit

SQL Server Audit allows set up of automatic auditing and the creation of audit specifications for server and database level events, which then can be written to the security or application event logs or to the audit file. SQL Server Audit makes available the tools and processes that must be enabled, stored, and then to view audits on server and database objects.

SQL Server Audit allows recording audits on SQL Server and/or database levels. The audit event will be logged every time when auditable actions are encountered.

The following example will show how to get notified by an email on any security event related to SQL Server login changes

First, create a new SQL Server Audit that will dump all the audit event into an .audit file

In the object explorer navigate to the Security -> Audits folder and from the context menu select New Audit

This will open the Create Audit dialog like the one below, but depending on the SQL Server version the layout can be slightly different:

Let’s quickly review some less specific options that can be set here:

Audit name is self-explanatory

Queue delay (in milliseconds) allows defining the time delay after which the audited events will be written in the log(s)

Shut down server on audit log failure will force the server shut down when the server cannot write data to the audit repository. This option should be used only when an audit failure could seriously affect the security or integrity of the server

Note: The login used for auditing must have the SHUTDOWN permission in order to successfully use apply this option

Fail operation will prevent actual server operation to be executed if SQL Server Audit cannot write in the repository for any reason. Operations that doesn’t trigger the audited events will be allowed to continue with execution. This option should be used only if the auditing integrity is more important than the full operability of SQL Server.

Next is the option to choose the destination where the audits will be logged:

When Windows Security Log and Application Log are selected for storing audit events, all other options in this dialog will be disabled. Behavior, in this case, will be controlled by Windows according to the settings for the selected log. Generally the most user-friendly option that ensures the highest security, as well as the best stability and performance, is the File option, which will store the binary log file in the Windows file system, so we will base our solution on that option. The rest of the options are mainly self-explanatory, and more information can be found here

In the case that the audit has to support database mirroring, a specific GUID must be assigned to the audit that matches the GUID in the mirrored database. If this is the case, using a SQL script for creating the new audit must be used, and in our case it would look like:

USE [master]
GO

CREATE SERVER AUDIT [Audit Login Changes]
TO FILE 
(	FILEPATH = N'C:\SqlAudits\'
	,MAXSIZE = 1024 MB
	,MAX_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '<enter_appropriate_guid_here>'
)
ALTER SERVER AUDIT [Audit Login Changes] WITH (STATE = ON)
GO

Now that the new SQL Server Audit object is created, the Server Audit Specifications object for the created audit object must be set

In the object explorer navigate to the Security -> Server Audit Specifications folder and from the context menu select New Server Audit Specification

The Server Audit Specification object describes what server level events will be audited. The only one Server Audit Specification object can be created per the Server Audit object; however Server Audit Specification object can contain multiple audit actions groups. To track all the changes made on logins, users, and roles, the following Audit Action Type displayed in the next image should be created. More on the specified Audit Action types is available in appendix

And finally, after specifying the name for this Server Audit Specification object and entering the name of the Audit object to which Server Audit Specification object will be related, the SQL Server audit solution is set.

For those who prefer to create the Server Audit Specification object by using T-SQL, here is a script that will do this for you:

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Audit Login]
FOR SERVER AUDIT [Audit Login Changes]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON)
GO

Now, after the new SQL Server Audit is set to collect the specified security events, the system that will raise a notification when event occurs have to be established. First, a script that is capable to read and analyze the .audit file and then send an email to the specified user must be created. Here is an example:

DECLARE @TempTime datetime2;
DECLARE @Counter int;
DECLARE @MailQuery NVARCHAR(MAX);
SET @Counter = 0
SET @TempTime = (SELECT TOP 1 LastEventTime FROM dbo.TempAuditTime)
SET @Counter= (SELECT COUNT (event_time) 
 FROM sys.fn_get_audit_file('C:\SqlAudits\*.sqlaudit', default, default)
 WHERE DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time ) > @TempTime)
 PRINT @Counter
 IF @Counter > 0 

 	BEGIN
	SET @MailQuery = CAST ((SELECT td = DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time), '', 
							td =statement, ''
					FROM sys.fn_get_audit_file('C:\SqlAudits\*.sqlaudit', default, default)
					WHERE DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time ) > @TempTime FOR XML PATH('tr'), TYPE
					) AS NVARCHAR(MAX))

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Security Event Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Event time</th><th>Statement</th>'+
	N'</tr>' +
    @MailQuery +
    N'</table>';
	
 PRINT @tableHTML

 -- Update temp table event time
USE master

	UPDATE dbo.TempAuditTime
   SET [LastEventTime] = SYSDATETIME ()

-- Send Email
EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'SecurityEvent', 
		@recipients = 'nikola.dimitrijevic@apexsql.com',
		@body = @tableHTML,
		@body_format = 'HTML',
		@subject = 'Security Event Occured';
		
    	END; 

The particular script requires a table dbo.TempAuditTime to be created, as it will be used to store the information about the time when last file read has occurred and this DateTime will be used for comparison with the time when event has occurred. If the time of the event is newer the email with event details will be sent

For that purpose, the SQL Server job can be created that will execute this script. A SQL Server Agent job allows to be scheduled as frequently as needed, but how frequently a job will be executed depends also on how often there is a need to check the SQL Server Audit repository file in pursuit for important events and then send an email alert. In our case, an email alert is HTML formatted as a table to ensure better readability of the important information

Drawbacks of this method are that it requires advanced T-SQL knowledge, significant SQL Server resources can be used depending on the schedule frequency, but also on the size of the .audit file that have to be read each time. Finally, it requires the whole procedure described above to be repeated manually and set manually up for each event type and database

ApexSQL Audit

ApexSQL Audit is a SQL Server auditing and compliance tool that tracks more than 200 events on multiple SQL Server instances and their objects and saves them into one central tamper-evident repository. Captured information is available through a range of built-in reports with additional custom report designer to meet specific user requirements.

One of the features ApexSQL Audit provides is alerting. It consists of predefined system alerts (monitoring basic health status like disk space and central repository usage), data alerts (monitoring captured events) and custom SQL alerts (ability to alert on any event and/or metric that can be retrieved with SQL script)

To create a data alert in ApexSQL Audit:

1. Start the ApexSQL Audit main application

2. Select the Manage Alerts button in the left pane and in the main ribbon, and click the New button

   

3. Select Auditing alert (the Custom script alert option is described below)

   

In the further text we’ll focus on the alert options. The following steps generally apply to all alert types with difference in alert specific conditions.

1. Enter the alert information in the Alert name and notification options dialog. Here, the alert name, subject and report body text can be specified. Placeholders like $LoginName$ will be replaced when the actual alert is triggered, so the user can fully customize the format of the alert report he will be getting. The Limit the number of reports for this alert to one per minute (for each server) option ensures that the end user doesn’t receive an inordinate amount of reports, in case of a spike in alerts

   

2. Select the SQL Server instance(s) that you want to be alerted and press next to continue with setting up the conditions for the alert

   

3. The ApexSQL Audit alert feature utilizes an advanced filter which allows for the granular setting of alerting conditions. Users can set conditions under which the event will trigger the alert. Events can be filtered using the application name, client host, database name, database operation, login name, object name, object type, schema name, server operation and text data. All conditions can use is and is not operators while text data can additionally use contains and does not contain operators.

   

4. Additionally, for case sensitive collated databases, the Case sensitive checkbox should be checked

5. In the next Actions dialog the user can choose whether he want to get the alert notification via email. After checking the Send this alert report via email checkbox, the mail account can be configured by clicking on the link Click here to configure an account for sending e-mail.

   

6. Enter the destination e-mail address in the To field, and then press the Send test e-mail link. If the test mail is received correctly, press Next

7. The last, Alert summary dialog, displays all the relevant information about the created email. If everything is correct, by pressing Finish, the new alert will be created

   

In case that some advanced/custom alerting ability is needed and the Custom script alert option is used in step 3, replace the steps 3 to 6 with the following:

1. Select the Custom script alert option, which will allow executing the user defined script and triggering or not triggering the alert according to the script result

   

2. Enter the alert information in the Alert name and notification options dialog – the alert name, Severity (Low, Medium, or High), subject and the report body text can be specified

   

3. Select the SQL Server instance(s) that you want to be alerted about in the Server deployment dialog. Press Next to continue with setting up the conditions for the alert

   

4. In the Conditions dialog, the numeric value and script that will be executed must be specified. The script must be designed to return a numeric value, and if this numeric value is equal or higher than the numeric value entered, an alert will be triggered. In our example, a script that will return the size of the ACMEDB database in MB is used. If the database size is equal or greater than 4096 MB (4 GB), the alert will be triggered.

   

The Check interval dialog allow us to set the date/time when the alert will start with executing the script as well as the recurrence period. The recurrence period can be set by user by entering the recurrence value and selecting the time measure from the drop-down menu



5. When everything is set, the Alert summary dialog will appear upon pressing the Next button. This allows the final check of the custom alert parameters. If everything is correct, press the OK button and the new custom alert will be created

   

Once the alert is triggered, besides in an e-mail form, all raised alerts can be reviewed in the History tab of the Alerts dialog

From the Manage tab, each created alert can be edited, deleted, disabled or enabled

Each native SQL Server auditing methods has some significant limitations and/or drawbacks while at the same time each one requires some advanced SQL knowledge for establishing proper alerting system. ApexSQL Audit, at the other hand, allows setting up alerts using the standard or advanced options in a several mouse clicks, while ensuring the efficient auditing and reporting process for multiple SQL Server instances, databases, and their objects at the same time.

Appendix

• DATABASE_ROLE_MEMBER_CHANGE_GROUP – Event is logged whenever a login is added/removed from a database role
• SERVER_ROLE_MEMBER_CHANGE_GROUP – Event is logged whenever a login is added/removed from a fixed server role
• DATABASE_PERMISSION_CHANGE_GROUP – Event is logged for a GRANT, REVOKE, or DENY events
• SERVER_OBJECT_PERMISSION_CHANGE_GROUP – Event is logged whenever a GRANT, REVOKE, or DENY is issued for a server object permission
• SERVER_PERMISSION_CHANGE_GROUP – Event is logged when a GRANT, REVOKE, or DENY is issued for permissions in the server scope, such as creating a login
• DATABASE_PRINCIPAL_CHANGE_GROUP – Event is logged when users, are created, altered, or dropped from a database
• SERVER_PRINCIPAL_CHANGE_GROUP – Event is logged when server principals are created, altered, or dropped

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.