Chapter 6, Lesson 3 of “Exam 70-462: Administering Microsoft SQL Server 2012 Databases” is all about SQL Server Audit which is something I had never heard of before. This falls under the “Optimise and Troubleshoot” objective. I’ve gone through the lesson in the book and had a play around and this is what I’ve found…
SQL Server Audit allows you to set up auditing at the instance or database level (only 2012 Enterprise Edition allows database level auditing) to track activity, including who is connecting to the instance, who is changing objects, who is updating permission etc. The audit logs are written to either a flat file, or the Windows Security or Application logs. Access to the Windows Security log is restricted whereas the Windows Application log can be access by anyone who can authenticate against the server so Microsoft recommends that you send the audit data to a different machine to the one you are auditing and only give certain people access to the logs so that anyone making unwanted changes to the instance cannot tamper with the audit logs to hide what they are doing. The destination server should be configured so that only Audit Administrators and Audit Readers have access.
Setting up a SQL Server Audit
To set up SQL Server Audit we firstly need to create a new Server Audit. In SSMS expand the Security node, right-click on Audits and select New Audit… The Create Audit window will be displayed.
Enter a name and the queue delay, which is the delay in milliseconds before audit actions are processed.
Choose the On Audit Log Failure which tells the engine what to do if the auditing fails: either continue, shut down the engine or just fail with no event written.
Finally choose the destination which can either be a file or the Application or Security log. We’re going to write to the Security log and this could also have been set up using the following T-SQL statement against the master DB:
CREATE SERVER AUDIT [SimonTest]
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
The audit is disabled by default so you’ll need to right-click an enable it.
In order to write to the Windows Security log the SQL Server service account must have the Generate Security Audits policy. I’m running everything locally on Windows 7 Ultimate and this can be found under Control Panel -> System and Security -> Administrative Tools -> Local Security Policy -> Security Settings -> Local Policies -> User Rights Assignment -> Generate security audits.
You’ll also need to enable both the Success and Failure audit attempts which can be found in the Audit Policy node of the Local Policies.
We’ll also create a second audit that writes to a file so we can pick and choose where we want to send the audit log data to.
Now we can set up both Server Audit Specifications and Database Audit Specifications depending on what level and what actions you want to log. For a list of all the available actions see http://msdn.microsoft.com/en-us/library/cc280663.aspx
Setting up a Server Audit Specification
In this example we’re going to set up audits for the DATABASE_CHANGE_GROUP for the two audits we set up so that we write this audit log info to the Security log and the file.
In SSMS, under the Security node right click on Server Audit Specifications and select New Server Audit Specification…
Enter a name, select the appropriate audit (firstly we’ll set up the one using the Security log) and then select DATABASE_CHANGE_GROUP as the Audit Action Type. This will audit and changes to any of the databases on that instance.
Create a second Server Audit Specification that uses the audit that write to the file and against select DATABASE_CHANGE_GROUP as the Audit Action Type.
The Server Audit Specifications will be disabled by default so you will need to enable these in SSMS.
We could have also the following T-SQL to create on the Server Audit Specifications:
CREATE SERVER AUDIT SPECIFICATION [DB Change Security Log]
FOR SERVER AUDIT [SimonTest]
Now if we make a change to one of the databases on the instance, e.g. change the recovery model of one of the databases we should see an entry written to both the Windows Security Log and the file.
The entry in the Windows Security Log will look as follows. In this example I’ve changed the recovery model of the database [Simon] to be bulk-logged.
We can also view the log by right-clicking on the Audit in SSMS and selecting View Audit Logs. This opens the SSMS log viewer and pulls the SQL Audit events out of the Security log.
NB. you’ll see in the Select logs bar on the left that you can view SQL Audit log for both the Windows Application and Security logs. Also if you expand the Audit Collection node you will also be able to see logs for any file audits that have been set up.
If we browse to the folder where we set up our file audit you will see a .sqlaudit file exists with the name of the audit and then a guid as the file name, e.g.
It’s not easy to read this in a text file app such as Notepad.exe so it’s best to view it through SSMS Log File Viewer. With the file log each piece of information is written to a column in the Log Viewer so it is easy to filter the logs to check a specific database or user. It’s also easy to view all the individual statements that have been run.
Setting up a Database Audit Specification
Now we’re going to set up a Database Audit Specification to log all select statements made against a certain table. In SSMS expand the appropriate database, then right-click on Database Audit Specifications under the Security node and select New Database Audit Specification…
In this example we’re using our existing file audit and we’re going to log all SELECT statement run by any users running under dbo against the object dbo.TestSQLAudit.
Now if we enable the new Database Audit Specification and run a select statement against dbo.TestSQLAudit we get a new entry in the SSMS Log Viewer that records the full statement that was executed.
We can also create Database Audit Specifications using the following T-SQL statement
CREATE DATABASE AUDIT SPECIFICATION [SELECT Logger]
FOR SERVER AUDIT [SimonFileTest]
ADD (SELECT ON OBJECT::[dbo].[TestSQLAudit] BY [dbo]);
To enable the specification using T-SQL run
ALTER DATABASE AUDIT SPECIFICATION [SELECT Logger] WITH (STATE = ON);
Alternatively we could have added the WITH (STATE = ON) clause to the end of the CREATE DATABASE AUDIT SPECIFICATION statement.
There are a bunch of DMVs and DMFs that can be used to view the audit configuration on a server. Here’s the list pinched straight from the exam guide
- sys.dm_audit_actions Stores information on each audit action and each audit action group that you can use with SQL Server Audit
- sys.server_audits Enables you to see information about each SQL Server audit configured on an instance
- sys.dm_server_audit_status Enables you to see information about currently configured audits
- sys.server_audit_specifications Enables you to see information about server audits
- sys.server_audit_specifications_details Enables you to see information about actions that are audited at the server level
- sys.database_audit_specifications Enables you to see information about currently configured database audit specifications
- sys.database_audit_specifications_details Enables you to see information about actions that are audited at the database level
- fn_get_audit_file Enables you to query a file-based target for audit information
Setting up Login Auditing
There is a third type of auditing that the exam guide briefly mentions as this is Login Auditing which is configured on the Security page of the Server Properties dialog box as shown below. The options are pretty obvious what they do. With the Login Auditing you don’t use the SQL Audit to specify where it’s logs to – it logs to the SQL error log.
The lesson in the exam guide finally talks about c2 Audit Mode (which can audit successful and failed calls as defined by the c2 security standard), Common Criteria Compliance (which supersedes c2 Audit Mode) and Policy-Based Management (which allows you to manage more than one instance across multiple servers). For on these subjects see the following links:
c2 Audit Mode: http://msdn.microsoft.com/en-us/library/ms187634.aspx
Common Criteria Compliance: http://msdn.microsoft.com/en-us/library/bb326650.aspx
Policy-Based Management: http://msdn.microsoft.com/en-us/library/bb510667.aspx
If you’re studying for the 70-462 exam, the guide mentions an exam tip in the Policy-Based Management section that says there is an existing policy called Database Auto Shrink that can be used instead of created your own policy. I guess you need to know that this existing policy exists.