This article will provide an overview of manually creating a SQL Server audit using SQL Server Extended events and triggers. It will provide an overview of SQL tracing/profiling as well as extended events. I’ll walk you through how to create a new extended events session and how to use it, in a worked example, to audit failed logins. Finally, we’ll touch on an auditing approach using triggers
If you are just joining with this article, not that in the previous article of this series, Implementing a manual SQL Server Audit, we went through the different methods that can be used to audit various types of actions performed at both the SQL Server instance and database levels.
SQL Trace
The SQL Trace feature was introduced the first time in SQL Server 2000, and considered as the best method of SQL Server auditing using different SQL Server actions. At the beginning, you need to define the classes of events that you manage to collect using a set of T-SQL system stored procedures. The defined events can be collected using the SQL Server Profiler tool, which cannot be used in the production environment due to performance purposes, or simply called inside your application to create these event traces manually.
These system T-SQL stored procedures include the sp_trace_create that is used to define the SQL Trace, sp_trace_setevent that is used to define the list of event traces to be collected and the columns that is retrieved, and the sp_trace_setstatus that can be used to start, stop and remove the SQL traces. SQL Traces are working temporarily, where the traces will be stopped, and not started automatically, when the SQL Server service is restarted. For more information about the SQL Trace feature, check out the SQL Trace document.
Both the SQL Trace feature and the SQL Server Profiler tool are deprecated and may be removed from future SQL Server versions, as the technology is replaced by the SQL Server Extended Events feature, that we will describe shortly in this article.
Extended Events
SQL Server Extended Events feature was introduced the first time in SQL Server 2008, as a light weight performance monitoring feature. And with the vital enhancements in SQL Server 2012, such as the SQL Server Extended Events graphical user interface that makes it easy to create and configure the Extended Events sessions without the need to go through the underlying architecture of its framework, it is considered as the best replacement for both the SQL Server Profiler and SQL Trace deprecated features.
SQL Server Extended Events are a highly scalable and configurable events framework that helps in collecting as much useful information as possible from the wide range of available actions, with the least possible SQL Server resources consumption, for troubleshooting and performance tuning purposes. For more information about the SQL Server Extended Events feature, check the Extended Events article.
Creating an extended event session
SQL Server Extended Events can be used also for SQL Server auditing purposes. For example, you can create a SQL Server Extended Events session that audits both the succeeded and failed login processes. To do that, expand the Extended Events option under the Management node, right-click on the Sessions option and choose New Session…, as below:
On the displayed New Session window, provide a meaningful name for the new session, which is Audit_Demo in our example, and set the appropriate scheduling settings from the available options, as shown below:
The New Extended Events Session wizards allows you to choose from the available default events templates, similar to the SQL Server Profiler templates, as shown below:
Or click on the Events tab, to customize your own session and choose the events that you are managed to monitor. In our example here, we will choose the Login event to track the successful login processes and the Error_Reported event to collect the failed logins as follows:
Double-clicking on the selected event will move you to a new window, on which you can customize the columns that will be recorded and received for that event. For example, we are interested in retrieving specific global information about the successful login process, as shown below:
Auditing for failed logins
For the failed login processes, we need to filter on the 18456 SQL Server error message, that is returned when a connection attempt is rejected because of an authentication failure that involves a bad password or user name. This can be performed by choosing the Filter tab and specify a filter for the error_number field, to retrieve only the error with number 18456, as shown below:
The location where the SQL Server auditing session result will be written can be specified from the Data Storage tab, by choosing the type of output target and configure its settings as follows:
For example, you can choose the target as a SQL Server Extended Events event_file, with XEL extension, then configure its location and properties, as bellow:
In the Advanced tab, you can configure the retention and resources settings for the SQL Server Extended Events session. In our example, we will keep the default values, as shown below:
Once the SQL Server Extended Events session created, a new empty window will be displayed in the SQL Server Management Studio, in which the caught events will be displayed, as follows:
If this page is not displayed, right-click on that session and choose the Watch Live Data option, as below:
After performing successful and failed login processes, the events will be collected and displayed by the SQL Server Extended Events session. For example, the successful login process properties, including the user name, the host name, the application used for the login and other useful information will be displayed as shown clearly below:
On the other hand, all useful information about the error message generated when the login attempts to fail, will be caught and displayed in the SQL Server Extended Events session, as shown below:
In addition to writing the logs to the event file for future analysis, as below:
Managing sessions
To start or stop the created session, browse for that session under the Extended Event Sessions, and choose the Start Session or Stop Session, as shown below:
SQL Server Extended Events cannot be used to design a complete database auditing solution. Although it is very useful in auditing the successful and failed logins processes, as shown in the previous example, this feature still limited in terms of auditing the different database DML changes and comparing the values before and after the modification process, that can be easily performed in the SQL Server auditing mechanisms discussed later in this article and the next articles of this series.
SQL Server Triggers
SQL Server triggers are special type of procedures that are automatically fired when an event occurs at the SQL Server database. There are two types of triggers, the DML triggers that are executed as a result of a data modification or insertion operation. The DDL trigger is executed as a response to a data definition operation, such as CREATE, ALTER or DROP statement. The response of the trigger to the different actions can be in the form of another statement that will be executed after the current action, or a statement that will be executed instead of the firing action. For more information about the SQL Server triggers, check the CREATE TRIGGER article.
SQL Server triggers can be used to track and audit large number of database operations. This is due to the fact that, the triggers are T-SQL scripts that can be customized to build your own SQL Server auditing solution that fits your systems, based on your development skills. You can create at least one trigger on each table that contains critical data to audit the modified or inserted data and compare the data before and after the modification. You can also design a proactive SQL Server auditing system using a trigger that prevents the changes on a specific table and instead of performing that change, it will audit the failed action to a data repository.
Assume that we need to prevent any new insertion to the Employees table and audit these failed operations using SQL Server trigger. We will start with creating the CompanyEmployees table, and fill it with 100 records, using the script below:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE CompanyEmployees ( ID INT IDENTITY (1,1) PRIMARY KEY, Emp_Name NVARCHAR(50), Emp_BirthDatae Datetime, Emp_Salary INT, Emp_Address NVARCHAR (MAX) ) INSERT INTO CompanyEmployees VALUES ('ALI','1988-08-15',850,'AAAAAABBB') GO 50 INSERT INTO CompanyEmployees VALUES ('Zaid','1988-06-10',730,'CCCCCDDD') GO 50 |
After creating the table, we will create the SQL Server audit repository table, where the employees data will be inserted, instead of the main Employees table, in addition to the name of the user who tried to insert the data and the insertion time. The table can be created using the T-SQL script below:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Emp_AUDIT_Table ( Emp_ID INT , Emp_Name NVARCHAR(50), Emp_BirthDatae datetime, Emp_Salary INT, Emp_Address NVARCHAR (MAX), WhoInserted NVARCHAR(128), WhenInserted datetime ) |
Once the audit table is ready, we will create the INSTEAD OF INSERT trigger to prevent the new insertions, using the CREATE TRIGGER script below:
|
1 2 3 4 5 6 7 |
CREATE TRIGGER AuditEmployees ON CompanyEmployees INSTEAD OF INSERT AS BEGIN INSERT INTO Emp_AUDIT_Table SELECT i.ID, i.Emp_Name, i.Emp_BirthDatae, i.Emp_Salary,i.Emp_Address, SUSER_SNAME(), getdate() FROM inserted i END GO |
If you try to insert a new record to the CompanyEmployees table, it will show you that two rows will be affected, as below:
But internally, the first affected row is the failed inserting process, as no record will be inserted to the main table. And the second affected row is the audit row that will be written to the SQL Server audit table, with full information about the user and the time of insertion, as shown clearly below:
This is a simple example of how we can take benefits from the triggers in auditing the data and schema changes. And it is yours now to build your customized code based on your development skills for SQL Server audit purposes.
Third party tools like ApexSQL Trigger, can help you rapidly design and automatically maintain a trigger-based, SQL Server audit solution.
Summary
Building an effective SQL Server auditing system using triggers is difficult and will add more complexity to the database design. In addition, it is not recommended to create the SQL Server triggers in a heavily transaction tables, as it will be executed each time a data insertion or modification process is performed, adding extra time and resources overhead to different SQL Server queries and transactions, and leading to a major performance issues on these tables.
In the next article, we will discuss a more effective way to create a SQL Server audit by reading SQL Server Transaction Log records. Stay tuned!
Table of contents
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
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021