Introduction
Securing the server and database is a very essential aspect. The principle of least privileges should always be kept in mind while granting access to any user on the server or database. As a DBA, we adhere to and promise confidentiality, integrity, and availability of the server and the hosted databases. Security plays a very important part and helps our purpose.
DBAs employ various checks to audit and to keep track of any unauthorized activities that might be going on at the server or database level. A key part of any data security strategy is the ability to track who accessed, or attempted to access data. This provides the ability to not only detect unauthorized access attempts, but also any malicious actions by insiders who try to misuse their legitimate access. Furthermore, a rich and robust tracking capability can provide oversight of sensitive configuration changes made by administrators.
Forget about databases for a moment; in general, why do we perform audits? Auditing is a mechanism to help us find if the infrastructure setup is efficient and healthy and meets our requirements. It tells us where the nail is to be hammered. Audit logs show us who tried to get into the system but was filtered by the security measures employed by us.
We may have a security plan in place, with all the rules and measures in place to keep unauthorized elements at bay, but to be assured of their usability and authenticity, we need to perform audits regularly. Until SQL Server 2005, DBAs performed audits using SQL Profiler. They ran and reviewed traces, wrote DDL/DML triggers to log critical activities, or used the SQL Server error log, Windows NT log, or SQL Agent logs for logging specific events, e.g., failed logins etc.
With SQL Server 2008, Microsoft introduced an important new feature that provides a true auditing solution - SQL Server Audit. It offers numerous attractive advantages that will help DBAs achieve their goals of meeting regulatory compliance requirements with just a few clicks of the mouse. SQL Server Audit includes the ability to provide centralized storage of audit logs and integration with System Center, as well as noticeably better performance. Most importantly, it permits fine-grained auditing whereby an audit can be targeted to specific actions by a principal against a particular object.
It’s really appreciable that finally Microsoft has given due consideration to providing the means of efficient auditing at such a granular level which can be managed/archived centrally. Audit files can be reviewed and archived at a customized, centralized path at the DBA's will.
To be able to understand how SQL Server 2008 Audit functionality works, it’s important to be well versed with:
- Server Audit
- Server Audit Specifications
- Database Audit Specifications
Let us get an insight on each of these, and with an example, we will try to get a closer look at audits.
Server Audit
To configure auditing on a SQL Server instance, ‘Audits’ is the place to start with. Audits can be logged in either a file or in a security log or in an application log. We can configure where or how to log them using ‘Audits’.
Fig. 1
Server Audits act as a sink for dumping all audits. Server Audits specifies where the audits will be recorded but not what will be recorded. As shown in the above figure, right click on ‘Audits’ and select 'New Audit'. A screen similar to Fig. 2 pops up.
Let’s review this screen in detail. As the name suggests, 'Audit name' is the name of the audit, and Queue delay (in milliseconds) defines the time interval after which the events will be written in the logs.
Shut down server on audit log failure gives DBAs an option to shut down the server in case of an audit log failure. Next is the option to choose the destination where the audits will be logged. Let us select File.
Fig. 2
Using the Browse button in File Path, select the path where the logs and files will be saved. Let’s create an audit and define the destination as “File” and save it to C:\Keshav.
Server Audit Specifications
The Server Audit Specification object describes what to audit at the server level. There is a one-to-one relationship between the Server Audit Specification object and the Server Audit object. A server audit specification is associated with a server audit in order to define where the audit data is written. Right click on Server Audit Specifications and select New audit specifications. A screen similar to Fig. 4 pops up. Enter a Name and select an audit from the Audit drop down. In the Audit action type section, various server level audits are available for selection. For our example, let’s select “FAILED_LOGIN_GROUP”.
Finally click OK.
Database Audit Specifications
This describes what to audit but, as the name suggests, focuses on the actions which occur in a specific database. Where the audit data is written is defined by the association of a database audit specification with a Server Audit object. There is a 1:1 relationship between the database audit specification and the server audit object.
As depicted in Fig. 5, the database audit specifications are located under Databases>TestDB>Security.
Right click on it to see a pop up screen similar to Fig.6 and set up a name for the database audit specification. As we can see, there is a 1:1 mapping between our new database spec and the destination audit. Select a destination from the Audit drop down. Next, set up the audit that has to be tracked. There are a lot of granular audits available at our disposal. For this example, we select action as INSERT for our Employee table in the TestDB database. We’ll create another audit for UPDATE on the same table.
Click on OK.
Note: by default, the audit created on all three levels is disabled. MS SQL Server expects that you enable audits after configuring them. Once configured, right click and enable all three audits we just created.
Next, we’ll try to log in with invalid credentials.
As these are not legitimate credentials, we’ll not be able to login. Now let’s login with a valid credential.
Once done, run the DMLs below on TestDB:
UPDATE TestDB..Employee SET ENAME='Andy' WHERE EID=5;
INSERT INTO TestDB..Employee VALUES('Rose','IT');
If we look at C:\Keshav, we’ll find that a binary file with extension .SQLAUDIT has been created with the name that we specified for the server audit. Since we chose the FILE option as our audit data sink, this file will store all the results of the server audit spec and the database audit spec which was configured in the server audit to act as its destination.
To validate the results of the audit, execute the query below:
SELECT * FROM fn_get_audit_file(
'C:\Keshav\Audit-*.sqlaudit',
default, default)
The result shows all the details of the audit.
Let's filter a little and re-evaluate:
SELECT event_time,action_id,statement,database_name,server_principal_name
FROM fn_get_audit_file(
'C:\Keshav\Audit-*.sqlaudit',
default, default)
event_time action_ statement database server
id _name _principal
_name
2011-03-01 11:27:24.43 LGIF Login failed for user 'Amit'. [CLIENT: Amit
Reason:Could not find a login <local
matching the name provided. machine>]
2011-03-01 11:30:41.01 UP UPDATE TestDB..Employee TestDB keshav
SET ENAME='Andy' WHERE EID=5;
2011-03-01 11:30:15.22 IN INSERT INTO TestDB..Employee TestDB keshav
VALUES('Rose','IT');
(3 row(s) affected)
Observe that the DML details which were executed for updates and inserts have been recorded as part of the database audit spec. Also, the name of the user who executed the DML has been logged. This was part of the database audit. As far as server audit is concerned, we find that the information of the person trying to login with invalid credentials has also been logged.
With the help of this example, you will get a hang of this exciting new feature.
The architecture level implementation of audits has been demonstrated in Fig.7.
Conclusion
SQL Server 2008 has taken auditing to the next level and has also made it considerably simpler and centralized. Hope this article has been successful in bringing out this feature at a fundamental level. Audits have a much wider implementation, and discussing the same is out of the scope of an article.
