By: K. Brian Kelley | Updated: 2017-01-17 | Comments | Related: More > Auditing and Compliance
I have been asked to provide better visibility into what's going on with my SQL Servers. Some of the things my auditors and management are interested in are:
- Security changes
- Object changes
- When users access sensitive tables and columns
Trying to build a home-grown approach is doable, but it seems like a monumental effort. Is there a commercial solution already available?
Yes, a home-grown solutions is doable. However, any satisfactory solution requires a significant amount of set up and coding, all of which requires time. Time is what we tend to be short of in IT, so it makes sense to look in the commercial space. As it so happens, IDERA has a product that meets this requirements: SQL Compliance Manager (CM hereafter). Let's look at some of the things it can do. (Note: some of the images have been masked to hide sensitive data.)
Auditing a Server
CM provides an easy GUI to add a server to monitoring. First, you specify a server to monitor:
CM requires an agent to monitor the server, so it'll require an installation. There are several options to do so, allowing you to set up the agent at a convenient time.
There are a couple of more screens to indicate what database to audit and what level of auditing, but we'll cover those options in more details throughout the review.
Auditing a Server
As you'd expect with an auditing product, you have a lot of options on what to capture. Regardless of what you set when you initially registered the server, you can always go back and make changes as needed. Here are the activities you can audit at the server level. I've captured the default settings without any regulatory compliance requirements.
I should point out that while CM audits the events, it won't alert you that an event has occurred unless you setup the alerts. More on that later in the review.
You can also audit the events of particular (privileged) users and roles. For my auditing, I've told CM I care about what System Administrators and Security Administrators role members do on this particular SQL Server. The audited activities shown below are the defaults. If needed, I could also capture the SELECT and DML operations these users execute. In highly secure systems you might do that, but otherwise you'll probably stick with the defaults.
Since we do have activities being monitored, we can configure thresholds. In this case, I've configured the DDL events to flag at just 1 per hour. Yes, this is artificially low, but I've configured it to show some of the alerting later on.
Auditing a Database
When you set up a server for monitoring, you can select the databases to monitor at the same time. You have to set up at least one. However, you can always come back to a monitored server and add or remove databases to be monitored. Here I add a brand new database to a monitored server.
When you set up a monitored database, you can also set up what level of auditing you need. You can customize the auditing to what you need or take advantage of preconfigured settings to cover regulatory requirements.
Once you've completed the wizard, you'll see the added databases. The database IShouldNotBeHere has been added to the list.
If you need to make changes at a later time, this is easily done. Right-click on the database and choose Properties. Then you can see and adjust what is being audited.
For instance, what events:
What to filter on:
If you require monitoring the changing data, that's possible too, but CLR is required.
And you can set up specific auditing (and events) for sensitive columns, such as those for tax ID numbers and the like. Here I'm auditing the whole customer table.
I've hit on the most common options, but there are other tabs providing additional choices. For instance, you can filter out particular privileged accounts so that they don't generate events. This can be useful if you need to filter out a service account or the like.
Implementing PCI or HIPAA Level Controls
What if you have to meet a regulatory requirement? What options do you select? CM simplifies this for you by giving options for both PCI and HIPAA and then choosing what to monitor automatically.
Starting the process, the initial wizard screen lets you select one or both regulatory controls:
When you select a control, you'll get information about what the control is for and what the expectations are.
Proceeding further, CM will tell you what activities it's going to toggle on. As noted on the previous screen, this will overwrite existing settings, if necessary. However, meeting compliance does require some work on your part. There's additional configuration that's required, as you might expect. CM tells you what to configure in the next few screens.
Once you've completed the remainder of the audit settings, you should be in good shape for whichever regulatory auditing requirement you have to meet.
Alerting on Events
Collecting all this information is great. However, we need alerting, both visually and via email. CM provides this capability. If you recall, I set the DDL alerting down to 1 event for both warning and critical levels and then enabled the alerts. If we look at the CM console, we can see that the DDL event is marked with an error icon, telling me a critical threshold has been crossed during the monitoring period.
However, we're not going to be able to look at the console all the time. Therefore, CM gives us the ability to configure alerts via SMTP (email) and SNMP. Let's look at SMTP. You'll need to specify a server and, if necessary, specify the username/password to connect.
After that, you'll need to configure the alerts themselves. Here's an alert for whenever anyone queries the Customer table in the AdventureWorksLT2012 database. Note that it sends an email to the DBAs.
And sure enough, when DemoUser queried the Customer table, an email was generated. There's not much detail in the email alert, just enough to let you know there's an issue which needs investigating. To see exactly what was done, you'll either need to log on through the Console or use the web-based dashboard, which I'll cover next.
Web Dashboard to Minimize Installations on Workstations
Installing a client on everyone's system who needs access to the compliance information can be tedious, especially when dealing with folks who only need to see the information once in a while. CM does interface with IDERA's Dashboard to provide most of the information and capability from a web browser.
The dashboard gives a quick view of what's been happening. For instance, it'll give you charting on the events that have been recorded over a period of time.
You can also see the alerts. This is handy if you just need to check in after seeing an email.
You can also see exactly what led to the event being recorded. Here's the interface the web dashboard provides. I can see when the event happened, who did it, and even the SQL statement that was executed.
Audit and Change Reports
Part of any audit and compliance solution is reporting. CM has its share of reports. Some are available from the web dashboard. Here's a report of all user activity for a particular user.
Some reports, however, do require the console itself. For instance, the permission denied report is console only.
Not only does the console app have more reports, it also allows you to export the reports into one of two formats:
- Adobe Acrobat Reader PDF
- Microsoft Excel
Here's an exported report of the events for login creation.
In this particular case, since it was for a review, I was able to change the identifying information (<SQL Server>, <DBA>) before snapping a screenshot. This allows you to do the same thing as needed or to be able to use the information for tracking or response purposes, which is a common activity with audit and compliance.
I was impressed with how quickly I was able to set up auditing using SQL Compliance Manager. While I've built scripts and traces before to collect the same data, one of the issues with such an approach has always been how to present that information. CM handles this problem via the console app, a web dashboard, and with the ability to export reports for other parties' use. Also, setting up alerts for key events was simple and easy; certainly faster than trying to implement a home grown solution. Therefore, if you need a robust audit and compliance solution for SQL Server, I recommend you give IDERA's SQL Compliance Manager a good look.
Last Updated: 2017-01-17
About the author
View all my tips