Restricting logons during certain hours for certain users

Following a an email in a DL I decided to look at implementing a logon restriction system to prevent users from logging on at certain ties of the day. The poster had a solution but wanted to add auditing. I immediately thought of the My post on logging messages during a transaction because I new that part of the logon trigger functionality is that you rollback the connection. I therefore assumed you had to do the logging like I talk about in that post (otherwise the logging wouldn’t persist beyond the rollback.

So I jumped in developing a logon trigger solution to test the theory, oh how wrong can I be. This was definitely a RTFM situation. If only I had done that first, what I found out in reading BOL is that after you issue the rollback in the logon trigger you are able to then issue any insert/update/delete and the data will be persisted. Logon Trigger Execution Guidelines explains that,

“The current trigger continues to execute any remaining statements that appear after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back.”

So with that resolved I still thought it useful to post the trigger code that allows you to prevent users from logging in.

First we need a table to define our restrictions

create table LoginHours (

   Login varchar(100)

 , LoginStart time

 , LoginEnd time

 , Days int

  )

I made the decision that the table would also allow different days to have different times, however to make it easier to maintain multiple days could share the same time restriction by using a bitmasks for the days. So the Days column can be any combination of 1, 2,4,8,16,32,64 for Monday thru to Sunday respectively. To compare against this just use the following logic

 days & @DayOfWeek = @DayOfWeek

where @DayOfWeek represents the current day and can be calculated as follows

Power(2,datepart(dw,getdate())-1)))

The next is to define put some data in.

One tip when testing logon triggers it is always advised to have at least 2 sysadmin accounts, one that you should never put any restrictions on secondly make sure you always have a few open query windows connected as sysadmins in case you get your boolean logic wrong and block everyone from connecting.

insert into LoginHours (login, LoginStart, LoginEnd, Days)

  values ('<domain>\Simon','9:00','10:00',1+2+4+8+16+32+64)

This allows my user account to only login between 9:00 and 10:00 Monday to Friday. It would be nice to have enum support in SQL but alas no. There is a connect item “Allow literals and read only table data to be represented as enums” and I’ve written an article about it before for SQL Server Central “Enums in SQL Server

Now we need something to tell us if someone can login or not. We could embed this logic in the trigger, however for debugging that makes it difficult. Its much better to use a function which can be called outside of the trigger. In this case I’ve created a base function and 2 other function (effectively overloads)

create function udf_GetLoginRestrictionCountForLoginSecure (@UserName nvarchar(128),@DayOfWeek int)

returns table

return (

select HasLoginRestrictions = count(1)

      ,IsAllowedAtTheseHoursCount = sum(

          case when LoginStart <= cast(getdate() as time)

                 and LoginEnd >= cast(getdate() as time)

                 and days & @DayOfWeek = @DayOfWeek then 1

               else  0

           end)

 from dbo.LoginHours

where @UserName like Login )

go

 

create function udf_GetLoginRestrictionCountForCurrentLoginAndTime ()

returns table

return (

select HasLoginRestrictions

      ,IsAllowedAtTheseHoursCount

 from dbo.udf_GetLoginRestrictionCountForLoginSecure(suser_sname(), Power(2,datepart(dw,getdate())-1)))

go

 

create function udf_GetLoginRestrictionCountForTime (@username nvarchar(128))

returns table

return (

select HasLoginRestrictions

      ,IsAllowedAtTheseHoursCount

 from dbo.udf_GetLoginRestrictionCountForLoginSecure(@username, Power(2,datepart(dw,getdate())-1)))

These allow you to test to see if someone has a valid record or not. I’ve also made a step to simplify the system so users with no restrictions don’t have to be put in the table. No record indicates no restriction. The Days column makes this work because to prevent users from accessing the system you put a 0 in the Days column. This won’t match any day and as there is a record will restrict the user from logging in.

The other feature I did was to use LIKE. This means you can put a wildcard in the table to restrict all users with James in their login for instance. Probably more useful for restricting whole domains.

You could make this as complex as you like, like always allowing sysadmins.

To test the function call the function as follows, this returns 2 columns. The first HasLoginRestrictions indicates if any restrictions are in place and the second indicates if any records exist that allow access at this time.

select *

from udf_GetLoginRestrictionCountForLoginSecure('ajoes',Power(2,datepart(dw,getdate())-1))

We need a table to audit or failed logins. Information will be put in the error log but not much. You could raise severity 21 (or higher) errors to put additional information in the error log but thats not advised as it will bloat your error log with far too many messages for just 1 failed login (2 entries for the failed login and 2 for each severity 21 error raised)

create table LoginFailedAudit (LoginFailedAuditId int identity(1,1)

                              ,Audit xml)

Now we have the data and the audit we now need the trigger.

This does a number of things, firstly it is specified to execute as SELF. This means it executes as the person that created the trigger. This is essential to allow use of the functions. If you  don’t do that you need to grant SELECT on the functions to PUBLIC which is a security risk. Secondly the SET DATEFIRST ensures that Monday is always the first day of the week. Thirdly note the use of original_login() this is used to get the login used to make the connection, if we didn’t use this we would get the person that created the trigger because we are executing the trigger as SELF. Finally we use XML to combine all the data from the sessions and connections DMV and put it in the audit table.

NOTE: the rollback is done very early so that anything that occurs afterwards is not rolled back.

create trigger tr_loginAudit

on all server

with execute as self

for logon

as

set datefirst 1 -- ensures consistency for monday being 1 in the lookup table

 

declare @HasLoginRestrictions int

       ,@IsAllowedAtTheseHoursCount  int

      

select @HasLoginRestrictions = HasLoginRestrictions

      ,@IsAllowedAtTheseHoursCount = IsAllowedAtTheseHoursCount

from udf_GetLoginRestrictionCountForTime(original_login())

 

 

if (@HasLoginRestrictions = 1

   and @IsAllowedAtTheseHoursCount = 0)

    begin

    --This indicates to the server that the login is not allowed

    rollback

   

    --build a message

    declare @message xml

    set @message = (select

                           (select session_id              [@session_id]

                                  ,most_recent_session_id  [@most_recent_session_id]

                                  ,connect_time            [@connect_time]

                                  ,net_transport           [@net_transport]

                                  ,protocol_type           [@protocol_type]

                                  ,protocol_version        [@protocol_version]

                                  ,endpoint_id             [@endpoint_id]

                                  ,encrypt_option          [@encrypt_option]

                                  ,auth_scheme             [@auth_scheme]

                                  ,node_affinity           [@node_affinity]

                                  ,num_reads               [@num_reads]

                                  ,num_writes              [@num_writes]

                                  ,last_read               [@last_read]

                                  ,last_write              [@last_write]

                                  ,net_packet_size         [@net_packet_size]

                                  ,client_net_address      [@client_net_address]

                                  ,client_tcp_port         [@client_tcp_port]

                                  ,local_net_address       [@local_net_address]

                                  ,local_tcp_port          [@local_tcp_port]

                                  ,connection_id           [@connection_id]

                                  ,parent_connection_id    [@parent_connection_id]

                                  ,most_recent_sql_handle  [@most_recent_sql_handle]

                             from sys.dm_exec_connections

                            where session_id = @@spid

                            for xml path('connection'), type)

                          ,(select  original_login_name   [@login]

                                  , host_process_id       [@host_id]

                                  , host_name             [@host]

                                  , program_name          [@program]

                                  , session_id            [@spid]

                                  , login_time            [@time]

                                  , client_interface_name [@client_interface_name]

                            from sys.dm_exec_sessions s

                            where session_id = @@spid

                            for xml path('session'), type)

                    for xml path('LoginAudit'), type)

   

    insert into LoginFailedAudit(audit) values (@message)

    end

I hope you find this useful and will be interested to hear any comments.

Comments

03 December 2010 22:36 by SimonS Blog on SQL Server Stuff

# Restricting logons during certain hours for certain users

Following a an email in a DL I decided to look at implementing a logon restriction system to prevent

# Twitter Trackbacks for Restricting logons during certain hours for certain users - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Restricting logons during certain hours for certain users - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com