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):
|Named Instance:||HKLM\Software\Microsoft\Microsoft SQL Server\<Instance
Here are the audit levels corresponding to the registry values:
|Audit Level||Registry Value|
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
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
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
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.
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.
- 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
2004 by K. Brian Kelley.
Author of Start to Finish Guide to SQL Server Performance