SQL Server auditing best practices: 3 key questions for DBAs
Acing a SQL Server database audit starts with careful monitoring of how sensitive data is accessed and used so you can answer the top questions that auditors ask.
A compliance audit can sometimes feel like a database administrator's worst nightmare, but it doesn't have to be. The key to acing a SQL Server database audit is preparedness; planning ahead and carefully monitoring the use of your data from the get-go can take some of the pain out of the auditing process.
That's according to Bullett Manale, vice president of sales engineering at Idera. In a webinar hosted by the database tools vendor, Manale discussed SQL Server auditing best practices -- in particular, what the auditors really want to know and how to best answer their questions.
According to Manale, auditors typically have three main questions for database administrators (DBAs):
Is the audit trail that's being used to validate the access controls reliable?
To avoid problems during compliance-related database audits, SQL Server DBAs should be able to answer all of these questions, said Manale, who addressed them individually in more detail.
Tracking access to sensitive SQL Server data
At the outset of a compliance audit, the auditors will want to know who has access to sensitive data and -- more importantly -- how they gained that access, Manale said. This really boils down to knowing who should and shouldn't have access to the data, he added.
Information that SQL Server DBAs need for database audits
Knowing exactly where relevant data resides in a database is the key to determining who has access to it, according to Manale. As part of SQL Server auditing best practices, DBAs need to understand how user permissions are applied to the parts of databases where sensitive data is stored, he said.
Tools that can help are available -- Manale noted that they enable DBAs to isolate and identify users, as well as their rights and permissions, painting a complete picture of who has access to what in a database.
Before moving onto their second question, auditors typically will need to know that all this information has been validated by an organization's compliance officer, Manale said.
Data access for the right people only
Even if the audit trail shows compliance, many auditors will not trust the information without some form of integrity check of the audit itself.
Bullett Manalevice president of sales engineering, Idera
Auditors also want to make sure that workers are utilizing sensitive data properly. In order to do so, Manale said, they'll ask for proof that the data is only being accessed by the right people -- qualified individuals whose job requires the data in question. It's equally important to be able to prove that the data can't be accessed by the wrong people, he added.
To assure auditors that the data access situation is under control, SQL Server auditing best practices include being able to provide detailed records and reports on when data was accessed and by whom. Manale said the main challenge with that for DBAs is not only keeping track of the who, what, when and where of every transaction related to particular data sets, but also maintaining those records and being able to pull them up as needed.
A complete and accurate audit trail
Finally, auditors will want to ensure that the audit trail being used to validate the data access controls hasn't been tampered with. In order to prove that, DBAs need to be able to show that the audit data hasn't changed, Manale said.
This can be done using hash functions or cyclic redundancy checks, but Manale cautioned that it can be an especially complicated task because the integrity of the data in a database is essential to passing a SQL Server audit. "Even if the audit trail shows compliance, many auditors will not trust the information without some form of integrity check of the audit itself," he said.
To adhere to SQL Server auditing best practices, Manale recommended that DBAs collect and maintain a full audit trail for transactions relating to the sensitive data in a database. Data should be kept in accordance with a standard data retention policy -- typically, for a minimum of seven years, he said. He added that real-time controls should also be put in place to alert DBAs when transactions don't meet corporate data usage standards so they can put a stop to activity that could create compliance risks.
