SQL 2012 Security Audit 3 (Predicate and Failure)

  • Article

This is the script to bring it all together. In this case I have opted for just a server side specification with a filter on the database and exclusion of system objects. You can certainly leave the access to system objects, however it will generate a significant number of audit entries. I encourage you to investigate this on an idle system and decide for yourself.

You will also notice that I have added a shutdown on failure statement. If you have not guessed, if the engine ever loses the ability to write to the sqlaudit file, the engine will stop. The bonus is, when you try to start the SQL Server and the sqlaudit file is still not accessible, the server will fail to start. On the bright side the error log will tell you exactly what to do. 

USE [master]

GO

CREATE SERVER AUDIT [Server_Audit_AW]

TO FILE

( FILEPATH = 'F:\SQLAudit'

        ,MAXSIZE = 10 MB

       ,MAX_ROLLOVER_FILES = 10000

       ,RESERVE_DISK_SPACE = OFF

)

WITH

( QUEUE_DELAY = 1000

       ,ON_FAILURE = SHUTDOWN    

)

WHERE database_name = 'AdventureWorks2012' and schema_name <> 'sys'

 

--Enable the Audit

ALTER SERVER AUDIT [Server_Audit_AW] WITH (STATE = ON)

GO

 

--create the specification and enable it

Create SERVER AUDIT SPECIFICATION [Server_Audit_Access]

FOR SERVER AUDIT [Server_Audit_AW]

       ADD (SCHEMA_OBJECT_ACCESS_GROUP)  

WITH (STATE = ON)

GO

 

--reviewing the data

 

SELECT * FROM fn_get_audit_file('f:\SQLAudit\*.sqlaudit',default,default);

GO

 

  --select * from [AdventureWorks2012].[Production].[Product] where ReorderPoint < 10 and size in ('L','XL')

 

 Make your audit destination disappear, in my case i used a USB device and disconnected it.

 

The first shutdown

2012-07-12 20:20:24.65 spid7s The server was stopped because SQL Server Audit 'Server_Audit_AW' is configured to shut down on failure. To troubleshoot this issue, use the -m flag (Single User Mode) to bypass Audit-generated shutdowns when the server is starting.

 

On restart,

2012-07-12 20:21:10.68 spid4s Recovery is complete. This is an informational message only. No user action is required.  

2012-07-12 20:21:11.02 spid7s The server was stopped because SQL Server Audit 'Server_Audit_AW' is configured to shut down on failure. To troubleshoot this issue, use the -m flag (Single User Mode) to bypass Audit-generated shutdowns when the server is starting.

 

Looks easy right? Just start the server with the –m option…? Well, sort of easy. On my server I run the following, and use SQLMD to connect to the server. You will notice I use the “–s” option to identify the instance name of SQL 2012.

C:\Program Files\Microsoft SQLServer\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -sSQL2012 –m

If your environment is like everyone I have ever supported you will immediately have an issue as soon as you start SQL in single user mode, as you have to be the first user connection and before you know someone has beat you to it. So, here is what you do.

When you use the “–m”, parameter pass the application name of SQLCMD, this will start the engine in single user mode and only allow an application named SQLCMD to connect to SQL. Yes, if you are not the first SQLCMD connection you will have the same problem all over again. If that does happen, disable all of your protocols on SQL except Shared Memory. 

Then, you will have to be SQLCMD and on the server to get a connectionto the engine! 

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn>sqlservr.exe -sSQL2012 –m”SQLCMD”

Obviously your SQLCMD connection string will be

SQLCMD –Sserver1\sql2012 –E

Then in my case I would run the following;

 

  1. Disable the audit spec
  2. Disable the server audit
  3. Modify the path
  4. Enable the server audit
  5. Enable the server spec
  6. Return to normal operation, don’t forget to re-enable your network protocols of you did disable them. 

 

ALTER SERVER AUDIT SPECIFICATION [Server_Audit_Access] WITH (STATE = OFF)

GO

ALTER SERVER AUDIT [Server_Audit_AW] WITH (STATE = OFF)

GO

ALTER SERVER AUDIT [Server_Audit_AW] TO FILE (FILEPATH = 'C:\SQLAudit')

GO

ALTER SERVER AUDIT [Server_Audit_AW] WITH (STATE = ON)

GO

ALTER SERVER AUDIT SPECIFICATION [Server_Audit_Access] WITH (STATE = ON)

GO

 SHUTDOWN

GO

 

Enable any protocols that were disabled, and start your SQL Server using normal methods, service control manager or net start. Your audit is back online, and your server is back online. 

 

Happy Auditing!!

Shep