x

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

half fast dba gravatar image

half fast dba
152 8 9 10

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.5k 19 21 74

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

http://www.red-gate.com/products/sql-development/sql-compare/learn-more/audit-trail

http://www.lepide.com/sql-server-audit/

more ▼

answered Apr 15, 2013 at 12:42 PM

denisrichi gravatar image

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

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:

x43

asked: Apr 18, 2012 at 02:23 PM

Seen: 1313 times

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