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:
|
1 |
EXEC sp_readerrorlog 0, 1, 'Login failed' |
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:
|
1 2 3 4 5 |
USE master GO SELECT * FROM sys.databases ; GO |
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:
|
1 2 3 4 |
SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'P' GO |
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:
|
1 2 |
SELECT USER_NAME(memberuid) As DatabaseUserName, USER_NAME(groupuid) AS RoleName FROM sys.sysmembers |
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:
|
1 |
select * from sys.triggers |
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:
|
1 |
SELECT sqlserver_start_time FROM sys.dm_os_sys_info |
And the result will be as follows:
The opened sessions on the SQL Server instance can be also listed using the query below:
|
1 2 3 4 5 6 |
SELECT db_name(dbid) AS DatabaseName, count(dbid) AS NoOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame order by NoOfConnections desc |
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:
|
1 2 3 4 |
SELECT local_tcp_port,session_id,connect_time,client_net_address FROM sys.dm_exec_connections WHERE local_tcp_port <> 50000 order by connect_time desc |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
DECLARE @DefaultconfigValues TABLE ( ConfigName nvarchar(35), ConfigDefaultValue sql_variant ) INSERT INTO @DefaultconfigValues (ConfigName, ConfigDefaultValue) VALUES ('access check cache bucket count',0), ('access check cache quota',0), ('Ad Hoc Distributed Queries',0), ('affinity I/O mask',0), ('affinity64 I/O mask',0), ('affinity mask',0), ('affinity64 mask',0), ('Agent XPs',1), ('allow updates',0), ('awe enabled',0), ('backup compression default',0), ('blocked process threshold (s)',0), ('c2 audit mode',0), ('clr enabled',0), ('common criteria compliance enabled',0), ('contained database authentication', 0), ('cost threshold for parallelism',5), ('cross db ownership chaining',0), ('cursor threshold',-1), ('Database Mail XPs',0), ('default full-text language',1033), ('default language',0), ('default trace enabled',1), ('disallow results from triggers',0), ('EKM provider enabled',0), ('filestream access level',0), ('fill factor (%)',0), ('ft crawl bandwidth (max)',100), ('ft crawl bandwidth (min)',0), ('ft notify bandwidth (max)',100), ('ft notify bandwidth (min)',0), ('index create memory (KB)',0), ('in-doubt xact resolution',0), ('lightweight pooling',0), ('locks',0), ('max degree of parallelism',0), ('max full-text crawl range',4), ('max server memory (MB)',2147483647), ('max text repl size (B)',65536), ('max worker threads',0), ('media retention',0), ('min memory per query (KB)',1024), ('min server memory (MB)',0), ('nested triggers',1), ('network packet size (B)',4096), ('Ole Automation Procedures',0), ('open objects',0), ('optimize for ad hoc workloads',0), ('PH timeout (s)',60), ('precompute rank',0), ('priority boost',0), ('query governor cost limit',0), ('query wait (s)',-1), ('recovery interval (min)',0), ('remote access',1), ('remote admin connections',0), ('remote login timeout (s)',10), ('remote proc trans',0), ('remote query timeout (s)',600), ('Replication XPs',0), ('scan for startup procs',0), ('server trigger recursion',1), ('set working set size',0), ('show advanced options',0), ('SMO and DMO XPs',1), ('SQL Mail XPs',0), ('transform noise words',0), ('two digit year cutoff',2049), ('user connections',0), ('user options',0), ('Web Assistant Procedures', 0), ('xp_cmdshell',0) SELECT SysConfig.name AS ConfigName, SysConfig.value_in_use, DefConfig.ConfigDefaultValue , SysConfig.description FROM sys.configurations SysConfig INNER JOIN @DefaultconfigValues DefConfig ON SysConfig.name = DefConfig.ConfigName WHERE SysConfig.value <> SysConfig.value_in_use OR SysConfig.value_in_use <> DefConfig.ConfigDefaultValue GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE [master] GO CREATE LOGIN [test] WITH PASSWORD=N'123123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE [serveradmin] ADD MEMBER [test] GO USE [AdventureWorks2016CTP3] GO CREATE USER [test] FOR LOGIN [test] GO USE [AdventureWorks2016CTP3] GO ALTER ROLE [db_datareader] ADD MEMBER [test] GO USE [AdventureWorks2016CTP3] GO ALTER ROLE [db_datawriter] ADD MEMBER [test] GO USE [AdventureWorks2016CTP3] GO ALTER ROLE [db_owner] ADD MEMBER [test] GO |
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
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