asked
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!
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.
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.
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........
Many Thanks for the replies. They are all sincerely appreciated
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:
5 People are following this question.
