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.