{"id":5354,"date":"2018-11-12T09:34:59","date_gmt":"2018-11-12T09:34:59","guid":{"rendered":"https:\/\/dbtut.com\/?p=5354"},"modified":"2020-10-07T13:46:09","modified_gmt":"2020-10-07T13:46:09","slug":"how-to-create-sql-server-audit","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/11\/12\/how-to-create-sql-server-audit\/","title":{"rendered":"How To Create SQL Server Audit"},"content":{"rendered":"
We will create sql server audit step by step in this article. We need to create an Audit file to record transactions made by people and dbas in SQL Server.<\/p>\n
Create SQL Server Audit<\/h3>\n
First, right-click Audits in the Security section of SSMS and click on the new audit.<\/p>\n
We give a name to the AuditName section. I prefer to give the name of the database I created(AuditDBName). Because I will create the audit database-based.<\/p>\n
SQL Server Auditing Best Practices<\/h4>\n
Do not make any changes on “queue delay” and\u00a0 “On Audit Log Failure” sections.<\/p>\n
If you select “shut down server” from the “On Audit Log Failure” section, sql server service will stop if the audit doesnt work. You can select this option if you do not want the database to work when the audit does not work. This option can be selected in applications where security is very high.<\/p>\n
Audit Destination will remain as File.<\/p>\n
In the File Path section, we specify the path to write the Audit File.<\/p>\n
You can save Audit to the local server where your instance is running. But in order to keep the DBA’s own security and keep all DBAs under control, it is better to write the audit to a share on a remote server.<\/p>\n
On the share on the remote server, the sql server service account must have only the insert and modify authority. There should not be any rights to drop and delete. In this way, a malicious dba will not be able to modify or delete the audit file.<\/p>\n
Do not make any changes on the “Audit File Maximum Limit” section.<\/p>\n
I usually set Maximum File Size specifically.<\/p>\n
If the audit size does not exceed 100 MB in 1 month, I set it to 100 MB.<\/p>\n
If the audit size will exceed 100 MB in a week, I set it to 1000 MB.<\/p>\n
We do not choose Reserve disk space because it unnecessarily allocates disk space before writing audit.<\/p>\n
You can also filter the audit from Filter option.<\/p>\n
You can see these operations below.<\/p>\n
<\/p>\n\n
SQL Server Audit Specification<\/h3>\n
We can create two types of audit specification.<\/p>\n
\n
Database Audit Specification<\/li>\n
Server Audit Specification<\/li>\n<\/ol>\n
Create Database Audit Specification<\/h3>\n
Right-click on the “Database Audit Specification” in the Security tab of the database, and click “New Database Audit Specification”. We give a name from the Name field. We choose the audit we created from Audit section.<\/p>\n
SQL Server Audit Action Type For Database Audit Specification<\/h4>\n
Then, in the “Audit Action Type” section, we select the sql statement that we want to audit, “DATABASE” from the object Class section, database name from the Object Name section, and the user from the Principal Name section.<\/p>\n
As a critical knowledge;<\/strong> To watch sysadmin on Instance, you must select dbo in the Principal Name section.<\/p>\n
As you can see below, we will audit the selects, updates and deletes performed by sysadmin\u00a0 and updates and deletes performed by denemeUser.<\/p>\n
<\/p>\n
SQL Server Audit User Activity<\/h4>\n
You can chose specific user from PrincipalName and chose specific audit action type for those users when creating Database Audit Specification.<\/p>\n
SQL Server Audit Select Statements<\/h4>\n
Normally we dont audit select statements, because most application sends a lot of select and fill audit log files. But you can audit select statements for speficis users. Look at the above image first. Then Select “SELECT” from Audit Action Type and “DATABASE” from Object Class , your database name from ObjectName and specific login name you want to audit from PrincipalName.<\/p>\n
SQL Server Audit Table Data Changes<\/h4>\n
Like Select statements we have mentioned above you can audit table data changes by using Database Audit Specification. Use UPDATE and DELETE\u00a0 instead of SELECT as action type to perform this operation.<\/p>\n
Create Server Audit Specification<\/h3>\n
We do a new audit file configuration by repeating the first operations we performed in the “Database Level Audit” section.<\/p>\n
Then right click on Server Audit Spesification from Security Tab and click on new server audit specification.<\/p>\n
We give a name from the Name section and select the audit we created.<\/p>\n
SQL Server Audit Action Type For Server Audit Specification<\/h5>\n
And we choose action from action type and click ok. Here you can follow a lot of things on server level.<\/p>\n
For example, failed logins, changes on schemas, changes on audits.<\/p>\n
I’m using almost all server-level actions.<\/p>\n
In this way, we record all the work done by the database administrators at the server level.<\/p>\n
<\/p>\n
For example, if a malicious dba stops the select audit in that database to take a select from a database that should not be read, you can create another audit for logging it.<\/p>\n
Or, you can create an audit when a login with a select right is created in a database that should not be read.<\/p>\n
In this way, you can follow every step taken by everyone and every breath they take. This includes you. Of course, a dba provides his\/her own security by creating an audit system.<\/p>\n
SQL Server Audit Log<\/h4>\n
To see the SQL Server audit log, right click on the relavant Audit file and then click View Audit Logs.<\/p>\n
<\/p>\n
Filtering SQL Server Audits<\/h3>\n
Sometimes you may want to use filters when creating Server Audit Specification or Database Audit Specification. You can use below commands for specific columns on the audit file you SELECT<\/p>\n
You can SELECT the audit log files we created with the help of the below article.<\/p>\n
![](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/11\/349.png\")
<\/p>\n\n![](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/11\/753.png\")
<\/p>\n![](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/11\/256.png\")
<\/p>\n![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/11\/sqlserverauditlog.jpg\")
<\/p>\n