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