Last month I noticed this tweet from @SQLPrincess on #sqlhelp, asking if there was a way to find out what happened to a linked server:

The short answer is that SQL Server does not track this information by default. You need to be auditing linked servers for modifications before they happen.

I did my best to reply, suggesting the use of SQL Audit:

I suggested setting up an audit against the sys.servers table. But that’s the wrong approach. The correct approach is to examine the system stored procedures used to create, alter, or delete linked servers.

Here’s a list of the system stored procedures that can affect the sys.servers table:

sp_addlinkedserver
sp_addserver
sp_dropserver
sp_addlinkedsrvlogin
sp_droplinkedsrvlogin
sp_serveroption
sp_setnetname

Our goal here is to track who has added, removed, or modified a linked server in any way, and when the action happened. SQL Audit is perfect for tracking these events, but you will need to configure this audit manually.

Getting the audit up and running is simple enough. First, we will create the Server Audit object. This is the object that will tell SQL Server where and how to store the captured audit log events. Here’s is a quick version to get you started, we will create a Server Audit named ‘LinkedServer’:

CREATE SERVER AUDIT [LinkedServer]
TO FILE 
(	FILEPATH = N'C:\TeamData\AuditLogs\LinkedServer\'
	,MAXSIZE = 0 MB
	,MAX_ROLLOVER_FILES = 2147483647
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [LinkedServer] WITH (STATE = ON)
GO

The Server Audit exists and is running. We next create a Database Specification Audit named ‘LinkedServerMaster’. We will track EXECUTE statements for the system stored procedures listed earlier:

USE [master]
GO
 
CREATE DATABASE AUDIT SPECIFICATION [LinkedServerMaster]
FOR SERVER AUDIT [LinkedServer]
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_addlinkedsrvlogin] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_droplinkedsrvlogin] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_serveroption] BY [dbo]),
ADD (EXECUTE ON OBJECT::[sys].[sp_setnetname] BY [dbo])
WITH (STATE = ON)
GO

OK, the audit objects are in place, so we next create a linked server. Notice I’m using an instance of SQL 2012 for the audit and connecting to an instance of SQL2016:

 

auditing linked servers

 

Now, let’s check the audit logs and see if we have any results (click to embiggen):

 

auditing linked servers

 

Success! We’ve captured details on the creation of the linked server along with options set and logins created.

Summary

SQL Audit is a great way to capture details on who is making changes to your instance, and when. However, as mentioned before, you must have an audit configured prior to any linked server issue happening. If you are interested in this audit, I’d recommend you configure the audit right after SQL Server is installed.

 

%d bloggers like this: