asked
SQL Server monitoring for security, not performance
I work on the security side of SQL Server now, versus previously working on the support side of the fence. In that I ensure SQL Server instances and databases are secured (locked down) in accordance with DoD standards ([IASE STIG checklist][1]). In this checklist a few requirements are to monitor audit trail data and SQL error logs for unathorized access or attempted, frequent access by an unauthorized user. Then the occassional instance where we need to find out who changed something. Most instances are either utilizing C2 Auditing or the checklist contains a custom audit procedure that is set to start up when SQL Server service is started. This procedure just creates an audit trace that is less overhead of C2 Auditing, capturing the bare minimum of what is required. Then the defaul trace is enabled as well. I know vendors are out there that provide an enterprise wide solution for this, for PCI or HIPPA compliance reports. Are there any good ones that you can suggest. I would also like some type of utility (maybe like ClearTrace or SQLNexus) that I could use to maybe pull out the number of times an account logged on or accessed an object. I can think of ways to do this with PowerShell and the SQL ERRORLOG file, but would be nice if there was a utility to do this with the trace files or ERRORLOG file. ClearTrace and SQLNexus are utilities mainly for performance troubleshooting (finding which query or procedure gets called the most, etc) that I was aware of, could I use them for this too? [1]:
http://iase.disa.mil/stigs/app_security/database/sql.html
LogParser was built to interrogate (perhaps not surprisingly given its name) log files. I wrote a series of How To's on my blog (
http://www.simple-talk.com/community/blogs/jonathanallen/archive/2010/07/05/93370.aspx ). You can automate it to scan log files of all sorts and pump the data into a destination, doing aggregation on the way if you like. The help documentation is really detailed and you should be able to do what you need.
OOhhh I forgot about that one! I have messed with that one with IIS logs a few years back.
Well, give it a whirl and if you get stuck ping a question on here or my blog if its something I havent explained well enough.
We use an Idera tool called [SQL compliance manager](
http://www.idera.com/Products/SQL-Server/SQL-compliance-manager/) on our sensitive instances. It's very expensive (retails at $3K an instance) but it can be configured to work pretty well and creates some really pretty graphs. You can configure it to get down to the level of seeing all of the queries an individual ran which hit certain tables. One word of warning: if you have connection pooling, it's not going to give you much useful information for those accounts (though it will work just fine for anybody who logs into the server directly as opposed to via a pooled account). Neither will pretty much any other tool on the market, at least as far as I have seen. I was part of a state effort to find a good Database Activity Monitoring tool, and we found that even the industry leaders (like [IBM's Guardium](
http://www.guardium.com/), [Imperva's tools](
http://www.imperva.com/)) do not really handle pooled connections very well. They do some other things very well, and can alert you in real-time if people are acting outside their "normal" activity patterns. As far as connection pooled accounts go, however, they can tell you: a) that user Bob hit the application server at time T, b) that there was a connection to the database at T + x, and c) that there is probably a correlation between the two, but that's about as close as they can get. If you need to monitor who ran what in a pooled connection environment, it would probably be easier to build that into the business objects for the applications using the pooled account; at least that's the solution we came up with. If you look at a DAM solution, those will be significantly more expensive, but they're hardware/VM appliances so licensing isn't per-instance.
