The SQL Server Auditing feature is often used to define an audit of the SQL Server settings and database access on your SQL Servers. With more people working from home at present and as companies move more resources and processes into the cloud, auditing is becoming crucial in a business environment. Let us explain why this is the case.
If your organisation is hacked, then often the first questions are:
- What data did the hacker access?
- Did they change anything? If so…
- What did they change?
This information can be very difficult to find after the fact. It can be even harder to locate, and time-consuming to find if the hack occurred a long time ago. This is where the SQL Server Auditing comes in – if we have a database audit enabled on a database, then we can be in a position to track down potential holes in our SQL Server and infrastructure security as well as unauthorised changes of data.
What Should I Audit?
Looking at SQL Server Native auditing, we often set up 2 audits. One for the Server side of SQL Server – this is to monitor any changes to the actual SQL Server configuration. The other side is a Database Audit. This audits changes to the actual database itself. Looking at Database Audits there are a lot of things we can audit:
The trick is – if we audit too much, then the audit becomes unwieldy and can take a lot of time to review and find the correct info. If we audit too little, we may miss vital information. As a guideline we would suggest the following events in the database be selected for auditing:
| AUDIT ACTION TYPE | COMMENTS |
| INSERT, DELETE, UPDATE | Any changes to data in the database tables |
| FAILED_DATABASE_AUTHENTICATION_GROUP | Has a login been added or removed from a database role (eg dbo; db_writer etc) |
| FAILED_DATABASE_AUTHENTICATION_GROUP | Someone tried to login to the database and failed. A new connection failed to connect to the database. Could be evidence of a malicious attack. |
| DATABASE_PRINCIPAL_CHANGE_GROUP | Users are created, dropped or altered in the database eg a database user’s permissions are changed |
| SCHEMA_OBJECT_CHANGE_GROUP | Any CREATE, ALTER or DROP statements executed on objects eg tables in the database |
| AUDIT_CHANGE_GROUP | Any changes to the audit (audit items added or removed). Audit the DBA to ensure they do not change what we are auditing. |
Where are Audits Stored?
You can configure this as you desire. Often Audits are set to write to a file on disk or to the Windows Security Event Log. We recommend the former to keep the audit separate and not flood the windows log with events.
My Audit is Set Up: Now What?
Once the audit has been configured, your DBA should be reviewing the output of the audit (and restarting it to keep the output minimal) on a regular basis. If, for example, we have hundreds of failed logins – which may indicate a brute force hack – it is better to pick this up quickly rather than reviewing the audit logs and identifying that the system has been attacked after the fact – maybe we could have done something to prevent it.
A simple way of reviewing the captured audit log is to execute the following TSQL:
SELECT * FROM
sys.fn_get_audit_file(‘C:\Wardy\Wardy_Server_Audit_03632A7E-0C43-4FA9-BD7A-4E44760CC911_0_132325205979760000.sqlaudit’,default,default)
Complimenting Your SQLServer Audits: Other SQL Server Advanced Functions
SQL Server Auditing allows you to review what has happened on your SQL Server. But what about preventing it in advance? Obviously, there are the normal security features – setting password policies, utilising Windows authentication, etc., but the following advanced security features are also available in SQL Server and should be considered to harden your SQL Server installation:
- Transparent Data Encryption: Encrypts your ‘at rest’ data. – IE the database files
- Encrypted Backups: Encrypts the Database backup files. This occurs automatically if TDE is enabled
- SSL Encryption: TDE encrypts your database files, but SSL encryption encrypts the network traffic between the user and the SQL Server.
- Always Encrypted: Encrypts the actual data in the database – masking the data from users who should not have access to view the data
Should you need any assistance at all with implementing SQL Server Auditing or any other security feature in SQL Server please do not hesitate to contact us here at WARDY IT Solutions.
