Introduction to MS SQL Server Auditing
|
|
| The steady increase of heightened security requirements, laws for accountability, risk control and regulatory compliance demands make Microsoft's SQL Server database an important subject for auditing. Even though it doesn't actually block breaches in security, auditing, like CCTV, is a powerful tool for investigating events and getting facts about activity that has taken place. In our case, activity on your MS SQL Server database system. |
|
What kind of information is available in the Audit?
|
|
|
Lots of different kinds of activity take place on a DBM like SQL Server. The actions are classified into action groups including administration activity, such as starting and shutting down the server, backup and recovery operations, security actions such as granting and revoking permissions, and access to the data itself in the form of SQL statements for reading, updating and deleting records.
|
|
Setting Up a Policy for SQL Server Auditing
|
|
|
Your audit policy is the deciding factor regarding what you are going to find in your audit. If you define everything you will be covered for every auditable event but may notice a drop in database performance. If you are too selective, you might find the information you need is missing just when you need it. Then there is the consideration of SQL Server audit log size. A policy that logs a large number of events will give you a detailed audit trail but also means rapid log growth, more disk usage and more frequent maintenance.
|
|
SQL Server Auditing Tools
|
|
|
The Bsafe CPA or Cross Platform audit is an independent software tool for MS SQL Server auditing. It is built to provide administrators and auditors with an easy to use way of setting up the Microsoft SQL Server audit policy and producing audit reports. For the administrator's convenience, the product divides audit policy definition into three, sub-policies: A system audit policy, an SQL statements audit policy and a data audit policy.
|
|
Trace File Size
|
|
|
The size of the SQL server audit trace files - the physical files holding the logged audit data - and the frequency of the rollover, or replacement, cycle is an important consideration in optimizing SQL Server auditing performance. The cross platform audit allows you to set the maximum file size before rolling over and creating a new file, and the number of new ‘rollover' files to be created on your system before overwriting the oldest.
|
|
System audit policy
|
|
|
System audit policy consists of seven event groups: Successful logins, failed logins, object creation, object deletion, security management, database management and backup restore. Additionally, you can select the users to be included in the audit. The screen below shows the system event audit groups that can be included.
|
|
SQL Statements Audit Policy
|
|
|
The SQL statements audit policy allows selection of any of 5 groups - select, insert, update, delete and execute - in the overall SQL Server auditing policy, and further qualifies the tracking activity for specific users, databases and applications.
|
|
Data Audit Policy
|
|
|
The data audit policy is a way to specify tables to be monitored for changes in data. Each table can be monitored for record insertion, update and deletion in a way that the actual field values changed will be available to the auditor.
|
|
And Finally, Auditing the Logged SQL Server Events
|
|
|
Once the policy has been set up and the activity is logged, the events are available for viewing and the task of SQL Server auditing can begin. The same logical split used in the policy definitions is seen in the on-line reports - an SQL statement audit, a system audit and a data audit. They are listed individually and have filtering options on date, time, user, database, table, category and event. The audit data can be imported into the consolidated central data repository where events can be correlated with events from other MS SQL server instances and with events from other platforms.
|
|
|
|
