SQL Server 2008 helped to alleviate the need for DBAs to write DDL triggers or to use traces to audit SQL Server by adding the “Server Audit” capability. However, to have a complete audit solution that can be used by auditors or folks outside of the SQL Server administrator teams, you need a tool to format the audit information. It’s not reasonable to test for compliance or to manually look through complex audit logs.
There are a number of very good products on the market today that you can purchase that are meant for compliance standards and auditing of entire ecosystems. But if you would like to take the SQL Audit capability at least a step higher to where the SQL Server audit data from the audit objects are parsed out of logs and into nicely formatted reports and stored in a database that you can manage, search and monitor, then I suggest taking a look at a really cool sample solution.
This is something that the Microsoft SQL CAT team put together with consultants and a customer that will not do all the things a true auditing and compliance product will do. But it will take you a step beyond the current SQL Server Audit object which today stops at writing to a log file or a Windows log. For everything that I’m showing you in this posting, you can download it all and the complete sample application here.
Once you download that material, you will expand it out to a series of folders. You’ll find the full complete set of instructions on how to set-up & configure the complete solution from the Word document called “SQL Audit Solution” in the \SQLComplianceLab\SQLAudit folder. There are SSIS packages, SSRS reporting projects, the Audit database and scripts and even hands-on labs and policy-based management samples. Pretty cool stuff.
The details, requirements and relevance to compliance standards that this audit solution strives to meet are explained very well in this whitepaper on Reaching Compliance with SQL Server 2008. You will see quite a few references to one of Microsoft’s biggest SQL Server customers, CareGroup, because I’m sure that this is where the Microsoft partner and SQL Server CAT team first implemented with solution. Be sure to replace all things related to CareGroup and their file system (you’ll see a log of references to E drive and C drive folders that aren’t relevant to your servers). When going through this material and implementing this SQL Server Audit solution in your environment, I think you’ll agree that Denny Lee and the partner teams did an awesome job with this and it is great to see that they’ve shared the entire solution with everyone at no cost.
The diagram below is a very simplified high-level look at what the solution will do for you as an end-to-end auditing application. There are sample best-practice policies and security objects that you can use from the documentation and install. Once your SQL Server starts writing the audit logs, you can schedule the ETL process from SSIS (see below) to parse the audit logs and store it in the partitioned tables in the SQL Server Audit tables that are included in the download from above.
A few things to focus on when setting up the reports in your SSRS report server (see below) and your SSIS packages for ETL (directly below) based on my efforts in setting this solution up on my systems include:
- In my BIDS screenshot below of the SSIS package, I have the variables window open. Go through and make sure that you are pointing to your Server Audit logs folder and that you’ve removed any references to CareGroup that still exist in some of the code from the implementation at the customer site
- Do the same scan and scrub of the SSRS data sources in BIDS and make sure you are pointing to your SQL Servers and audit database that came with the install. You’ll need to do this on the source & target adapters in SSIS, too
- Run the ETL from BIDS a few times so that it is easier to troubleshoot and find errors before deploying to SSIS and scheduling. It took me a couple of runs to get the variables set right in the packages. Most of it is actually pretty well documented. But a few things took 1 or 2 tries to get right
Ok. So you’ve gone through the trouble of downloading the material and the scripts. You’ve installed the audit database, configured the SSIS packages, modified the connection strings in SSRS for the reports … Now, what do I get for all of my work & efforts??
See below … I’ve taken just a few of the report screenshots from the document in the Reaching Compliance whitepaper from SQL CAT. Now, this is not going to be a robust, supported solution for auditing in the same vein as off-the-shelf solutions that are sold by Microsoft (System Center Audit Collection Services) or 3rd party auditing tools that you can find in the market today by companies like Lumigent and Idera. You are essentially implementing custom code and are on your own to configure and support. You can always hire a consultant to provide help and support on a solution like this. And like I said earlier, this is much more robust and accurate that wiring together a bunch of triggers or manually scanning log tables and files. Just be aware of the risks of implementing something that is not a supported product.