Basics of SQL Server Auditing
In this page
- A brief history of server auditing
- Auditing components
Are you using SQL databases to store your critical data? How are you auditing? Do you think auditing database activities is not essential? Take a look at the following scenarios:
- A hospital that is liable to comply with HIPAA mandate holds patient health information in SQL server. On a fine day, they discover that their PHI (Protected Health Information) is compromised. How can they ascertain who leaked the information?
- A government agency that stores variety of PII (Personally Identifiable Information) in a SQL server is liable to comply with GDPR. If an insider leaks out sensitive information, there will be serious consequences. How can the security of the data be ensured?
-A user logs in to the network at an unusual time. Though this might look like a normal activity, it is important to know what the user was up to. How can this be done?
How can you analyze these incidents without enabling auditing for your SQL server. This article discusses different ways of auditing SQL servers and databases.
A brief history of server auditing
Prior to the development of SQL 2008, the following features were combined to facilitate auditing.
- Login auditing and C2 auditing
- Triggers and event notifications
- SQL Tracking used in conjunction with SQL Profiling
However, utilizing the above mentioned features for auditing purposes became quite difficult as it involved a significant amount of setup. The data collected by this method were logged and stored in different formats and different locations respectively. This made it difficult to comprehend information from the logs.
After 2008, though these features are still being used, SQL Server auditing, a new feature that utilizes extended events to audit everything that happens in your server was developed. This enables you to track every single event from server setting changes to who modified a setting in the database. This information is then written in the Windows security log, Windows application log or a flat file.
Though auditing was an enterprise only feature in SQL Server 2008, in SQL Server 2012, it has been made available in all the editions. However, database auditing is still available only in enterprise edition.
Auditing components
The SQL Server auditing feature encompasses three main components:
