Monitoring (auditing) a SQL Server database does not only serve to comply with the requirements of the compliance rules. It is a practice that has become necessary for analyzing database actions, solving problems, investigating suspicious or fraudulent activities. It can also be useful to prevent inappropriate actions by users, as if there were a closed circuit TV system on its databases.
There are different auditing techniques for SQL Server:
- Manual Auditing – can be created to meet specific requirements, but implementation takes a significant amount of time and can easily lead to errors
- Using Extended Events in SQL Server – easy to set up, you can monitor a wide range of actions, but it does not provide information about what has been deleted / inserted nor the old and new values for updates; moreover, detailed auditing can cause performance problems
- Use SQL Server triggers – easy to set up but can cause performance problems for databases with a large number of transactions
- Read the transaction log – it is not required to capture additional data because SQL Server already tracks the changes. Provide additional memory space and keep in mind that some actions (such as EXECUTEs) are not recorded
- Use SQL Server Profiler and SQL Server traces – flexible and complex. It has some difficulty in reading and filtering the records
Identifying the most correct solution depends on your environment, what you need to observe, where you plan to record the captured actions and the way you intend to represent the data.
Manual auditing
A SQL Server database can be put under observation using ad hoc stored procedures and functions for tracking changes on data and objects. This provides a flexible solution which, however, involves a great deal of writing and code development, thus increasing the cost and the implementation period.
Use SQL Server Extended Events
SQL Server Audit is a feature introduced by the 2008 version that uses Extended Events to monitor SQL Server actions. It allows different verification actions, providing many details during the setup process and covering a wide range of activities in SQL Server.
To create a new Audit object in SQL Server:
- In SQL Server Management Studio, expand Security and right click on Audits
- Select New Audit
- Specify in the name of the audit, choose whether to save the data in the application event log, security event log or file and then set a destination for the audit file.
- Click OK and your audit will appear in the Audits node of the Management Studio Object Explorer
- By default the audit is disabled and for this it is shown with a red arrow. To enable it, right click and select Enable Audit.
- Choose between Server Audit Specification or Database Audit Specification depending on whether you want to monitor activity at the SQL Server instance or database level.
- To create a Database Audit Specification, expand the database node you want to monitor, go to Security click with the right to Database Audit Specification and choose New Database Audit
- In the Create Database Audit Specification dialog box, specify a name, associate the specification with the audit object created in step # 1, and specify the activities to be verified in the Audit Action Type. For a database auditing, it indicates the database, the object or schema as Object Class, the name of the audit object and the login
You can see all the activities to be monitored with SQL Server Auditing in the drop-down menu for Audit Action Type
The Principals to be selected in this dialog box are actually the users that will be monitored
As with audits, the Database Audit Specification is disabled by default. To enable them, select this option in the context menu.