Implementing Login Statistics Using SQL Server 2005 Trace Feature

Introduction

 

Login statistics is a useful mechanism to keep track of login activity on a server system. Availability of such information is a requirement for systems to be certified under Common Criteria. SQL Server 2005 SP1 does not offer such information. This post will discuss ways to implement it using existing features of SQL Server 2005.

 

Let’s discuss what information it would be desirable to capture in login statistics table.

 

Scenario 1: Image a user who logs into a bank account and want to make sure that his account was safe from intrusion while she was away.

  • First the user might check is there were any attempts to break into her account.
  • She might also be interesting to know when the last time she logged in was. If this date was not the date she remembers, it might means that somebody already knows her password and using it to access her account.

In both of those cases it would be good idea to call customer service and report suspicious activity.

 

Scenario 2: Account administrator wants to monitor is there were attempts to break into anybody’s account by guessing account names passwords (dictionary attacks) and their frequency.

  • For example if it was only a single failed attempt to a certain account it might mean that a user mistyped her password, however if such number is large it might mean that somebody was trying to guess a password.
  • Also if there are failed attempts to login under non-existing accounts, it might indicate that somebody was probing the server for obvious account names. By having this information administrator can block either accounts themselves or obtain IP addresses of the attack originator to configure the firewall to block it.
  • Various types of additional information can be extracted. For example by looking at the last time of the user login administrator can decide if this account is active or not. If it has been inactive for more that X month it can be disabled until a user requests to activate it back.

Login Statistics Table

 

Hence the discussion above it would be interesting to provide following information:

  1. The time of the last successful login
  2. The time of the last unsuccessful login
  3. The number of unsuccessful logins since the last successful login

This information will be sufficient for Scenario 2, but it is not sufficient to implement Scenario 1 above. For the interactive user, once she logs in, the information about last successful login will be overwritten with her most recent login time, so she will be unable to find out the last successful login, before her most recent one, which she is obviously not interested to see. Thus it is logical to introduce another time:

  1. The time of the last interactive login, which is still active

Thus when user logs in, she would only need to pay attention to the times 1-3, but not 4. One can argue that to eliminate the confusion time 4 can be hidden from the user at all. However such approach suffers from potentially serious problem.

When there is an attempt to break into her count while her session is open, such attempt can not be noticed by the user until she logs out and logs in again. Even more if such attempt is successful (attacker knows the password) it may never be noticed by neither the user, nor administrator. Say if successful attack happened one hour in the interactive session, user may very well notice that somebody has opened two sessions under her name within one hour, while she remembers that she only opened a single session. To the other hand if user logs out and logs in several days, she may not remember exact time she logged in, most likely she will only remember what date it was and may be if it was morning or afternoon.

Even for administrator introducing time 4 will be a problem. For example if there were unsuccessful login attempts after user has opened interactive session where this information can be captured? It can’t be captured in 2, because it will erase last filed attempt for the interactive user. The solution to all those problems would be to introduce two sets of variables. One set as defined in 1-3, another set is a snapshot of this information before it was updated the last time (previous copy of the set).

With this model in mind, once a user logs in, her most current set would contain {last_login_success, last_login_fail, number_of_falures}. Notice that last_login_success will be after last_login_fail, assuming there were no failed attempts since the login. Also number_of_falures will refer not to the last_login_success, but to the one happened before. This is all correct, but somewhat confusing. To eliminate this confusion lets slightly redefine the set and introduce variable names:

  1. The time of the last successful login
    • last_success
  2. The time of the last unsuccessful login since the last successful login
    • last_fail_since_success
  3. The number of unsuccessful logins since the last successful login
    • num_failed_since_success

And the previous set:

  1. The time of the successful login before last_success
    • last_success1
  2. The time of the last unsuccessful login since last_success1 but before last_success
    • last_fail_since_success1
  1. The number of unsuccessful logins between last_fail_since_success1 and last_success1
    • num_failed_since_success1

A diagram on Figure 1 in lgnstats.rtf in the attachment demonstrates that graphically.

Thus login statistics table (we name it lgnstats) includes following columns:

  • login_name
  • last_success
  • last_fail_since_success
  • num_failed_since_success
  • last_success1
  • last_fail_since_success1
  • num_failed_since_success1

Capturing Login History

SQL Server 2005 can keep track of login history using login audit events. Whenever a user logs into SQL Server an event entry is entered into a trace file (with extension “*.trc”). Both successful (event 14) and unsuccessful logins (event 20) can be recorded. Besides a trace, this information can also be captured asynchronously using Event Notifications mapped to logon audit events. However the latter will involve some advanced features of SQL server. To the other hand trace feature is an easy way to understand and code login statistics table. It offers tabular interface for trace files using sys.fn_trace_gettable table valued function. Hence it can be used to retrieve event records and populate a real table.

A new trace can be created and configured using sp_trace_* family of stored procedures. The trace can consists of one or more trace files with maximum size, number of files and roll over policy. For this example we will chose 100 files 1 MB each with rollover policy to overwrite existing files once maximum number of files is reached. When updating login statistics table we will be looking only at the files which were not used previously to obtain statistics. We will detect such files by the latest time recorded in login statistics table, so, naturally, we want to have relatively small files. The size and the number of rollover files depends on estimated login traffic; in this example we made the file size small enough to cause rollover in simple tests. Unprocessed files will be deleted if maximum number of filled files (100 in this case) is reached. If this is not acceptable a SQL agent job can be implemented to process them periodically.

We will store files in LOG directory along with default trace files. Files are named sys_lgnstats_*.trc with incrementally increasing numeric suffix.

We’ve selected the following trace record columns in our login history trace file:

  • Login name (LoginName)
  • Login time (StartTime)
  • Event id (EventClass)

Also for debugging purposes we keep

  • SPID
  • Login SID (LoginSID)

We introduce 2 stored procedures to start and stop login history trace:

sp_lgnstats_trace_start

sp_lgnstats_trace_stop

For example:

--start login history trace

Exec sp_lgnstats_trace_start

--stop login history trace

Exec sp_lgnstats_trace_stop

For detailed implementation of these procedures see attached code on Figure3 in lgnstats.rtf.

Computing Login Statistics

Using trace files with login history along with sys.fn_trace_gettable, it is fairly straightforward to compute login statistics as described on Figure 1 in the attachment lgnstats.rtm. This logic is implemented in sp_lgnstats_merge in the attached sample and presented below:

-- Find @lastSuccessNew

SELECT TOP(1) @lastSuccessNew = StartTime FROM #lgnstats_trace

WHERE … LoginName = @login AND

EventClass = 14

ORDER BY StartTime DESC

-- Find @lastFailNew

SELECT TOP (1) @lastFailNew = StartTime

FROM #lgnstats_trace

WHERE … LoginName = @login AND

EventClass = 20 AND

StartTime > @lastSuccessNew

ORDER BY StartTime DESC

-- Find @lastSuccessNew1

SELECT TOP(1) @lastSuccessNew1 = StartTime FROM #lgnstats_trace

WHERE … LoginName = @login AND

EventClass = 14 AND

StartTime < @lastSuccessNew

ORDER BY StartTime DESC

-- Find @lastFailNew1

SELECT TOP (1) @lastFailNew1 = StartTime

FROM #lgnstats_trace

WHERE … LoginName = @login AND

EventClass = 20 AND

StartTime < @lastSuccessNew AND

StartTime > @lastSuccessNew1

ORDER BY StartTime DESC

-- Find @failCountNew

SELECT @failCountNew = count(*)

FROM #lgnstats_trace

WHERE … LoginName = @login AND

EventClass = 20 AND

StartTime > @lastSuccessNew

-- Find @failCountNew1

SELECT @failCountNew1 = count(*)

FROM #lgnstats_trace

WHERE … LoginName = @login AND

EventClass = 20 AND

StartTime < @lastSuccessNew AND

StartTime > @lastSuccessNew1

However generally we need to process only information entered after the latest time login statistics was computed. This will both help the efficiency and will be functionally correct when processed trace files are deleted. This task is a little bit more challenging, since we need to merge information from new trace files with the previous login statistics in lgnstats table.

To accomplish this we will enumerate all trace files from the one with the highest numeric index (sys_lgnstats_N.trc) and exclude from the computation all times which happened before the latest time recorded in login statistics table across all files. For example if the latest time recorded in lgnstats table is @latestRecTime, we will exclude all times regardless of the event type (success or fail) and login name, which happened before @latestRecTime. Here is the query to obtain @latestRecTime.

DECLARE @latestRecTime DATETIME

SELECT @latestRecTime =

MAX(dbo.fn_lgnstats_MAX

(last_success, last_fail_since_success))

FROM lgnstats

Note that we use dbo.fn_lgnstats_MAX() scalar function to handle NULL values as smallest time, otherwise any comparision betwen NULL and non-NULL will result in FALSE. MAX() aggregate however ignores any NULL vaues.

Now we enumerate all files starting from N, N-1, N-2,…, K until we find a file, which has a time greater than @latestRecTime. Once such file with index K is found, we use sys.fn_trace_gettable to gather login statistics in all files starting from Kth one.

SET @traceFile = ‘sys_lgnstats_K.trc’

SELECT LoginName,StartTime,EventClass

FROM sys.fn_trace_gettable(@traceFile,default)

WHERE StartTime > @latestRecTime AND

(EventClass = 14 or EventClass = 20)

At this point we can merge newly obtained login history data with already captured in lgnststs table. That would effectively require combining two diagrams on Figure 1 from lgnstats.rtf – for all and new data. While this is possible it requires a number of carefully written comparison statements. TSQL is not the most suitable language to implement this in a readable form, so rather we use an alternative approach.

Lgnstats table in our implementation is very similar to login history one obtained from the trace file. Thus we can create a temporary table *(we call it #lgnstats_w) similar to what trace file returns to us. For example lgnstats has two last successful login times, just like they would appear in the trace, so we insert them into our temporary table without changes. Lgnstats table also has failed login times, but they only show cumulative information. In their case we can expand those times by the number of failures. For example for last_fail_since_success we would insert last_fail_since_success time into temporary trace table num_failed_since_success number of times. Similarly for last_fail_since_success1. In reality we don’t insert them multiple times, instead we use an extra weight column in this temporary table. The weight column is only used to compute number of failures. Here is an example of typical #lgnstats_w table. In reality some of the times below can be NULL.

LoginName

EventClass

StartTime

Weight

Login_name

20

last_fail_since_success_new

num_failed_since_success_new

Login_name

14

last_success_new

0

Login_name

20

last_fail_since_success1_new

num_failed_since_success1_new

Login_name

14

last_success1_new

0

Login_name

20

last_fail_since_success_old

num_failed_since_success_old

Login_name

14

last_success_old

0

Login_name

20

last_fail_since_success1_old

num_failed_since_success1_old

Login_name

14

last_success1_old

0

In this table *_new designates entries obtained from the trace file, *_old is for ones gotten from lgnstats table.

We apply they the same logic to #lgnstats_w, which was used to compute login statistics from row trace file (see above) as if there were no lgnstats table to merge with. The only difference here is that we use weight column to compute a number of failed attempts rather than counting trace rows for failed logins.

Displaying login statistics

We have identified two scenarios when login statistics table can be useful. An individual user can monitor login activity in his account as well as administrator can keep track on logins by various users into the server. Obviously an individual user can see only information about herself, while administrator needs to see information about any login. SQL Server 2005 implements permission based security and it is possible that userA may have permission to view information about userB in catalogs. This is usually allowed in userA has ANY permission on userB. There is also a special permission which gives control to all logins in the server – ALTER ANY LOGIN. Grantees of this permission can also see information about all users in the server. Thus our permission rule would be following:

(*) UserA can see login statistics on userB iff:

  1. userA is granted ALTER ANY LOGIN permission OR
  2. userA is granted ANY permission on userB OR
  3. userA = userB

(**) UserA can see login statistics on everybody iff

  1. userA is granted ALTER ANY LOGIN permission

For (*) to be consistent with SQL Server 2005 catalog security model we also must check that VIEW DEFINITION permission is not denied for userA on userB. But currenly there is no easy way to achieve this using TSQL, so we ignore it.

Access to login statistics table is implemented using sp_lgnstats_get procedure. It accepts @loginName as an argument. If it is NULL it will show information on all logins.

Script

Following procedures can be used to initiate, stop and view login statistics as defined on figure 3 in lgnstats.rtf attached to this post:

-- to start login statistics

sp_lgnstats_trace_start

-- to stop login statistics

sp_lgnstats_trace_stop

-- to obtain login statistics for a user

sp_lgnstats_get @loginName = NULL sysaname

There are also a number of auxiliary stored procedures, which are used only internally.

-- Called from sp_lgnstats_get to compute login statistics

sp_lgnstats_update

-- Find the first trace file which contains data not yet

-- included in lgnstats table

-- Called from sp_lgnstats_update

sp_lgnstats_need_update

-- Combines login history from trace file and lgnstats table

-- Called from sp_lgnstats_update

sp_lgnstats_merge

To install login statistics sample copy script on Figure 3 in lgnstats.sql into SQL Server Management Studio, “select all” and execute.

Examples

Figure 2 in ithe attachment lgnstats.rtf demonstrates how to use login statistics with some examples.

Summary

Login statistics feature is very helpful in monitoring potential attacks on accounts by both users and administrators. SQL Server 2005 trace offers inexpensive way to implement such functionality without significant development efforts. Login statistics can be easily enhanced by including extra information available from a trace. For example, it can include IP address or SID of a login.

lgnstats.rtf