SQL 2012 Security Audit 1

  • Article

While the SQL Server audit is not difficult to setup, I am still seeing many folks use and ask about SQL server side trace to address their auditing needs. Though the SQL Server Audit is a bit confusing the first time or two you set it up it is vastly more flexible and much easier to get the data out that you want. This will be a few posts ince I want to deal this out in byte (sp.) size chunks to get you running with something simple like a basic server login audit. SQL Server Audit started its life in SQL 2008, so even if you have not upgraded to SQL 2012, this is still a useful resource for you.

If you want to hit the ground running and figure out what it all means later, start here! To create a basic who has logged in or attempted to login audit, these are the least number of steps, and the minimum amount of information needed. You will also notice that there is an AUDIT_CHANGE_GROUP for the purpose of auditing your audit. It should be fairly obvious that after the audit is disabled, there will be no auditing taking place; however it will log the disable as its last entry.

  1. Create a server audit
  2. Turn on the audit
  3. Create a server audit specification
  4. Turn on the Server specification
  5. View the wonder of your work

 

Step 1

USE [master]

GO

CREATE SERVER AUDIT [Server_AuditLogins]

TO FILE

( FILEPATH = 'C:\SQLAudit'  

)

 

Step 2

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

GO

 

Step 3 and 4

CREATE SERVER AUDIT SPECIFICATION [Server_AuditLoginsSpecification]

FOR SERVER AUDIT [Server_AuditLogins]

     ADD (SUCCESSFUL_LOGIN_GROUP),

     ADD (FAILED_LOGIN_GROUP),

     ADD (AUDIT_CHANGE_GROUP)

WITH (STATE = ON)

GO

 

Step 5

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

GO

 

A word of warning about the fn_get_audit_file, IF, you fat finger the location of the wildcard file name, or the directory, or the drive, the function will not fail, it will return column headers but no data. This can be a bit misleading since your first assumption would be that the function would fail, not the case. So be careful with your file and folder names, passing complete garbage as the file location will still yield a set of column headers, just no data. If all else fails, simply go to the directory and make sure you have a file there! To stop and drop your audit, the following commands may be useful to you.

 

To stop and drop your audit, the following commands may be useful to you.

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

GO

DROP SERVER AUDIT SPECIFICATION [Server_AuditLoginsSpecification]

GO

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

GO

DROP SERVER AUDIT [Server_AuditLogins]

GO

 

When deciding how to handle your audit, consider these things.

  1. Use MAXSIZE option. The default MAXSIZE is unlimited, so it will run until your drive is full.
  2. Consider logging to a target that is the best option for you, you can use the Windows Application or Security Event Log as well. For the purpose of a third party application scraping your Windows logs, this could come in handy but, be cognizant of how much you are logging, event logs have been known to fill depending on the Event Log options that are set.
  3. To keep an audit from filling your disk when using file as the target, use MAX_ROLLOVER_FILES combined with MAXSIZE, however be cognizant of your retention requirements. MAX_ROLLOVER_FILES will delete the oldest file when evaluated. 
  4. There is an option not demonstrated here that you should be aware of, the books online covers it pretty thoroughly, so I see little need to dive too deep. There is a parameter called ON_FAILURE, this tells the audit what to do if the target cannot be written to. Your options are CONTINUE, SHUTDOWN (Shutdown the SQL Instance), or FAIL_OPERATION (any action that causes an audit event will fail). Here is the tricky part on using SHUTDOWN, what do you think will happen if you try to start the SQL Server again and the audit target is still offline…? Depending on what you are auditing, this could put you in a tricky situation. Eventually
    I think this will be one of our more fun yet annoying to the DBA cases, much like when folks test a logon trigger and manage to lock themselves out of their SQL Server. I will provide a rescue from this night mare in the final SQL Audit post. I have not forgotten that in 2012 we added a predicate to the Server Audit; I will cover it in the next few posts as well. This should be enough to get you running with a basic login audit; I will dive into the Database specific audit in the next post. 

Happy Auditing!

Shep

 

CREATE SERVER AUDIT (Transact-SQL)

https://msdn.microsoft.com/en-us/library/cc280448.aspx

CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)

https://msdn.microsoft.com/en-us/library/cc280767.aspx