Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Auditing - Part 1

By Brian Kelley, (first published: 2004/08/05)

SQL Server has several methods of auditing logins and the simplest among these is to change the audit level within SQL Server. Using traces through Profiler or the set of trace stored procedures is another method, but most folks gets started by configuring auditing in Enterprise Manager. Depending on the auditing requirement, I may find myself using one or both methods. However, I'll save tracing for another article. 

Configuring Login Auditing

The most common way of configuring auditing for SQL server is through Enterprise Manager. Bring up the SQL Server properties for the given server (right-click on the server and choose Properties) and click on the Security tab. You can set what kind of login events to audit under the Audit Level. I have highlighted it in blue in Figure 1.

Figure 1: Setting Auditing Options

What about T-SQL? Unlike most SQL Server configuration settings, there isn't an sp_configure option or other standard means of setting the audit level in this manner. The audit level is stored in the registry and read when SQL Server is started. When we make the setting change through Enterprise Manager it updates one of the following registry values, depending on whether or not you have a named instance or not (HKLM stands for HKEY_LOCAL_MACHINE):

Default Instance: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\AuditLevel
Named Instance: HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer\AuditLevel

Here are the audit levels corresponding to the registry values:

Audit Level Registry Value
None 0x00000000 (0)
Success 0x00000001 (1)
Failure 0x00000002 (2)
All 0x00000003 (3)

If you're determined to make a change in audit level through T-SQL, the undocumented stored procedures xp_regread and xp_regwrite permit access to the registry. To read the current settings, use xp_regread.

Default Instance: Named Instance
DECLARE @AuditLevel int

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
  @value_name='AuditLevel',
  @value=@AuditLevel OUTPUT
  
SELECT @AuditLevel
DECLARE @AuditLevel int
    
EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer',
  @value_name='AuditLevel',
  @value=@AuditLevel OUTPUT
  
SELECT @AuditLevel

Use xp_regwrite if you want to change the audit level. I will offer up the standard warning about being very careful any time you touch the registry.  Unless you have a reason to touch the registry in such a way (such as applying a script across multiple servers), it's best to use Enterprise Manager. However, here is how to change the audit level using xp_regwrite.

Default Instance: Named Instance
DECLARE @AuditLevel int
SET @Auditlevel = <Audit Level Value>

EXEC master..xp_regwrite
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
  @value_name='AuditLevel',
  @type='REG_DWORD',
  @value=@AuditLevel
DECLARE @AuditLevel int
SET @Auditlevel = <Audit Level Value>

EXEC master..xp_regwrite
  @rootkey='HKEY_LOCAL_MACHINE',
  @key='SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\MSSQLServer',
  @value_name='AuditLevel',
  @type='REG_DWORD',
  @value=@AuditLevel

Note: If you alter the audit level, be sure to stop and restart the SQL Server. SQL Server reads this value from the registry during startup meaning until there is a restart SQL Server will continue to perform at the old setting.

Success, Failure, or Both

SQL Server gives us the option of auditing login successes, failures, or both. Realize that depending on your choices, there could be a lot of events recorded. This is especially true of selecting success or both. In that case every single successful new login will be recorded. If your SQL Server has a lot of activity, there will be many, many events. Also, if you have automated processes connecting to SQL Server with a SQL Server login, a single password mismatch will generate quite a few errors on the failure side as well. 

In all cases, ensure you audit at least to the level necessary. Carefully consider anything more. The events will be written to both the SQL Server log and the Windows application log, as I'll cover below. In the case of the SQL Server log, realize that this will make the log larger in size. Ultimately the log is a file on disk. The increase in events probably won't mean much as far as disk space unless your exceptionally short on such, but it will impact anything you use to view or parse the logs, including Enterprise Manager. For the Windows application log, keep in mind that there is a setting to how large the log file is and also the retention method. You may have to alter these settings to ensure you don't lose any information.

Viewing Login Events - SQL Server Log

When a login event we're auditing for occurs, the information is recorded in the SQL Server log. Failure events (Figure 2) just indicate what login attempted to login:

Figure 2: Failure Events

Success events tell us a bit more information (Figure 3). If a connection was made using Windows authentication, it's marked as a trusted connection.

Figure 3: Success Events

Getting the information out of the log using some sort of automated process takes a bit more work than you might expect. While a particular log is being written to by SQL Server, it is in use. That means you'll either need to cycle the log so it's not in use or you'll need to use some method from within SQL Server. However, if you're checking the log on a frequent basis, you're not going to want to cycle the log. There is another option. The undocumented stored procedure xp_readerrorlog gives us this access. We can parse the lines of the error log for logon events by dumping the contents of the error log to a temporary table and then looking for the substring of 'logon' in the full row.

Parsing the SQL Server Log:
CREATE TABLE #errorlog (
  rowID int IDENTITY,
  textRow varchar(4000), 
  continuationRow int
)

INSERT INTO #errorlog
(textRow, continuationRow)
EXEC master.dbo.xp_readerrorlog
        

SELECT textRow 
FROM #errorlog
WHERE CHARINDEX('logon', textRow) > 0
ORDER by rowID

DROP TABLE #errorlog

Starting with this bit of code, you can modify it to build your own automated process to report on the events recorded. Having a process collect and report all the information saves us the trouble of having to hand-check each server. In larger environments this is a must. 

Viewing Login Events - Windows Application Log

SQL Server also records the information in the Windows Application log. However, just looking at the log doesn't tell us if the logon event was a success or failure. Nor does it tell us what the login was. Figure 4 shows an example of a recorded audit event.

Figure 4: Audit Event in Application Log

Unfortunately, for both success and failure audits the Source will always by MSSQLServer (or the Instance), the Category will always be (4) and the Event ID will always be 17055. In order to gain more information we'll have to open up the event log record and look at the description. Figure 5 shows a failed login attempt.

Figure 5: Failed Login Event

The same is true of a successful login attempt. We must look at the description as in Figure 6.

Figure 6: Successful Login Event

Of course, many events are like this, so if you're already used to parsing Windows event logs this is nothing new. There are all kinds of tools for getting the information out of the event log; pick your favorite. If you don't have one, take a look at PsLogList from SysInternals. It's a part of the PsTools suite. PsLogList has the capability of outputting the event logs into a text file. I've included a link in the Additional Resources section. 

Conclusions

At some point you may be asked to set up some form of auditing on your SQL Servers, if you haven't been already. There are a couple of ways to do this, but simply adjusting the audit level on SQL Server is probably the easiest way to start. Be careful when you start auditing to the first time as you may find a lot of events being generated. These events are recorded both to the SQL Server log and the Windows application log, therefore plan for an increase in the number of records in both. You can also look in both locations to get the results of your audit. Building an automated process to report the events is easily done and helps reduce the workload of looking over the audit information. Use what works best for you.

Additional Resources

 © 2004 by K. Brian Kelley. http://www.truthsolutions.com/
 Author of Start to Finish Guide to SQL Server Performance Monitoring (http://www.netimpress.com).

Total article views: 38569 | Views in the last 30 days: 30
 
Related Articles
FORUM

SQL Server Audit Level

SQL Server Audit Level on SQL Server 2005

FORUM

Server level Login variable for trigger?

Server level Login variable

BLOG

Auditing Failed Logins – SQL Server 2008

I wrote about setting up a basic server audit recently. That showed about how a server level audit i...

FORUM

SQL Server 2005 auditing using event notifications

problem with event AUDIT_SCHEMA_OBJECT_ACCESS_EVENT - recursive behavior

FORUM

Auditing Logins in Sql Server 2005

Auditing Logins in Sql Server 2005

Tags
security    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones