By: Matteo Lorini | Comments (1) | Related: > Auditing and Compliance
Problem
When restoring a database, that has audit specifications defined, on a different server, the audit events are defined but the audit mechanism is not able to capture audit data. In this tip we walk through the steps you need to take to enable capturing of audit data for the database.
Solution
In this example, I will be using two SQL Server 2008 servers named SQLTEST1 and SQLTEST2.
Let's create the Audit definition on SQLTEST1 for database TEST.
TO FILE
( FILEPATH = N'C:\AUDIT\'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 90
,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
USE TEST
GO
CREATE DATABASE AUDIT SPECIFICATION [Test_Audit_DB]
FOR SERVER AUDIT [Test_Audit]
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (DELETE ON DATABASE::[TEST] BY [dbo]),
ADD (INSERT ON DATABASE::[TEST] BY [dbo]),
ADD (UPDATE ON DATABASE::[TEST] BY [dbo]),
ADD (SELECT ON DATABASE::[TEST] BY [dbo])
WITH (STATE = ON)
GO
Here we can see that the Audit Specification has been defined.
In order to verify that my audit trace is working, I ran the following code and verified that Audit information was captured.
Let's backup the TEST database so we can restore it on the SQLTEST2 server.
Let's restore the TEST database on SQLTEST2 as SQLTEST2TEST.
After the restore, we can see that the database has been restored and we can also see the Audit Specifications defined.
Even though the audit definition is defined, the server level Audit definition is not present on SQLTEST2 and therefore audit data is not captured. The reason for this is that when restoring or attaching a database that has an audit specification defined, but the specific GUID for that audit specification does not exist in the system tables this causes an orphaned audit specification. In our case, since an audit with a matching GUID does not exist on server SQLTEST2, no audit events will be recorded.
Fixing the Orphaned Audit Specification
In order to correct this situation, we need to issue the CREATE SERVER AUDIT command on SQLTEST2 to create a new server audit with the specific GUID from SQLTEST1.
If the oringial server is available, we can script out the Audit definition as follows:
In my case the script generated the following output.
TO FILE
( FILEPATH = N'C:\AUDIT\'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 90
,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'ad6f9c87-5b56-44d4-b18f-d835aa4cf9b8'
)
GO
to make the Audit work on SQLTEST2 I need to issue the following command, making any adjustments necessary such as the FILEPATH in the below example. Also, note that the AUDIT_GUID value is the same for both SQL Server instances. This is so the audit can be tied correctly again.
TO FILE
( FILEPATH = N'C:\SQLTEST2\AUDIT\'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 90
,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'ad6f9c87-5b56-44d4-b18f-d835aa4cf9b8'
)
GO
If the original server is not accessible then you can get the AUDIT_GUID by runing the following command on the new server:
This will return output simliar to below.
When we have have the AUDIT_GUID we can issue the CREATE SERVER AUDIT command such as the following to set this up:
TO FILE
( FILEPATH = N'C:\SQLTEST2\AUDIT\'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 90
,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'ad6f9c87-5b56-44d4-b18f-d835aa4cf9b8'
)
GO
Once this has been done your database should begin collecting audit data again.
Next Steps
- For more information about SQL Server Auditing refer to these tips:
About the author
Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
