Bullet Proof SQL Server 2008 Audit

The enlightened bean counters half fast inc. have bought a new HR app that has a SQL Server back end. Sick leave data, pay and other sensitive info is stored on the database. The app does not encrypt the data. Encryption on SQL Server does not work as the DBA team can always access the data thanks to their SA privileges.

The only option open to me is auditing access to that database.

I have read the white paper at http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx and have been experimenting with Audit. I found that I could create a SQL user, grant it R/W access, impersonate it and read the data unnoticed.

Can anybody advise me on an audit strategy that could not be circumvented by the DBA under any corcumstances? Alternately any other novel or encryption solution to my problem would be very welcome!

more ▼

asked Apr 18, 2012 at 02:23 PM in Default

avatar image

half fast dba
248 9 13 19

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

2 answers: sort voted first

To get as complete a record as possible of what occurs on the server, I'd use extended events. You should be able to capture every query that runs on the system and it won't place much load on it at all. The pertinent events are rpc_complete for procedures and parameterized queries through code, and sql_batch_complete for SQL batches, such as those run through SSMS.

But, the main issue is that giving someone 'sa' privs enables them to turn off anything on the system, so any auditing solution can be turned off by them. In short, the best approach is to limit access. For example, at my previous employer, all HR data was managed through a single PeopleSoft instance. Only one DBA, out of 15, had access to the system, and that DBA was a trusted resource for the company. In general, if you can't trust your DBA, you should hire another.

more ▼

answered Apr 18, 2012 at 02:41 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

Auditing is a good solution because you can write the results to the security log and audit changes such as switching it on and off are captured as well. My problem is securing the audit within reasonable limits to ensure that somebody will have work hard to get around it.

Unfortunately we cannot put the database on a single server with one DBA as we use clustered servers with identical set ups that all DBAs have access to. Anything else becomes an administrative nightmare.

As for trusting a DBA we are mere mortals and infintely curious as well........

Apr 18, 2012 at 03:12 PM half fast dba

If the business sees a risk of the data being accessible but decides that any/all options to audit access and reduce "attack vectors"( or "surface area" or what ever other phrase is popular) are dismissed as too difficult to implement or too expensive or too much hassle to support then they are accepting the risk as they first identified it. They have evaluated the risk against the cost of prevention and that is their business decision.

There is only so far a DBA can go in advising of options and benefits/downfalls of each. At the end of the day, if you have done what is required and they make a poor decision in full light of the facts then it is their position to accept consequences.

Apr 18, 2012 at 07:14 PM Fatherjack ♦♦

Many Thanks for the replies. They are all sincerely appreciated

Jun 01, 2012 at 02:31 PM half fast dba
(comments are locked)
10|1200 characters needed characters left

Auditing & monitoring with independent vendors would also be the safe option if you want to monitor every activity happening in SQL server; these solutions will provide you the report in various format also you can set the alerts for the same. check the following solutions



more ▼

answered Apr 15, 2013 at 12:42 PM

avatar image

160 2

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Apr 18, 2012 at 02:23 PM

Seen: 1671 times

Last Updated: Apr 15, 2013 at 12:42 PM

Copyright 2018 Redgate Software. Privacy Policy