September 8, 2015 at 10:35 pm
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
END;
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch him. Let me know how can I do this, if I use insert command it rollsback everything and I can't do any activity.
Shamshad Ali
September 8, 2015 at 11:00 pm
shamshad.ali (9/8/2015)
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer]ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
END;
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch him. Let me know how can I do this, if I use insert command it rollsback everything and I can't do any activity.
Shamshad Ali
Why not disable or drop the login and just log failed attempts?
😎
September 9, 2015 at 3:13 am
Eirikur Eiriksson (9/8/2015)
Why not disable or drop the login and just log failed attempts?😎
Because doing so would prevent him from logging in entirely, whereas the trigger prevents him from connecting from Management Studio
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
September 9, 2015 at 3:27 am
Few comments on the trigger first.
The execute as. Typically you'd have something like 'execute as sa', to ensure that the login trigger has enough permissions to write logging info away, not have it execute as the login that it's preventing access.
Application names can be spoofed. It's pretty easy for me to connect from Management Studio and have the program_name show up as .Net SqlClient Data Provider, or ODBC or anything else.
Try doing the insert into the logging table after the rollback. You do need to ensure that the login that the trigger is executing under has permissions to the logging table.
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
September 9, 2015 at 3:30 am
GilaMonster (9/9/2015)
Eirikur Eiriksson (9/8/2015)
Why not disable or drop the login and just log failed attempts?😎
Because doing so would prevent him from logging in entirely, whereas the trigger prevents him from connecting from Management Studio
That implies that he can connect by other means (not mentioned in the OP).
Since it's trivial to change the program name, this may not be the most secure approach.
😎
September 9, 2015 at 3:40 am
Eirikur Eiriksson (9/9/2015)
That implies that he can connect by other means (not mentioned in the OP).
Such as the application which the user uses to do their job.
Since the request is specifically says 'in case the user connects through SSMS', not a stretch to conclude they should be allowed in via other methods.
Since it's trivial to change the program name, this may not be the most secure approach.
No, but it's a good start, and add some additional monitoring of when the user connects via something else and it's better. If the user explicitly spoofs their application, then it's a good indication they're up to something they shouldn't be. That's been grounds for dismissal in some places I've worked, since the user can't say 'I didn't know that I wasn't supposed to use SSMS'
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
September 9, 2015 at 11:58 am
i used your given suggestion,
Alter TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
insert into [TestQuestions].dbo.tllog (Comment) values ('Smith tried to login')
END;
But on very first attempt by smith login from SSMS, it fires 10 times, I mean when I query on Log table, It is showing 10 rows .... I want to have it once only. Incase of email to me, it is sending email 10 times.
Shamshad
September 9, 2015 at 12:07 pm
Please never mind, I resolved it by simple logic below:
Alter TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
Declare @Found bit = 0
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
BEGIN
set @Found = 1
ROLLBACK;
END
end
If @Found = 1
insert into [TestQuestions].dbo.tllog (Comment) values ('Smith tried to login')
END;
Up till now, its working as desired.
Thanks for your help Gail, You are always my guru.
September 9, 2015 at 3:35 pm
See my earlier comments about spoofing the program name. If you suspect that this person may know how to do that (or might use google to find out how), then log all his login attempts along with the program name and review it (or have information security review it).
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply