Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Pass Server Security Audits Easily with SQL Compliance Manager


By:   |   Read Comments   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

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?

Solution

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:

sql compliance manager install

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.

sql compliance manager deployment

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.

sql compliance manager audited activities

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.

sql compliance manager privileged users

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.

sql compliance manager audit thresholds

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.

sql compliance manager select databases to audit

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.

sql compliance manager collection level

Once you've completed the wizard, you'll see the added databases. The database IShouldNotBeHere has been added to the list.

sql compliance manager audited databases

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:

sql compliance manager audited activities

What to filter on:

sql compliance manager audid properties

If you require monitoring the changing data, that's possible too, but CLR is required.

sql compliance manager clr audit

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.

sql compliance manager audit properties

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:

sql compliance manager regulation guidelines

When you select a control, you'll get information about what the control is for and what the expectations are.

sql compliance manager pci

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.

sql compliance manager regulation guidelines

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.

sql compliance manager report card

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.

sql compliance manager configure email

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.

sql compliance manager alert rules

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.

sql compliance manager email alert

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.

sql compliance manager report card

You can also see the alerts. This is handy if you just need to check in after seeing an email.

sql compliance manager alerts

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.

sql compliance manager event properties

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.

sql compliance manager reports

Some reports, however, do require the console itself. For instance, the permission denied report is console only.

sql compliance manager reports

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.

sql compliance manager export

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.

Concluding Thoughts

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.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools