Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Auditing Linked Servers

DZone's Guide to

Auditing Linked Servers

SQL Audit is a great way to capture details on who is making changes to your instance, and when. But you must have an audit configured prior to any linked server issue.

· Database Zone ·
Free Resource

Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.  

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:

Image title

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:

Image title

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:

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

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:

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 enlargen):

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.

Download AnzoGraph now and find out for yourself why it is acknowledged as the most complete all-in-one data warehouse for BI style and graph analytics.  

Topics:
database ,sql ,auditing ,sql server ,linked servers ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}