Expiring sessions after 15 minutes on SQL Server

Artikel
03/10/2011

[Prior Post in Series] [Next Post in Series]

In my prior posts on applying PCIDSS standards to protect your company’s data I showed now a DDL trigger (FOR LOGON) may be used to enhance logon security. In this post, I will look at a solution that works by executing a stored procedure every few minutes.

We are looking at the following PCIDSS item:

If a session has been idle for more than 15 minutes, require the user to re-authenticate to re-activate the session

This time period is a balancing act between valid idleness and users leaving their PC unattended and logged on. The goal is reduce the risk of someone walking by and exploiting the unattended PC.

Mechanism

The solution uses another ‘undocumented’ call, sp_who2. This produces a result like that shown below.

The key columns for us are:

[HostName] – where the client is located, ignore ‘.’
[Login] – ignore ‘sa’ (especially if sa has been disabled as recommended in earlier posts).
[LastBatch] – when the last activity was seen.
[SPID] – The Spool ID -- we will use to terminate the idle sessions with a KILL command.

I must point out that sp_who2 changed between SQL Server 2000 and SQL Server 2005/8. It may change or disappear in future versions of SQL Server.

Solution Algorithm

The approach follows the same pattern as before:

Capture the data from sp_who2 into a table
Filter the table
Act upon the filtered rows.
- We KILL SPIDs that are inactive for 15 minutes
Schedule the above to happen every minute (or other appropriate interval).

The Coding Solution

There are a few minor issues in capturing the data. The temporary table definition is not exactly as expected from looking at the above results, but as shown below.

 CREATE TABLE #Who2(
    [SPID] int, 
    [Status] SysName NULL,
    [Login] SysName NULL, 
    [HostName] SysName NULL,
    [BlkBy] SysName NULL,
    [DBName] SysName NULL,
    [Command] SysName NULL,
    [CPUTime] int NULL,
    [DiskIO] int NULL,
    [LastBatch] SysName NULL,
    [ProgramName] SysName NULL,
    [SPID2] int NULL,
    [RequestId] int NULL)

The [LastBatch] is not a DateTime column but a VarChar string (note that there is no year in it). We need to add a year to it and make sure that we add the appropriate year when it is the New Year (01/01). Once we have addressed these issues, then a simple query returns the [SPID] to be terminated which we do by using a cursor and dynamic SQL.

 CREATE PROC p_SessionTimeOut
AS
SET NOCOUNT ON
DECLARE @Now DATETIME
DECLARE @Cmd nvarchar(40)
DECLARE @SpId int
SET @Now = GetDate()
CREATE TABLE #Who2(
    [SPID] int, 
    [Status] SysName NULL,
    [Login] SysName NULL, 
    [HostName] SysName NULL,
    [BlkBy] SysName NULL,
    [DBName] SysName NULL,
    [Command] SysName NULL,
    [CPUTime] int NULL,
    [DiskIO] int NULL,
    [LastBatch] SysName NULL,
    [ProgramName] SysName NULL,
    [SPID2] int NULL,
    [RequestId] int NULL)     
INSERT #Who2 exec sp_Who2
DELETE FROM #Who2
    WHERE Login = 'sa'
    OR HostName='.'
ALTER TABLE #Who2
        ADD LastDate DateTime
IF Month(@Now)=1 And Day(@Now)=1
BEGIN
    UPDATE #Who2
        SET LastDate=
            CASE WHEN LastBatch Like '12%'
                THEN Cast( Substring(LastBatch,1,5)+ '/'+ 
                            Cast(Year(@now)-1 As varchar(4)) +' '+ 
                            Substring(LastBatch,7,8) as DateTime)
            ELSE
                           Cast( Substring(LastBatch,1,5)+ '/'+                            Cast(Year(@now) As varchar(4))+' ' +                           Substring(LastBatch,7,8) as DateTime)
            END    
END    
ELSE
BEGIN
    UPDATE #Who2
        SET LastDate=Cast( Substring(LastBatch,1,5)+ '/'+                 Cast(Year(@now) As varchar(4))+' ' +                 Substring(LastBatch,7,8) as DateTime)
END
DECLARE Hit_List CURSOR FOR 
SELECT SPID FROM #Who2 Where Abs(DateDiff(mi,LastDate,@Now)) > 15
OPEN Hit_List
FETCH NEXT FROM Hit_List into @SpId
WHILE @@FETCH_STATUS=0
BEGIN
    SET @Cmd='KILL '+Cast(@SpId as nvarchar(11))
    EXEC(@Cmd)
    FETCH NEXT FROM Hit_List into @SpId
END
CLOSE Hit_List
DEALLOCATE Hit_List
DROP TABLE #Who2
GO

If you are running SSMS, and go to one of the SPID killed, you will get the following message:

Msg 17892, Level 14, State 1, Line 65536
Logon failed for login 'SERVER2008X64\Administrator' due to trigger execution.
Changed language setting to us_english.

Right clicking will show that the connection no longer exists.

Reconnect and you are fine.

Summary

The above code shows how you can force users to re-authenticate on an idle session. You will likely need to do some tuning of it to better match your needs. For example, I would white-list the logins coming from the ISV product so they are never terminated.

The above stored procedure p_SessionTimeOut needs to be executed every minute by a SQL Server Agent job. If you are not familiar with creating SQL Server Agent jobs see How to: Create a SQL Server Agent Job (Transact-SQL).

Comments

Anonymous
January 01, 2003
The comment has been removed
Anonymous
March 16, 2011
Great article! However, how to avoid system threads? What if you have replication setup to run every 30 minutes or so? Just wondering....

Freigeben über