December 2, 2016 at 1:31 pm
Hey y'all... I've been asked to create a trigger on our database server to track and potentially block people who connect to the server from Excel and potentially other applications. I came up with the following and put it on a development server, and it seems to work as expected. Eventually we may want to allow these connections for some people but not others. Long story.
But before I do something like this in production, I was wondering if anyone here had any thoughts on this sort of thing... what are the dangers, potential pitfalls? Is there a better way? Is there some glaring error in my code that I missed?
use master
go
create table CheckLogins
(
UserName varchar(60) not null,
ApplicationName varchar(200) null,
LoginTime datetime
)
go
create TRIGGER tr_check_logins
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Microsoft Office%'
begin
insert master.dbo.CheckLogins (UserName, ApplicationName, LoginTime)
values (SUSER_NAME(), APP_NAME(), getdate())
return;
--ROLLBACK --if we want to disallow Office connections
end
else
begin
return;
end
END
December 2, 2016 at 1:50 pm
does that trigger actually work for a non-sysadmin?
looks like you are just logging offenders, and not denying their connection.
any login would need insert permissions on insert master.dbo.CheckLogins , is that just granted to public?
my typical end users don't have any user permission in master.
Lowell
December 2, 2016 at 2:02 pm
You probably want a
WITH EXECUTE AS
on that proc for the insert into a table in master.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2016 at 3:05 pm
GilaMonster (12/2/2016)
You probably want aWITH EXECUTE AS
on that proc for the insert into a table in master.
Ack, good catch 😀
The reason I used master was that I didn't want to risk the database or table not being there, but I think I'm going to just check for that in the trigger and have it abort if anything is missing. I think I will put the tables in another database...
Right now I'm toying with having a table that stores a list of applications to check against, and another table for users who are allowed to bypass the check. Basically the situation we have is that most of our users have read access because of the way our applications work; but a few of them have been abusing it via Excel. We want to be able to block the general population from doing this, but there are a few people we need to allow.
So far I have:
create TRIGGER [tr_check_logins]
ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON
AS
BEGIN
declare @app varchar(200) = left(APP_NAME(), 200),
@user varchar(60) = left(suser_sname(), 60)
/* if table does not exist, abort the trigger */
if object_id('master.dbo.CheckLogins') is null
begin
return;
end
/* if table does not exist, abort the trigger */
if object_id('master.dbo.CheckedApplications') is null
begin
return;
end
/* if table does not exist, abort the trigger */
if object_id('master.dbo.CheckedApplicationUsers') is null
begin
return;
end
/* if the application name matches a name in the table... */
if exists (
select * from CheckedApplications (nolock) where @app like '%'+ApplicationName+'%'
)
begin
if not exists (
select * from CheckedApplicationUsers (nolock) where @user = UserName
)
begin /* user is not allowed to bypass */
ROLLBACK /* comment out if we want to allow all Office connections */
insert master.dbo.CheckLogins (UserName, ApplicationName, LoginTime, AllowedIn)
values (left(SUSER_NAME(), 60), left(APP_NAME(), 200), getdate(), 'No')
return;
end
else
begin /* user is allowed to bypass */
insert master.dbo.CheckLogins (UserName, ApplicationName, LoginTime, AllowedIn)
values (left(SUSER_NAME(), 60), left(APP_NAME(), 200), getdate(), 'Yes')
return;
end
end
else
/* if the application name does not match a name in the table... */
begin
return;
end
END
June 12, 2017 at 8:04 am
Hello all,
I ended up implementing the trigger below, and for the most part it seems to do exactly what we need it to do... but there is a wrinkle that has me kinda stumped. Every so often we're getting errors in the event log that a login was blocked by the trigger, when the login is explicitly ignored by the code. For example, the account "sqladmin" we have hardcoded in the trigger to abort. And yet rarely - it's only happened a handful of times over the past few months - we are seeing this login blocked. We've also seen NT AUTHORITY blocked when it is also explicitly ignored.
Does anyone have any ideas why this would occur? It seems pretty straightforward - if the login is X then return, which should exit the trigger and allow the login to continue.
Sorry for the vague question...
CREATE TRIGGER [tr_check_logins]
ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON
AS
BEGIN
declare @app varchar(200) = left(APP_NAME(), 200),
@user varchar(60) = left(ORIGINAL_LOGIN(), 60)
/* always allow server connections */
if @user like '%NT AUTHORITY%'
begin
return;
end
/* always allow sa connections */
if @user = 'sa'
begin
return;
end
if @user = 'domain\sqladmin'
begin
return;
end
if @user = 'domain\DBDEV1$'
begin
return;
end
/* if table does not exist, abort the trigger */
if object_id('Reporting.dbo.CheckLogins') is null
begin
return;
end
/* if table does not exist, abort the trigger */
if object_id('Reporting.dbo.CheckedApplications') is null
begin
return;
end
/* if table does not exist, abort the trigger */
if object_id('Reporting.dbo.CheckedApplicationUsers') is null
begin
return;
end
/* if table does not exist, abort the trigger */
if object_id('Reporting.dbo.CheckLoginsBlockConnections') is null
begin
return;
end
/* table can be updated to control blocking globally, 1 = block and log connections, 0 = allow and log connections */
declare @block bit = (select top 1 BlockLogins from Reporting.dbo.CheckLoginsBlockConnections)
/* is the app that is connecting in our list? */
declare @blockedApp bit = (select top 1 BlockLogins from Reporting.dbo.CheckedApplications (nolock) where @app like '%'+ApplicationName+'%')
/* does the user who is connecting have a row for the application? */
declare @blockedUser bit = 0
if not exists ( select * from Reporting.dbo.CheckedApplicationUsers (nolock) where @user = UserName and @app like '%'+ApplicationName+'%' )
begin
set @blockedUser = 1
end
/* if the application is one that we check... */
if @blockedApp = 1
begin
/* if the user is NOT allowed to bypass for the application, and @block = 1 we block the connection and log
if the user is NOT allowed to bypass for the application, and @block = 0 we allow the connection and log
*/
if @blockedUser = 1
and @block = 1
begin /* login is blocked and attempt is logged */
ROLLBACK
insert Reporting.dbo.CheckLogins (UserName, ApplicationName, LoginTime, AllowedIn, BlockingEnabled, AppBlockingEnabled, UserBlockingEnabled)
values (@user, @app, getdate(), 'No', @block, @blockedApp, @blockedUser)
return;
end
else
begin /* login is allowed but logged */
insert Reporting.dbo.CheckLogins (UserName, ApplicationName, LoginTime, AllowedIn, BlockingEnabled, AppBlockingEnabled, UserBlockingEnabled)
values (@user, @app, getdate(), 'Yes', @block, @blockedApp, @blockedUser)
return;
end
end
else
/* if the application is not in the list, connection is allowed and is not logged */
begin
--insert Reporting.dbo.CheckLogins (UserName, ApplicationName, LoginTime, AllowedIn, BlockingEnabled, AppBlockingEnabled, UserBlockingEnabled)
-- values (@user, @app, getdate(), 'Yes', @block, @blockedApp, @blockedUser)
return;
end
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply