Microsoft SQL Server Database Audit
Table of Contents
There is requirement that we need to perform database analysis on invalid entires into the database. We need to trace or track the entries performed in the database. But to do that we need a proper way to do it.
how to test#
In this guide we will make a test database in called Tweety in Microsoft SQL Server 2017 and connect to it using Microsoft SQL Server Management Studio.
testing environment#
server#
The test is being conducted in Microsoft Windows Server 2016. Version: 1607 (OS Build 14393.693)
sql server information#
Microsoft SQL Server Express (64-bit)
Version: 14.0.1000.169
access information#
username : sa password : abcd@1234
virtualization information#
The VM is running in ProxMox server with 4 core cpu and 4 gb of RAM having 50 GB of disk space on NVMe disk.
Theory#
Audit Specification#
We can create two types of audit specification.
Database Audit Specification#
Server Audit Specification#
experiments#
Below we will check how to detect various issues and try to detect them in the audit / log file.
enable audit#
To do this you need to first enable the database audit in the MSSQL server. To do this first you need to enable the “Audits” under the security tree after you login to the server using MSSQL Management Studio.
After you have clicked on the item, we will be able to see another windows.
In this window, you need to enter only the path where the audit files will be stored. In production environment we can move the audit to another disk which might have a separate RAID system for maximum storage capacity. You can also change the parameters according to your needs. When all changes has been done, click on OK to move forward.
Now you have to enable the Audit. Right click on the newly created audit and click on the Enable Audit as shown below:
If the audit has enabled successfully, you will see a success message box as below:
As of now we have enabled the audit and can see record. By again right clicking on the newly created audit object and then selecting View Audit Logs from the menu as shown below:
The Log File Viewer will display some logs which will tell us that the Audit system has been successfully configured and running. The screenshot can be seen below:
As of now, we have only configured the log part and nothing else. We have not selected which items we have to monitor. In the next step we will configure login failure audits.
login failure audit#
In the last step we have enabled audit and now we will enable a specific audit for login failure. In the last screenshot of Log Viewer we have also seen that no log for login success / failure was not seen.
We need to create a new specification by following the steps shown below:
It will show a window called Create Server Audit Specification. In this window we will select Audit Action Type as FAILED_LOGIN_GROUP (Details [1]). For details please check the screenshot below:
And from the Audit you will have to select our newly created audit. Now click OK to create a new specification.
Like last time, you will have to enable this specification in order to make it work. You will also shown a success if the specification was successfully enabled.
Now we will close the Management Studio and attempt to login using a incorrect password and then go back to the View Audit Logs window to check what is the reflection we can see in the audit logs.
As we can see that the Log File Viewer can successfully show us the log that an user tried to access the server using an incorrect password.
After selecting the log entry, details can also be seen in the bottom segment of the windows where we get the following information:
Statement Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
insert log#
In this section we will look into how to get a log if you are performing some insert operation in a database’s table.
Version information#
For this to work, you will have to have Microsoft SQL Server Enterprise edition where you can enable the Common Criteria compliance. How to enable this has been given below.