Auditing Linked Servers

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:

Is there a way to find out who dropped a linked server in SQL Server 2008 R2? #sqlhelp #askingForAFriend #reallyAmAskingForAFriend

— Rebecca Mitchell (@SQLPrincess) January 26, 2018

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:

It’s not tracked AFAIK, you need to track that manually. It doesn’t help you now, but you could consider using SQL Audit on the sys servers table to log activity.

— Thomas LaRock (@SQLRockstar) January 26, 2018

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:

 

 

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

 

 

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.