As database administrators, we are constantly quizzed by our superiors to answer questions such as: how secure is our network, how secure is our data, is data secure at rest, is data secure in transition, who is accessing my data? All these are basic yet compelling queries businesses ask. In this age of competitive businesses, organizations are becoming tech savvy in building a secure fort for their critical data. Having worked on many projects in the past, these security measures are something I know are non-negotiable from an implementation point of view for administrators. When on this topic, I am reminded of a conversation about SQL Compliance Manager, I had with a junior DBA named Siva.
Siva: Hi Pinal!
Pinal: Hi Siva, how are you dude?
Siva: I am rocking and have been put onto a spot now from my internal auditing team.
Pinal: Wow, nice. Please shoot your question. Let me see what can be done.
Siva: Our internal auditing team wants to do auditing for our mission-critical sensitive database.
Pinal: Well, it’s easy and as simple as the question you asked.
Siva: Really, are you sure?
Pinal: Yes. Use the out-of-box capability of SQL Server auditing and it is something to play with too.
Siva: Oh, yeah. That is true. But there is a twist. They do want to log everything, but want to make sure our administration/maintenance activities are not logged per se.
Pinal: That is an interesting requirement for sure. But, it is not difficult either – trust me.
Siva: Now you are getting me interested. Please guide me.
Pinal: Well, start looking for filtered audit. Have you looked into it?
Siva: Oh yeah. I do need to look into it. Thanks for the pointer. If you get a chance, please write about these on your blog someday.
Pinal: Sure.
This conversation has been on my mind for quite some time and I have been wanting to write what Siva asked since then.
Simple Auditing Filtering with SQL Server
In the above conversation, I am making an assumption that the administrative tasks are done by the local administrator account like “sa.” The first step is to know the “principal id” of our administrator account. Use the following DMV to know the same:
SELECT name, principal_id FROM sys.server_principals
In our example, the “sa” account will have the principal_id of 1. So that is what we will use in our filter section of audit definition. The next is the audit definition we will create using the filter condition. A typical TSQL construct would look like:
CREATE SERVER AUDIT [Filtered-Audit-2014-10] TO FILE ( FILEPATH = N'C:\Temp\' ,MAXSIZE = 0 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) -- Change server_principal_id as needed WHERE ([server_principal_id]<>(1)) GO
This is an awesome capability introduced with SQL Server 2012 and is worth a mention today. Now we can go ahead and create a server audit for specific events. Once the audit is enabled, we can go ahead and attach our events to this using the TSQL like below:
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141020] FOR SERVER AUDIT [Filtered-Audit-2014-10] ADD (FAILED_LOGIN_GROUP) GO
In this example, we have gone ahead and created an audit for failed logins. The wish list was to automate for a broader auditing capability with SQL Server.
Advanced Auditing with SQL Compliance Manager
On the topic of auditing I would like to talk a little bit about SQL Compliance Manager from Idera. As much as we would like to customize and use the out-of-box T-SQL functionality, it is sometimes necessary to do the same functionality of auditing in an automated fashion. This is where SQL Compliance Manager comes into play. There are a number of things we can start auditing using this tool. Various configuration options you can do with this tool include:
- Logins
- Failed Logins
- Security Changes
- Database Definition
- Administrative Activities
- User Defined Events
In addition to these, we can audit based on filtered access or we can audit all the events by unchecking the checkbox too. I don’t think we can get an easier configurable option for auditing logins, DDL, admin tasks in such a user friendly format. This in my opinion is the fastest way to configure audits on important tasks.
If you are a savy DBA, then the chances are you can enable all these auditing capabilities using a similar T-SQL script. But this is where I felt there are nice additions to the tool that make it worth a second look.
I particularly want to call out the auditing threshold, which can be used to track abnormal activity and proactively alert a DBA of any suspicious activity that has been identified based on thresholds set by the organization around access, login failures, DDL activity and many more. In addition to those activities, there is also a laundry list of standard reports they generate after the data is collected. This list is pretty exhaustive to say the least.
The more I play with the product, there are more pleasant surprises with each additional screen.
Wrap up
As I wrap up, feel free to use the filtered auditing capability with SQL Server and explore how these can be controlled at a granular level. If you want a quick solution that will enable auditing, keeping security and compliance in mind, then it is worth looking into SQL Compliance too. It is worthwhile to trial SQL Compliance Manager for your environment. I still look forward for plugs to the current SQL Server enhancements like filtered auditing, audit resilience capabilities in the future. I am sure these are present in some shape or form today and may be explored.
Reference: Pinal Dave (https://blog.sqlauthority.com)
