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!
asked Apr 18, 2012 at 02:23 PM in Default
half fast dba
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.
answered Apr 18, 2012 at 02:41 PM
Grant Fritchey ♦♦
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
answered Apr 15, 2013 at 12:42 PM