Talk to any financial institution or bank they will be super paranoid when it comes to security and auditing policies applied to their organizations. In a recent session to one of our customers, I had to enter their premises and I had to go through a number of screening. From the entrance, car parking, reception, elevators and everywhere there was someone asking us for some information. The more I think about it, the more frustrated I become. After returning home, I thought through the complete incident with my family. I was pleasantly surprised the calmness at which they were talking to me about process.
Wow, it is a great way to learn patience and why this is important. Organizations have a reason, they need data secure, they want to have procedures so that there is no data loss, no theft of data and many more. In this competitive world this is super critical. Hence these procedures are important, critical for survival. And hence this blog is inspired to the Auditing capability with SQL Server 2012 and what I found interesting.
Creating our Audit
Creating an audit is as simple as going through a series of wizard with some basic data. Go to SQL Server Management Studio -> Security -> Audits and create a new audit. Go ahead and add the FilePath, in our example I have added it as “C:\Audit”.
The TSQL equivalent for the same is:
CREATE SERVER AUDIT [Audit-20141115-213944] TO FILE ( FILEPATH = N'C:\Audit' ,MAXSIZE = 0 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE) GO
After creating the same, we will make sure this Audit is enabled. This ensures where our Auditing data will get into. In our example it is configured to a File as shown above.
Once we enable the Audit node, the next step is to add the Audit operation. Here we are going to add the “User Defined Audit Group”
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141115-214433] FOR SERVER AUDIT [Audit-20141115-213944] ADD (USER_DEFINED_AUDIT_GROUP) GO
Creating User defined Audit entries
This is a capability that was introduced with SQL Server 2012 and I personally feel this is quite powerful for a couple of reasons. Now, apart from standard auditing capability now applications can raise specific auditing into the audit log so that we can track or audit logically from an applications point of view.
The simplest way to raise an audit record from an application is using the stored procedure sp_audit_write command. A typical command looks like:
EXEC sys.sp_audit_write 1, 0, N'This is an audit from Pinal'
This command will now put an entry into my audit, we just defined in the previous. To view the entry, select the “View Audit Log” option from the Audit we just created. The Log viewer looks like below:
In the example above, we can see the details. Please note the Audit “Succeeded as False” because we sent the second parameter to sys.sp_audit_write as 0 hence it is False. If you want to enter it as True, pass the value as 0. Also the statement that got us this information is available as part of “Statements” line item.
If you want to view all the entries into our audit file, we can use the sys.fn_get_audit_file function.
SELECT * FROM sys.fn_get_audit_file('c:\Audit\*', NULL, NULL)
I hope you got a flavor of how custom user defined audits can be defined. This is a great way to use the auditing feature from your application’s point of view. Do let me know if you found this useful and if you are planning to use the same in your environments.
Reference: Pinal Dave (https://blog.sqlauthority.com)