Auditing Linked Servers
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.
Join the DZone community and get the full member experience.Join For Free
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:
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:
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.
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.
Published at DZone with permission of Thomas LaRock , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.