Click here to monitor SSC
  • Av rating:
  • Total votes: 45
  • Total comments: 9
Thomas LaRock

SQL Server Audit: Magic without a Wizard

27 April 2009

In SQL Server 2008, Microsoft introduced SQL Server Audit. This is much better than anything we had before, and is likely to meet the needs of all but the largest, or the most highly-regulated of industries. SQL Server 2008 Enterprise Edition includes all of the features whereas SQL Server 2008 Standard Edition only provides only a subset. What is most attractive about it is that it is easy to administer, as Thomas LaRock   explains.

If one was to make a list of all single-word phrases that will make any database administrator bang their head on their desk surely that list would include the word “audit”. No doubt that the idea of an audit, as well as a visit from auditor, has caused more than one painful experience for most database administrators. An auditor once insisted that I should remove the ‘sa’ account on a SQL 2000 instance because “using that account is bad, and you shouldn’t be doing that”. Well, I wasn’t using it, as I have always used windows authentication where possible, which isn’t always the case. He was just looking at a list of logins for the instance and immediately focused on that account; he told me not to use it, and then asked me how soon I would be able to get rid of it.

“Never,” I tried to explain, but he went off to the deep recesses of the building to write the report he’d already set his mind on writing. I never saw him again, and I can only assume that he was promoted at some point to oversee the Federal Reserve banks judging by the financial state we are in these days.

Auditing a SQL Server instance is not anything new. Previous and current versions of SQL have had C2 auditing functionality, which can incur more overhead than necessary and is not always flexible. For example, if the c2 logs fill up the disk to which they are being saved, the SQL instance will shut itself down. The data being collected by c2 gives details about both failed and successful attempts to access or modify objects and data, but the results are not returned in a way to make it easy to determine the nature of the events, simply the events themselves.

SQL 2008 Enterprise version allows for you to quickly and easily configure SQL Server Auditing to automate your auditing needs. The simple explanation for how this works is as follows:

  1. Create a SQL Server Audit object
  2. Create an Audit Specification (can be at the server or database level)
  3. Turn it on

It really is that easy to get it up and running. The hard part will come later on when someone stops by to ask for the details surrounding specific events, but even that is not difficult to provide due to the inclusion of an audit log reader.

Creating a SQL Server Audit

A SQL Server Audit is a security object that collects and logs either actions or groups of actions. It is similar to a parking enforcement officer who walks around town, checking on parked cars. If a car is found to be in violation of a particular code, the officer will write a ticket (i.e., output to a log). In our case, the violations could be found at either the instance level or the database level. SQL Server Audits are always in a disabled state when created, which is the same as saying that you need to remind your parking enforcement officer when it is time for their first day of work.

You can create a SQL Server Audit with T-SQL or through SSMS, look for it under the Security folder and you will find a folder named 'Audits'. It is worth noting that you create a server audit, not a database audit. This is because the audit object is associated with the entire instance and yes, you can have multiple audits defined at the instance level. You will next specify what exactly you want to audit, which will be either an instance specification, database specification, or both if required.

Figure 1

If you right-click on the ‘Audits’ folder under the Security node inside of SQL Server Management Studio you will see a screen similar to Figure 1. Give your audit a name, and then decide if you want to store the information in a file, the Security event log, or the Application event log. If you select a file location you will need to supply a path for the binary file that will be produced. Click ‘OK’ and you are done. Of course, you could do all of this using T-SQL if you so desire, and could generate a script from this screen as well.

Creating a Server Audit Specification

Now that you have created a Server Audit, you will need to create either a Database Specification or a Server Specification. In SSMS, the Server Audit Specifications folder is located right below the Audits folder; you really cannot miss it unless you go looking somewhere else in which case go back to the start of this sentence and try it again. You can create only one Server Audit Specification per SQL Server Audit, but the specification can encompass multiple audit action groups, which just means that you define all of the instance level actions you want to be audited.

I will say there is a plethora of action groups for you to choose from, because no one knows how many actions groups make up a plethora and I like being intentionally vague. You can check the BOL for more details, or just be lazy and click on this link. I will even provide you with a screenshot (Figure 2). You're welcome.

Figure 2

When you create the specification you define the state to be either ON or OFF, unlike the SQL Server Audit, which will only be OFF upon creation. The Server Audit Specification must be enabled before you can audit the action groups which only makes logical sense and I feel silly for even bothering to remind you. Here are some of the more important server audit specifications, and what they are used for:

  1. FAILED_LOGIN_GROUP - This specification is used to identify when a principal tries to connect to the SQL instance and fails. Typically this would be a minimum audit requirement, to track failed login attempts against the SQL instance.

  2. SERVER_ROLE_MEMBER_CHANGE_GROUP - This specification will track events that either remove or add logins to a fixed server role. Often times audits will focus on high level accounts, such as members of the System Administrators fixed server role. Use of this specification would allow for you to easily determine if and when logins are modified with respect to a fixed server role.

  3. AUDIT_ CHANGE_GROUP - Possibly one of the most important specifications, this is the one that tracks changes to existing audits as well as the creation of a new audit. This is very important as often an auditor will want some proof that the current audit process has not been tampered with in any way.

Creating a Database Audit Specification

A Database Audit Specification can be created for each database on the instance for each SQL Server Audit. This allows for a great deal of customization as you are able to have different types of audits on different databases on the same instance. So, one database may require you to track all changes to objects in any schema, but the other database may only require you to log any time a user has been added or removed. A Database Audit Specification utilizes Extended Events to track and log events, meaning you can add audit multiple action groups or audit events to a specification, giving you the flexibility to perform various audit tasks.

You can create a Database Audit Specification with T-SQL or through SSMS, you can find it in SSMS by going to your database and expanding the Security folder (Figure 3). This is often going to be the level of detail you will need to drill into, as you will probably be asked to track CRUD events or to provide details about something that happened weeks ago.

Figure 3

Here are some of the more important database audit specifications, and what they are used for:

  1. DATABASE_OBJECT_CHANGE_GROUP – This specification is used to capture events related to the creation, the dropping, or altering of any database object, including schemas. Quite possibly one of the most often requests we see from auditors.
  2. DATABASE_ROLE_MEMBER_CHANGE_GROUP – This specification comes in handy when you need to track users that are added or removed from a database role.
  3. DATABASE_PRINCIPAL_CHANGE_GROUP – This specification is used whenever principals (i.e., database users) are created, dropped, or altered within a database. It is also used when trying to track events related to the creation or removal of a database role.

Enabling the SQL Server Audit

If you are one of those that just love to use T-SQL, then go ahead and enable the SQL Server Audit using T-SQL. The syntax for enabling the audit is as follows:

ALTER SERVER AUDIT GothamCityAccess

STATE = ON;

GO

The above code would be quite useful (and necessary) if you needed to enable an audit on a hundred instances across your enterprise. If you are lazy like me, or just have a few instances to manage, just go into SSMS, find the Audit, right-click, and enable it. Done. Of course, you had better make certain your Specification(s) are already enabled; otherwise you will not be collecting anything.

The ability to be able to script out most of the actions you configure through SSMS with regards to SQL 2008 Audit is quite valuable for large enterprises. It will allow for you as an administrator to ensure you have consistent audits configured across all instances with a few clicks of the mouse. For example, you could configure an audit to track all failed logins, script that out, and deploy to every instance with the assurance that it is being applied in a consistent manner everywhere; that all instances will begin tracking the same events in the same exact way.

Once the audit is enabled you can view events in a log file viewer, providing of course that the events to be audited have happened. In other words, if you look to enable auditing on failed logins, you need to make certain a failed login has actually happened before you decide to send me a nasty email and say "this doesn't work". Right-click on your Server Audit object and select the ‘View Server Logs’ option (Figure 4).

Figure 4

Summary

SQL 2008 Security Audit is very easy to implement. There is no wizard, but the overall architecture is not so complex that a wizard would even be necessary. The GUI itself will help lead you in the right direction. For example, you cannot create specifications unless a SQL Server Audit exists, which should be a big red flag for you to help guide you to create the SQL Server Audit first.

Once you create your server audit, you would then create either a server audit specification or a database audit specification (or both). You would enable the specifications, then the server audit, and you will begin to collect information on the events as they happen. The use of the log viewer to review events is quite valuable, as you can filter and drill down into the specific events you need to review. As more and more pressure comes towards database administrators to provide the means to track events and activities, SQL 2008 Audit provides a variety of ways to get the job done.

I love the direction that Microsoft is taking with regards to auditing. This new functionality is much more robust than anything they have had previously. It's like the difference in eating at a restaurant that serves lobster bisque and foie gras with one that has a menu with words like "bucket", "basket", or "gutbuster".

Thomas LaRock

Author profile:

Thomas LaRock is a seasoned IT professional with over a decade of technical and management experience. Currently serving as a Senior Database Administrator manager for Confio Software, Thomas has progressed through several roles including programmer, analyst, and DBA. Prior to that, he worked at several software and consulting companies, working at customer sites in the United States and abroad. Thomas holds a MS degree in Mathematics from Washington State University and is a member of the Usability Professional’s Association. Thomas is also a member of Quest Software’s Association of SQL Server Experts, currently serves on the Board of Directors for the Professional Association for SQL Server (PASS), and is a SQL Server MVP. Thomas can also be found blogging at http://thomaslarock.com and is the author of DBA Survivor: Become a Rock Star DBA (http://dbasurvivor.com).

Search for other articles by Thomas LaRock

Rate this article:   Avg rating: from a total of 45 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Editions supporting Auditing
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 30, 2009 at 9:31 AM
Message: Artical does not mention that edition of SQL Server must be Enterprise

Subject: Segregation of duty
Posted by: PollusB (view profile)
Posted on: Tuesday, May 05, 2009 at 5:45 PM
Message: Can you block DBA's from disabling audit? If not, how can you make sure the trace is not stopped before an evil DBA passes by?

Subject: Access
Posted by: Philip Kelley (view profile)
Posted on: Thursday, May 07, 2009 at 9:04 AM
Message: A follow-up to PollusB, who can create or modify audits? Is this SysAdmin only, or can it be delegated? (I'd go look it up in BOL myself, but then no one else would know...)

Subject: RE: Segregation of duty
Posted by: Thomas LaRock (not signed in)
Posted on: Saturday, May 09, 2009 at 10:39 PM
Message: PollusB, Philip,

If a DBA makes changes to the audit, then the audit can be made to show those changes. And yes, any sys admin would be able to make modifications.

I understand that this is an important piece of any structured auditing tool. The ability to track such modifications is there in the tool but I would caution that no system is foolproof.

Once you list out your requirements for the audit you should be able to find the functionality you need.

Subject: Overhead for Auditing Feature
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 12, 2009 at 4:56 PM
Message: What type of overhead does this cause for SQL Server and applications running on SQL Server?
Is this a log reader feature?

Subject: Overhead for Auditing Feature
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 12, 2009 at 4:57 PM
Message: What type of overhead does this cause for SQL Server and applications running on SQL Server?
Is this a log reader feature?

Subject: Overhead for Auditing Feature
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 13, 2009 at 3:36 AM
Message: if you can't trust your dba, you need to replace him/ her

Subject: Good Artical
Posted by: Rajesh Ranjan (view profile)
Posted on: Wednesday, June 03, 2009 at 2:13 AM
Message: Firstly
Hi to all viewers & specialy to Thomas LaRock who has written the article.

Acording to me the article is providing compleat overview that how and why to use Audit.
It's Good.
Because this is an artical, not a book.
Every body learn in IT field when they do that practically & when they face some problem.
If we want dump of solution, It's wrong.

Thanks to Thomas LaRock

Subject: SQL Server Audit - Reverse Engineering Log Output
Posted by: mread (view profile)
Posted on: Wednesday, October 09, 2013 at 10:30 PM
Message: Hi Thomas.

Have you (or anyone) come across a test harness (T-SQL) that will systematically trigger ALL server-wide auditable events? I'm trying to build a map but it's arduous, especially as I am not a seasoned DBA.

For example, DROP DATABASE xyz will trigger (and therefore log) the DATABASE_CHANGE_GROUP
event.

I cannot see any way of reverse-engineering the audit log output (*.sqlaudit) to obtain this information.

http://technet.microsoft.com/en-us/library/cc280663.aspx only provides hints as to what SQL command will generate what event.

With thanks,
Mark

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.