I had previously written two articles about an intriguing observation of triggers online.
SQL SERVER – Interesting Observation of Logon Trigger On All Servers
SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution
If you are wondering what made me write yet another article on logon trigger then let me tell you the story behind it. One of my readers encountered a situation where he dropped the database created in the above two articles and he was unable to logon to the system after that.
Let us recreate the scenario first and attempt to solve the problem.
/* Create Audit Database */ CREATE DATABASE AuditDb GO USE AuditDb GO /* Create Audit Table */ CREATE TABLE ServerLogonHistory (SystemUser VARCHAR(512), DBUser VARCHAR(512), SPID INT, LogonTime DATETIME) GO /* Create Logon Trigger */ CREATE TRIGGER Tr_ServerLogon ON ALL SERVER FOR LOGON AS BEGIN INSERT INTO AuditDb.dbo.ServerLogonHistory SELECT SYSTEM_USER,USER,@@SPID,GETDATE() END GO /* Dropping Database AuditDB */ /* Please note login to Server again will Produce Errors */ USE master GO DROP DATABASE AuditDB GO
After the database is dropped and we try to login again the following error will be displayed.
Logon failed for login ‘SQL\Pinal’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
This error could have been evaded if the Trigger was dropped prior to dropping database.
USE master GO DROP TRIGGER Tr_ServerLogon ON ALL SERVER GO
Now, it will not be possible to logon to the database using SQL Server Management Studio. The only way to fix this problem is using DAC. Read more details about DAC Using a Dedicated Administrator Connection to Kill Currently Running Query. Now let us take a look at the example in our case where I am using windows authentication to logon to the system.
Connect SQL Server using sqlcmd and -A option, which will establish DAC. Running the following command on command prompt will allow you to login once again in the database. I am specifying argument -d master which will directly enable me to logon to master database when DAC is connected.
C:\Users\Pinal>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER Tr_ServerLogon ON ALL SERVER
2> GO
I hope my explanation on logon triggers, DAC, and sqlcmd is clear to everyone. Let me have your thoughts about my present article.
Reference : Pinal Dave (https://blog.sqlauthority.com)
81 Comments. Leave new
Good article, however this fix will not work for me. I am receiving the error on a cluster instance. Remote DAC is disabled and I can’t connect to the instance locally as the cluster node doesn’t see any of the instances as local.
Any advice / work-around would be most appreciated.
Thanks a lot! great help.
Firing these types of triggers or procedures should also be instructed to run the fix.
Thanks again.
Hi Pinal,
Thank you for such a nice article. While reading this I am puzzling with one question. Is it possible to track INVALID LOGINs to Sql Server. I mean if there is a Login attempt to Sql Server with incorrect login details, then a that should be recorded into one database table?
Please suggest.
Thanks Again
Thanks Pinal , It solved my issue.
Holy Crap… that was a life saver!!!! One of those where I thought I was in for it! Thanks….
hi Dave i m getting the same error but i dnt no the trigger name where i can drop the trigger please let me where i can find this trigger as i m enable to find out the trigger name
You may have to select from sys.server_triggers table. I mean the statements you should use at sqlcmd are the followings:
SELECT * FROM sys.server_triggers
GO
You can use the statement below to make sure what you will drop
SELECT object_definition()
GO
where the objectID is the object_id you got from the select and whose code you want to see.
hm, my ‘objectID’ text was deleted, maybe because I used < > instead of & lt; & gt; …so the correct phrase is:
SELECT object_definition( < objectID > )
GO
Hi,
I am getting the same error but it did not work for me. But below steps resolve my problem.
1.Connect SQL Server using sqlcmd go to run type cmd. You can get the command prompt. Now type below command:
sqlcmd -S LocalHost -d master –A
2.Now search for trigger :
SELECT * FROM sys.server_triggers
GO
Found the trigger i found the trigger “tddl_limitconnections”
3. Than i drop the trigger.
drop trigger tddl_limitconnections on all server
GO
Now i am able to login using SQL Server Management Studio.
It resolves my problem may be this resolves yours too. If the triggers are not same please find trigger first than replace with tddl_limitconnections trigger it may resolve your issue.
Thanks.
SQL Server
DAC is not connecting to remote server databse
Error: Login timeout expired
make sure that dac is enabled …
error occured while establishing connection
i have to kill trigger becuase of this my work is stopped please help me.
Thanks for your explanation
Mega Thanks! Along with Terry I thought I was really in for it when this error occurred and when I read further about the script that I ran… well it was a little too late! You most definately saved my bacon and made my day. Thanks, thanks and again thanks.
Thanks for your explanation
C:\Documents and Settings\dove165>sqlcmd -s ghdb78 -e -A -q “Disable trigger tr
igLogon_CheckForSysAdmin ON ALL SERVER”
Disable trigger trigLogon_CheckForSysAdmin ON ALL SERVER
1>
worked like magic for me too
Pinal,
You have excellent information. I was bombed with a similar error: Logon failed for (user) due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892) for over 30 hrs.
Tried SQLCMD, and DAC none would work for me. However, today decided to search your forum and thank God, the solution was right there.
It’s a miracle!
Hi pinal,
My database contains 2200 stored procedure,the developers didnt include SET NOCOUNT ON to all the stored procedures…
Is there any options to activate SET NOCOUNT ON to whole database or server?
if it is there please tell me how to do this or otherwise i have to do manually………….
hi, as u mentioned above how to use sqlcmd on real mode …….
Question: Would using login triggers cause performance issue in applications.
I am trying to implement login trigger that would restrict the SQL account used by application to login from particular host only.
This would avoid other instance of same application to accidentally connect to production instance.
Could you please let me know what are the pros and cons.
Hi Pinal,
That is a very good information you gave in this article. :)
Thanks,
Varun
Wonderful article i messed my servers and was really clueless what to do next but ur article and mainly DAC inplementation saved my life thanks a lot