Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server / SQL Server 2008
Article

Audits on MS SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
4.71/5 (6 votes)
8 Mar 2011CPOL6 min read 80.2K   22   11
An important new feature of MS SQL Server 2008 that provides a true auditing solution - SQL Server Audit.

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:

  1. Server Audit
  2. Server Audit Specifications
  3. 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’.

Image 1

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.

Image 2

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

Image 3

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.

Image 4

Image 5

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.

Image 6

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.

Image 7

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:

SQL
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:

SQL
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:

SQL
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.

Image 8

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer
India India
I am a Microsoft certified Technology Specialist in MS SQL Server 2008 and 2005.I have fair amount of experience and expertise in MS database development, administration and modeling and MS BI. Started my career in 2007 and primarily into MS databases, and later diversified into MS BI especially SSIS. I also have a little exposure in Oracle 10g while working on one of the migration projects. But MS SQL Server is my passion!

Firm believer in knowledge grows from sharing, I really like reading books, trying new features and sharing the little that I know. Unless we're willing to have a go, fail miserably, have another go, success won't happen. The best thing I have discovered about myself is the "never say die" attitude. The best comment I have ever received is when my manager at my first job said "when this guy works he throws away his watch".

Comments and Discussions

 
GeneralCapture column updated (changed) through audit logs Pin
asterix52-May-11 22:57
asterix52-May-11 22:57 
GeneralUsing bcp command, having some troubles Pin
asterix521-Mar-11 12:28
asterix521-Mar-11 12:28 
I have this code, to create txt file from logs. But I get some errors, which are related with the query, since if I put a simple query inside @query, it is successfuly executed.
My idea is to create a specific txt file from the log, since I need to save the logs in txt files in a specific order and content (specific columns and part of columns).
Can you help solving this problem?
Thank you .
Here is the code:
____________________________________________________________________________________


DECLARE
@saveas NVARCHAR(2048)
,@query NVARCHAR(2048)
,@bcpquery VARCHAR(2048)
,@bcpconn VARCHAR(64)
,@bcpdelim VARCHAR(2)

SET @query = 'SELECT *
FROM sys.fn_get_audit_file (''C:\test\*.sqlaudit'',default,default) AS f
JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type
JOIN sys.dm_audit_actions a ON f.action_id = a.action_id
AND c.securable_class_desc = a.class_desc
WHERE f.action_id <> ''AUSC''
ORDER BY event_time DESC,sequence_number'
SET @saveas = 'C:\test\QueryOutput.txt'
SET @bcpdelim = '|'
SET @bcpconn = '-T' -- Trusted
--SET @bcpconn = '-U <username> -P <password>' -- SQL authentication


SET @bcpquery = 'bcp "' + replace(@query, char(10), '') + '" QUERYOUT "' + @saveas + '" -c -t^' + @bcpdelim + ' ' + @bcpconn + ' -S ' + @@servername
EXEC master..xp_cmdshell @bcpquery
GeneralRe: Using bcp command, having some troubles Pin
Keshav Singh21-Mar-11 20:53
Keshav Singh21-Mar-11 20:53 
GeneralRe: Using bcp command, having some troubles Pin
asterix523-Mar-11 13:05
asterix523-Mar-11 13:05 
GeneralRe: Using bcp command, having some troubles Pin
Keshav Singh23-Mar-11 19:40
Keshav Singh23-Mar-11 19:40 
Generalhi Pin
asterix518-Mar-11 1:35
asterix518-Mar-11 1:35 
GeneralRe: hi Pin
Keshav Singh18-Mar-11 1:51
Keshav Singh18-Mar-11 1:51 
GeneralAudit specific time (period) Pin
asterix511-Mar-11 2:38
asterix511-Mar-11 2:38 
GeneralRe: Audit specific time (period) Pin
Keshav Singh11-Mar-11 6:03
Keshav Singh11-Mar-11 6:03 
GeneralRe: Audit specific time (period) Pin
asterix528-Mar-11 4:25
asterix528-Mar-11 4:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.