Logon Trigger Not Acting As Expected
-
Hi All...Thanks in advance...This is my first shot at a logon trigger...
Here's my scenario...Users are logging into SQL using SSMS and a login they are not supposed to be using...ie...An application level login that has more rights than their individual logins...
I want to prevent logging in with that specified 'application' user from SQL Mgmt Console but want to allow the login it's normal access when ran by web page, application, etc...
Below I am trying to simulate by using a test user but allowing my workstation as the host...It does not let me login...
ie...The behaviour is the same...When I create the login trigger, it blocks that user regardless of conditions...
Here's the Actual Trigger (blocks that user login from ANYwhere):
CREATE TRIGGER
AppDeveloper_Unauthorized_Trigger
ON ALL SERVER WITH
EXECUTE AS 'AppDeveloper'
FOR
LOGON
AS
BEGIN
IF EXISTS
(
SELECT
sp.program_name
FROM
sys.SysProcesses sp (NOLOCK)
INNER JOIN
sys.SysDatabases sd (NOLOCK)
ON
sp.dbId = sd.dbId
WHERE
ORIGINAL_LOGIN() = 'AppDeveloper'
AND
(
sp.program_name = 'Microsoft SQL Server Management Studio'
OR
sp.program_name = 'Microsoft SQL Server Management Studio - Query'
)
)
ROLLBACK
END
Test Trigger:
This trigger also blocks all access...Regardless...I would think this SHOULD let me login from my host (DSBL4588)..
CREATE TRIGGER
LoginTriggerTest_Unauthorized_Trigger
ON ALL SERVER WITH
EXECUTE AS 'LoginTriggerTest'
FOR
LOGON
AS
BEGIN
IF EXISTS
(
SELECT
sp.loginame,sp.program_name, hostname
FROM
sys.SysProcesses sp (NOLOCK)
INNER JOIN
sys.SysDatabases sd (NOLOCK)
ON
sp.dbId = sd.dbId
WHERE
ORIGINAL_LOGIN() = 'LoginTriggerTest'
AND
(
sp.program_name = 'Microsoft SQL Server Management Studio'
OR
sp.program_name = 'Microsoft SQL Server Management Studio - Query'
)
AND sp.HostName <> 'DSBL4588'
)
ROLLBACK
END
Help is greatly appreciated...
Howard
-
howard i just tested this with a few different logons, and it seems to work; while i did not get the error message from the raiseerror stuff, it did prevent logins.
keep your window connected that you use to create this, since you might need to change or drop this trigger.
--Prevent access from SSMS
--drop TRIGGER logon_trigger_not_from_SSMS on all server
CREATE TRIGGER logon_trigger_not_from_SSMS
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Microsoft SQL Server%'
BEGIN
IF suser_name() IN ('Stormdev\Lowell','sa') --the only persons allowed to use SSMS, no sa allowed for testing
BEGIN
--only allowed from my host machine
IF host_name() !='STORMDEV'
BEGIN
RAISERROR('SSMS connections are restricted on to specific dba machines.', 16, 1)
ROLLBACK
END --host name check
END --suser_name check
ELSE
BEGIN
RAISERROR('SSMS connections are not permitted with this logon.', 16, 1)
ROLLBACK
END
END --app name check
ELSE
BEGIN
RAISERROR('SSMS connections are restricted on this server.', 16, 1)
ROLLBACK
END
END --trigger
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell...That fixed...I was on that path and had almost figured out...
One quick question...What would happen if you created a logon trigger like below...ie...Did not let anyone logon from SSMS...AND...Then closed window...
Obviously, no login would be able to login through SSMS/QueryAnalyzer, etc...
What would be the fix...???...???...(Reinstall SQL and restore dbs)...???...ie...Is there a backdoor safety valve...
CREATE TRIGGER
SSMS_Unauthorized_Login
ON
ALL SERVER
FOR
LOGON
AS
BEGIN
IF APP_NAME() = 'Microsoft SQL Server Management Studio' OR APP_NAME() = 'Microsoft SQL Server Management Studio - Query'
BEGIN
ROLLBACK
END --app name check
--ENDIF
END --trigger
Thanks again...
Howard
-
forget about the reinstall...just look at your rule: no ssms.....that's not preventing ALL connections, so you could change your application, which IS allowed to connect , but i'd do it this way: connect with query analyzer from SQL 2000, or with LinqPad, and then drop or disable the trigger.
;
you could even write an application to connect as well.
if the trigger did prevent all connections, there is a trace flag you can add to SS on startup to disable all server triggers also.
btw, linqpad is really nice to carry on your flash drive to someones desk who does not have SSMS; it's portable with no dependancies, has got a lot of features like an object explorer and multi-query tabs, and it's free.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
here's an article that does explains what we were talking about: total lockout due to a trigger:
http://www.pythian.com/news/1310/sql-server-troubleshooting-logon-triggers/
seems if you use the dedicated admin connection(DAC), you are also in without the trigger stopping you, besides that startup param -T7806
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Howard.Veazey (5/24/2010)
FROMsys.SysProcesses sp (NOLOCK)
INNER JOIN
sys.SysDatabases sd (NOLOCK)
Since you're using SQL 2005, rather use sys.databases and sys.dm_exec_sessions. Both sysdatabases and sysprocesses are deprecated and will be removed in a future version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Lowell...
You Rock...!!!...
That's kinda what I thought on the connections...I was testing on my local machine with SSIS connecting and no issues...We have a 'mgmt' type server that I'm gonna create an SSIS package to delete the triggers...
Thanks for the heads up on the LinqPad tool...I'm gonna download and do just what you suggested...
BTW...On the RAISEERROR that you originally said was not firing...It won't...The reason is the logon trigger fires *after* authentication but *before* actual connection, so it can't fire...
I really appreciate all the references and suggestions...I had kinda arrived at the exact same conclusions but since this was a really new area of SQL to me, it REALLY helped by enforcing that I was on the right track and just needed to dig a little deeper to figure out...
Thanks again and God Bless...
Howard...
-
Thanks to you also GilaMonster...
-
Howard.Veazey (5/25/2010)
BTW...On the RAISEERROR that you originally said was not firing...It won't...The reason is the logon trigger fires *after* authentication but *before* actual connection, so it can't fire...It will fire.
The error won't be returned to the client, because there's no connection at that point, but it will fire. The message from that RAISERROR will go into the SQL error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Nice...This will be a soooperb thread for anyone needing info on logon triggers...
SQLServerCentral Roxxx...
-
DerBeDer...
To support Gail...
Straight from Logon Trigger SQL BOL (which is exactly what Gail's post demonstrated)...
" Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails. "
So, my response to Lowell was not correct...
I'm providing this just for completeness so anyone reading this thread will get a thorough understanding...
Also be aware that logon triggers are for *ALL* logins...
Thanks again to Lowell & Gail for posts...
HLV
"Guess who just got back today..."
"Them wild eyed boys who'd been away..."
"Hadn't changed had much to say..."
"But, man I still think dem catz wuz crazee..."
-
DerBeDer...
To support GilaMonster aka Gail...
Straight from Logon Trigger SQL BOL (which is exactly what Gila's post demonstrated)...
" Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails. "
So, my response to Lowell was not correct...
I'm providing this just for completeness so anyone reading this thread will get a thorough understanding...
Also be aware that logon triggers are for *ALL* logins...
Thanks again to Lowell & Gail for posts...
HLV
"Guess who just got back today..."
"Them wild eyed boys who'd been away..."
"Hadn't changed had much to say..."
"But, man I still think dem catz wuz crazee..."
-
Here is a good article on implementing logon trigers, including how to troubleshoot the scenario where you accidentally block all accounts from logging in due to a bug in the logic.
http://www.pythian.com/news/1310/sql-server-troubleshooting-logon-triggers/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply