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

Here are the audit levels corresponding to the registry values:

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.

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.

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.

CREATE TABLE #errorlog (
  rowID int IDENTITY,
  textRow varchar(4000), 
  continuationRow int
)
INSERT INTO #errorlog
(textRow, continuationRow)
EXEC master.dbo.xp_readerrorlog
        

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

• Useful Undocumented Stored Procedures by Alexander Chigrik - http://www.mssqlcity.com/Articles/Undoc/UndocExtSP.htm
• Undocumented T-SQL Tricks with Logs by Brian Knight - http://www.sqlservercentral.com/columnists/bknight/readpurgelog.asp
• SysInternal's PsLogList - http://www.sysinternals.com/ntw2k/freeware/psloglist.shtml
• SysInternal's PsTools Suite - http://www.sysinternals.com/ntw2k/freeware/pstools.shtml