Expiring sessions after 15 minutes on SQL Server

Expiring sessions after 15 minutes on SQL Server

  • Comments 2
  • Likes

[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.

image

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
  • Great article! However, how to avoid system threads? What if you have replication setup to run every 30 minutes or so? Just wondering....

  • @Lazaro, I believe that replication establishes a new connection each time and thus would not be impacted. If there is a problem with any such process, the simplest solution is to create a unique login for the process and WHITELIST it, i.e.

    DELETE FROM #Who2

       WHERE Login IN ('sa','ReplicationLogin', 'Lazaro')

       OR HostName='.'

    Which means that those logins will not be killed.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment