SQL 2008 Audit and Linked Servers

• dbajunior

  SSCrazy

  Points: 2307

  More actions

  September 14, 2010 at 2:22 pm

  #226348

  The goal:

  To set up SQL 2008 Audit processes on one server to monitor multiple SQL 2008 remote (linked) servers.

  The code:

  -- Step 1

  USE MASTER

  Create SERVER Audit Audit_Prod1

  TO FILE (filepath = 'C:\AuditServer\AuditLogs\Prod1\',

  MAXSIZE = 1000 MB,

  RESERVE_DISK_SPACE=OFF)

  WITH (QUEUE_DELAY = 1000,

  ON_FAILURE = CONTINUE);

  ALTER SERVER AUDIT Audit_Prod1 WITH (state = on)

  -- Step 2

  USE MASTER

  Create SERVER Audit Specification Audit_Prod1_Spec

  FOR SERVER AUDIT DeAudit_Prod1

  ADD (FAILED_LOGIN_GROUP),

  ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),

  ADD (AUDIT_CHANGE_GROUP) WITH (STATE = ON);

  -- Step 3

  Create DataBase AuditProduction

  -- Step 4

  (Here, create a linked server)

  -- Step 5

  Use AuditProduction

  CREATE DATABASE AUDIT SPECIFICATION Audit_Prod1_DBSpec

  FOR SERVER AUDIT Audit_Prod1

  ADD (INSERT, UPDATE, DELETE ON (linkedserver1.db1.dbo.tablename)

  BY dbo)

  WITH (state = On)

  The error:

  Msg 117, Level 15, State 1, Line 10

  The object name 'linkedserver1.db1.dbo.tablename' contains more than the maximum number of prefixes. The maximum is 2.

  The problem:

  Referencing linked servers when creating a database audit spec.

  The question:

  Is auditing multiple linked servers even possible using SQL 2008 audit?

  Thanks in advance for any assistance you can provide.

• Derrick Smith

  SSChampion

  Points: 10833

  More actions

  September 14, 2010 at 2:30 pm

  #1220991

  No, as far as I know you cannot set up auditing this way. A central management server might be able to accomplish this, however I don't use one so I can't confirm.

  edit: typo

• Anthony Fourie

  SSC-Addicted

  Points: 407

  More actions

  May 31, 2012 at 4:54 am

  #1495122

  Hi,

  Got to agree with Derrick on this one... Though not sure about creating a single audit specification to audit multiple instances. You could possibly use PBM to check that certain audit specifications exist on the remote servers... Then pull the results via CMS to a central repository, would have to sit and play with it a little to give you definitive answer.

  There are a few resources on CMS if you would like to take a look:

  http://www.brentozar.com/archive/2008/08/sql-server-2008s-new-central-management-server/

  http://sqlserverpedia.com/wiki/Central_Management_Server

  http://www.simple-talk.com/sql/database-administration/policy-based-management-and-central-management-servers/

  Hope that helps ,

  Anthony

  /Ants