SQL Server: Audit changes on Instance Configuration, Linked Servers and Agent Jobs

Introduction

Very often with our customers, there is only one person with the DBA role. The configuration of the instances is then under control and if anything strange has to be questioned, the culprit is quickly identified. 🙂
When many people, not necessarily having DBA knowledge, have high permissions (sysadmin) on instances it becomes important to know who does what.

In this blog post, we will see how to get notified by email when certain sensitive points at the instance level are modified.
The elements that I will cover here are the following.

• Instance level configuration (Max server memory, etc.)
• Linked Server (creation or modification ..)
• Agent Jobs (create, drop, etc.)

The default SQL Trace contains some useful data like the files growth events but does not have the information I needed. Plus, as mentioned in the doc, it is deprecated.

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Extended events is a great performance troubleshooting tool but it does not help in this case.

It’s possible to use T-SQL and copy into a table the data from sys.configurations and other DMVs related to the Agent and linked server. At a 5min interval, you can compare the current state with the previous one and send an email alert based on this delta. In most cases, this will tell you what has changed but not who did it.

So finally I decided to use Audits. This feature is not widely used and sometimes underrated, but it is very powerful as we will see.

Audits

So let’s start with creating a new Audit.

I configure 4 rollover audit files of 50MB each located in C:\Temp\.

Now looking at the Server Audit Specification we can see there is nothing related to Linked Server, Configuration, or Agent Jobs.

So I decided to use a Database Specification on the system databases related to the audit I want to perform.
For a configuration change like “Max Server memory”, I can audit the execution of Stored Procedure “sp_configure” in the master database. For Linked Servers I will do the same with all related stored procedure.

Here is the T-SQL.

USE [master]
GO
CREATE DATABASE AUDIT SPECIFICATION [AUDIT___DBA_SPEC_master]
FOR SERVER AUDIT [AUDIT___DBA]
/* Linked Server */
ADD (EXECUTE ON OBJECT::[sys].[sp_addlinkedserver] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_addlinkedsrvlogin] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_droplinkedsrvlogin] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_addlinkedserver] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_addserver] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_dropserver] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_serveroption] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_setnetname] BY [dbo]),
/* Configuration */
ADD (EXECUTE ON OBJECT::[sys].[sp_configure] BY [dbo])
WITH (STATE = ON)
GO

Same thing for the Agent Job related stored procedure in the msdb database.

USE [msdb]
GO
CREATE DATABASE AUDIT SPECIFICATION [AUDIT___DBA_SPEC_msdb]
FOR SERVER AUDIT [AUDIT___DBA]
/* Agent Jobs */
ADD (EXECUTE ON OBJECT::[dbo].[sp_add_schedule] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_add_jobstep] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_update_jobstep] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_update_jobschedule] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_jobschedule] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_add_job] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_update_job] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_delete_job] BY [dbo]),
ADD (EXECUTE ON OBJECT::[dbo].[sp_stop_job] BY [dbo])
WITH (STATE = ON)
GO

Query Audit files

Now, after enabling the Audit and doing some changes, the following queries using sys.fn_get_audit_file will show if something has been changed on my instance and who did it.

select distinct [statement]
	, session_server_principal_name
	, server_instance_name
	, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time
	--, *
from sys.fn_get_audit_file('C:\Temp\AUDIT___DBA*.sqlaudit', default, default)
where [object_name] = 'sp_configure'
  and [statement] not like '%show advanced options%'
order by event_time desc

The Audit contains all the information I need. The login “LAB\Administrator” changed the “Max Dregree of parallelism” setting to value 4.

A similar query for linked server.

select DISTINCT [statement]
	, session_server_principal_name
	, server_instance_name
	, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time
	--, *
from sys.fn_get_audit_file('C:\Temp\AUDIT___DBA*.sqlaudit', default, default)
where [object_name] IN ('sp_addlinkedserver', 'sp_addlinkedsrvlogin', 'sp_droplinkedsrvlogin'
	, 'sp_dropserver', 'sp_addserver')
order by event_time desc

And finally for SQL Server Agent Job.

select DISTINCT [statement]
	, session_server_principal_name
	, server_instance_name
	, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time
	--, *
from sys.fn_get_audit_file('C:\Temp\AUDIT___DBA*.sqlaudit', default, default)
where [object_name] IN ('sp_add_job', 'sp_add_jobstep', 'sp_update_job', 'sp_delete_job', 'sp_stop_job'
	, 'sp_update_jobschedule', 'sp_delete_jobschedule', 'sp_update_jobstep', 'sp_add_schedule')
order by event_time desc

Email

Having this all set up, I can run a Job that will read the audit file and send an email with everything that happened for the last 5 minutes.
I decided to do an email for each type of audited element (Configuration, Agent Jobs, etc..).
The T-SQL script is quite simple:

--	Check interval
DECLARE @CheckPeriodInMinute int = 5

DECLARE @profile_mail varchar(128)= 'DBA_Profile'
      , @subject_mail varchar(128)= 'AUDIT - Configuration change ('+convert(varchar(256),@@servername)+')'
	  , @recipient_mail varchar(128)=' [email protected]'
	  , @html_header varchar(max), @html varchar(max), @html_part varchar(max),  @html_footer varchar(max)

DECLARE @Event TABLE (
	 [statement] varchar(503)
	,session_server_principal_name sysname
	,server_instance_name sysname
	,event_time datetime
)

insert into @Event
	select distinct IIF(LEN([statement])>500, LEFT([statement], 500)+'...', [statement]) AS [statement]
		, session_server_principal_name
		, server_instance_name
		, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS event_time
	from sys.fn_get_audit_file('C:\Temp\AUDIT___DBA*.sqlaudit', default, default)
	where [object_name] = 'sp_configure'
	  and [statement] not like '%show advanced options%'
	  and DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) > DATEADD(MINUTE, -@CheckPeriodInMinute, GETDATE())
	order by event_time desc

if @@ROWCOUNT>0
begin
	set @html_header='<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><style type="text/css">
	body    {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;}
	table   {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#C0C0C0; padding:0px 0px 0px 0px; margin:1px 1px 1px 1px;width:80%}
	tr,td   {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#F5F5F5; padding:0px 0px 0px 0px; margin:1px 1px 1px 1px;}
	th      {font:bold 9pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:2px 2px 2px 2px;}
	th.th2  {font:bold 9pt Arial,Helvetica,sans-serif; color:#333399; background:#ddddaa; padding:2px 2px 2px 2px;}
	h1      {font:bold 16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:15pt; margin-bottom:0pt; padding:0px 0px 0px 0px;}
	h2      {font:bold 14pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;}
	p       {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;}
	</style>
	</head>
	<body>
	'
	set @html_footer='<p><i>Executed on '+convert(varchar,getdate(),120)+' from server "'+isnull(convert(varchar(128),@@SERVERNAME),'')+'"</i></p>'

	SET @html=@html_header+'<h1>AUDIT - Configuration change</h1>
	<p><i>Check done on last '+convert(varchar(20),@CheckPeriodInMinute)+' minutes</i></p>
	<table>
	<tr>
	  <th>statement</th>
	  <th>session_server_principal_name</th>
	  <th>server_instance_name</th>
	  <th>event_time</th>
	</tr>
	'

	declare c1 cursor for
		select 
		'<tr>
		  <td>'+[statement]+'</td>
		  <td>'+session_server_principal_name+'</td>
		  <td>'+server_instance_name+'</td>
		  <td>'+CONVERT(varchar, event_time, 120)+'</td>
		</tr>
		'
		from @Event
		order by event_time desc

	open c1
	fetch c1 into @html_part
	while @@FETCH_STATUS=0
	begin
		set @html=@html+@html_part

		fetch c1 into @html_part
	end
	close c1
	deallocate c1

	set @html=@html+'</table>
	'+@html_footer

	exec msdb.dbo.sp_send_dbmail 
		  @profile_name=	@profile_mail
		, @recipients=		@recipient_mail
		, @subject=			@subject_mail
		, @body=			@html
		, @body_format=		'HTML'
end

Here is an example of the email I receive when someone changes the configuration of an instance :

Audits are very powerful can be very useful to get notified when a change occurs at the SQL Server instance level.