This guide describes how to enable MSSQL server and database level auditing. Database auditing basically involves capturing and monitoring database activities. The server-level audit involves server operations, such as management changes and logon and logoff operations while database level auditing involves actions such as data manipulation languages (DML) and data definition language (DDL) operations.
Enable MSSQL Server and Database Level Auditing
In this guide, we are going to learn how to enable MSSQL server and database level auditing using SQL server 2017 (evaluation copy) using SQL Server Management Studio.
Login to your SQL server 2017 using SSMS.
Create SQL Database Level Audit
To create SQL database level audit, navigate to Security > Audits. Right click on Audits and click New Audit.
On the window that opens up, set the Audit name, Audit destination (File), Audit File path, maximum Audit file size and leave the other options with default settings. To see a description of each options, check here.
Once you are done configuring the Audit options, click Ok to save it.
Next, navigate to Databases > Expand database you want to enable audit (card-data in my case) > Security > Database Audit Specifications.
Right click on Database Audit Specifications and click New Database Audit Specifications to create new database audit. On the window that opens up, set the name of the database audit, Select the Audit you created above from the drop down. Next, select the audit type actions.
For demonstration purposes, we selected the:
DATABASE_PRINCIPAL_CHANGE_GROUPwhich triggers an event when principals such as users are created, altered or dropped from a database.SCHEMA_OBJECT_CHANGE_GROUPwhich triggers an event when a CREATE, ALTER, or DROP operation is performed on a schema.
You can read more about Audit Action Groups and Actions here. Once done setting the audit actions and action groups, click Ok to save the audit.
Be careful when selecting the audit actions and groups. Otherwise, you may end end up with millions of audit events.
Now that we have the Audit itself and the database audit specification, they are disabled. To enable the audit, right click on the audit and select Enable Audit.
Next, enable the database audit specification by right clicking on the database audit and selecting Enable Database Audit Specification.
Test Auditing
Now that you have created and enabled the database level auditing for your database, run some tests to verify if the events are being captured. To begin with,based on our audit action groups selected above, create and drop a database user and check the audit logs.
Navigate to Security > Logins > Right click on Logins > New Login. On the new window, enter the username, set define the authentication method (Using SQL Server authentication in my case). You can opt to enforce password policy. Under Default database, select the database on which we enabled audit from the drop-down. To map the user to your database, click on User Mappings and check the database you want to map the user to. Click Ok to save the user.
Now, drop the database user created. Navigate to Databases > your-database (card-data in my case) > Security > Users > Right Click on user created > Delete > Ok.
You should now be able to see two events, create and drop user. Navigate to Security > Audits > Right Click on audit you created >View Audit Logs. Expand the log viewer to see more audit log details.
You can also query the audit log file saved under C:\Audits\CardData. The query below selects specific fields of the log file.
select event_time, action_id,server_principal_name,database_principal_name,database_name,object_name,statement,client_ip,application_name
from sys.fn_get_audit_file ('C:\Audits\CardData\*.sqlaudit',default,default) order by event_time DESC
To query all the fields, use select * from sys.fn_get_audit_file ('C:\Audits\CardData\*.sqlaudit',default,default).
Server-Level Audit
To enable server level audit, Navigate to Security > Server Audit Specifications > Right Click on Server Audit Specifications > New Server Audit Specifications. Set the name of the audit, choose the audit created earlier on the drop down. Choose the audit action types. In my case, I would like to see the authentication events to the database. Hence, have selected SUCCESSFUL_LOGIN_GROUP, FAILED_LOGIN_GROUP and LOGOUT_GROUP.
After that, enable the audit by right clicking the name of the server audit defined > Enable Server Audit Specification.
Perform the three events; successful, failed login and logout to the database. Ensure that you are using the SQL authentication method to login to the database.
Under Options, select the database to login to. Once you have performed the three authentication events above, view the audit logs to check if any event was captured.
Well, that is all about how to enable MSSQL server and database level auditing. In our next article, we are going to learn about to monitor MSSQL database activities using AlienVault SIEM. Stay connected.
