Ahmad Yaseen

Implementing a manual SQL Server Audit

January 25, 2019 by

A SQL Server audit can be performed using various number of methods. This includes using the built-in SQL Server Audit feature, using third party tools from the SQL Server market or simply perform the audit task using the legacy methods manually. In the previous article of this series, SQL Server audit overview, we discussed the main concept of a SQL Server audit, the importance of auditing the SQL Server instance changes and the SQL server audit checklists. In this article, we will go through the manual procedures for auditing the SQL Server instance.

SQL Error Logs and C2 Trace files

The SQL Server Engine writes number of useful events that can be used to audit the SQL Server instance. For example, the failed logins are audited by default and written to the SQL Server error logs. You can edit both successful and failed logins by changing the default configuration, from the Security tab of the SQL Server instance properties window, as below:

You can easily read the SQL Server error log using SQL Server Management Studio with the customizable query below:

And the result in our case will be as follows:

In SQL Server, you can also enable the C2 auditing feature, available since SQL Server 2000, to for a SQL Server audit of all instance and database level activities performed on that instance. This includes the execution of any DDL or DML query and login or logout activities. A C2 SQL Server audit can be enabled by checking the C2 Audit check box from the Security tab of the SQL Server instance properties window then restart the SQL Server service, as shown below:

There are two limitations of the C2 Auditing feature. One is that there is no option to choose what to audit, as it audits everything, and two, there is an issue of where to save the generated TRC files, because they will be automatically saved into the default DATA folder of the installed instance. It is recommended to keep an eye on the generated files, where a new trace file will be created when the size of the current file reaches 200MB. If there is no purging task configured to control these trace files, the database folder may run out of free space.

To perform a SQL Server audit by reading the generated TRC files is a time consuming process, as you need to go through all files generated during the period of time you plan to audit, in order to find the activity, you are interested in. You can imagine the time and effort spent checking the trace files generated from a heavy transactional system.

Ad Hoc Queries

Each database administrator has a secret library of scripts, that helps them in administrating and monitoring the different aspects of a SQL Server instance. This includes queries used to monitor SQL Server performance and track the activities and changes performed at both the SQL Server and database levels for auditing purposes. The same approach can be used for a SQL Server audit.

For example, you can run the below query to check all the databases properties on the SQL Server instance, and compare it with your baselines to make sure if there is any change performed on it:

And the result will be as follows:

Another example of an ad hoc SQL Server audit query is tracking the changes performed on the database stored procedures. The below query can be used to check the creation date and last modified date for each stored procedure under the selected database:

The members of each database role, which is an important part of the SQL Server audit process, can be also listed using the query below:

And the result will be as follows:

Triggers also should be included in your SQL Server audit in order to monitor if any change is performed on the data before or after any DML or DDL operation and if the data is copied to another place. This can be easily done by listing all existing triggers with its properties, using the script below:

The result in our case will be as follows:

At the SQL Server instance level, you can audit different aspects using T-SQL queries. For example, the last restart for the SQL Server service can be retrieved using the simple query below:

And the result will be as follows:

The opened sessions on the SQL Server instance can be also listed using the query below:

The result in our case will be as follows:

If you are interested in including the TCP port and the IP address of the active connected sessions in your SQL Server audit, the below query will help:

With no result in my scenario, as below:

The below query also can be used to audit the changes in the SQL Server default configurations:

And the list of changed configurations will be as follows:

You can see that SQL Server provides us with large number of ad hoc queries that can be used for SQL Server instance level and database level audit purposes. Auditing the SQL Server instances manually by executing the T-SQL ad ho queries is a time-consuming auditing process and not easy to cover all possible auditing events. In addition, you can automate that auditing method by combining it with other reporting tools, or simply the SQL Server Reporting Service, to receive the auditing reports periodically and automatically without the need to run it manually by the database administrator. For low budget auditing projects, it is a good option.

SQL Profiler

SQL Server Profiler can be used to monitor the performance of the queries and tune them to enhance query performance. In addition, it can be used to audit the different activities at the SQL Server instance and database levels. With the large variety of events, properties and filters available in the SQL Profiler, you can easily use it to audit who is logging to the SQL Server, from which machine and what activities done by a specific user:

For example, to audit the events of adding the server logins and the database users on a SQL Server instance, you can edit the events of an existing template, or add a new template that you can use it directly for SQL Server audit purposes. To add a new template, choose the New Template option from the Templates list of the File menu, as shown below:

In the General tab of the New Template, choose the type of the SQL Server on which this template applies to and the name of the customized name, as below:

In the Events Selection tab, choose the events that you are interested in and the retrieved properties for each event. In our example, we will audit the events related to adding a sever login, database user or role, under the Security Audit events category. To create the template, click on the Save button, as shown below:

To use the new template for auditing the server logins and database users’ changes, open a new tracing session on the SQL Profiler, and select the new template from the available templates list, as shown below:

From the Events Selection tab, you can review the default events and properties of that custom template, and add a filter to trace a specific database, server or user. Click on the Run button to start the session, as shown below:

On SQL Server Management Studio, run the below query that creates a new server login, add the login to an existing server role, create a database user for that login and grant it permission on a specific database, as below:

On the SQL Profiler trace, you will see that all these events are captured in the opened session, as shown below:

Auditing the SQL Server instance using the SQL Profiler is a great option, especially when the events and properties are selected properly and filtered to collect the requested information only. This will help minimizing the size of generated trace file and making it easier to analyze the events. The trace also can be written to a specified database table for future analysis.

SQL Server Profiler cannot be considered as a long-term auditing solution though, as it will be eventually deprecated by Microsoft. But the happy news is that it can be easily replaced by a most powerful feature, called Extended Events that will be covered in the next article.

Table of contents

SQL Server Audit Overview
Implementing a manual SQL Server Audit
Creating a SQL Server audit using SQL Server Extended Events and Triggers
Auditing by Reading the SQL Server Transaction Log
Change Data Capture for auditing SQL Server
Creating a SQL Server audit using SQL Server Change Tracking
SQL Server Audit Feature Components
Using the SQL Server Audit Feature to Audit Different Actions
Performing a SQL Server Audit using System-Versioned Temporal Tables
Perform a SQL Server Audit using ApexSQL Audit
SQL Server Auditing Best Practices
Ahmad Yaseen
Latest posts by Ahmad Yaseen (see all)

About Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields. He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer. Also, he is contributing with his SQL tips in many blogs. View all posts by Ahmad Yaseen