x

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
more ▼

asked Apr 14, 2011 at 01:23 PM in Default

Shawn_Melton gravatar image

Shawn_Melton
5.3k 19 21 29

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
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.
more ▼

answered Apr 15, 2011 at 01:43 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.3k 74 78 108

OOhhh I forgot about that one! I have messed with that one with IIS logs a few years back.
Apr 15, 2011 at 05:14 AM Shawn_Melton
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.
Apr 15, 2011 at 05:19 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

We use an Idera tool called 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, Imperva's tools) 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.
more ▼

answered Apr 15, 2011 at 03:56 AM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x161
x43
x18
x8

asked: Apr 14, 2011 at 01:23 PM

Seen: 2087 times

Last Updated: Apr 14, 2011 at 01:23 PM