SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

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 (http://blog.SQLAuthority.com)

About these ads

40 thoughts on “SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

  1. 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.

    Like

  2. 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

    Like

  3. 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

    Like

    • 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.

      Like

  4. hm, my ‘objectID’ text was deleted, maybe because I used < > instead of & lt; & gt; …so the correct phrase is:

    SELECT object_definition( < objectID > )
    GO

    Like

  5. 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.

    Like

  6. 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.

    Like

  7. 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.

    Like

  8. 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

    Like

  9. 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!

    Like

  10. 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………….

    Like

  11. 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.

    Like

  12. I´ve got the same problem after firing the trigger “ON ALL SERVER” today. Your solution was excellent. It´s really fix it. Thanks a lot.

    Like

  13. Hi Pinal

    Thanks for this, I am getting the same error on my sql server logs,

    Date 3/26/2012 2:39:39 PM
    Log SQL Server (Current – 3/25/2012 1:28:00 AM)

    Source Logon

    Message
    Logon failed for login ‘Mydatabase’ due to trigger execution.

    I have checked and done with the steps you have given above, but there is no server trigger present on master database.

    Can you please help me finding the issue here.

    Regards
    Rohit Malik

    Like

  14. Hi Pinal, This post as a lifesaver.. I ran into the same problem on a production database and almost thot I had to stay the night to get the database back up!

    I have 1 qn tho.. The circumstance through which I arrived at this same error was a bit different from what you described. I got this problem just after creating a logon trigger which does similar to what your logon trigger in this post does. No database was deleted. I encountered the error right after creating the trigger and attempting to logon to the database to test the audit. Any ideas why this is the case?

    Like

  15. Hi Pinal,

    I have an even worse accident. I get the message:
    “C: \ Documents and Settings \ George> sqlcmd-S surf \ SQLEXPRESS-d master-A
    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Server Network Interfaces: An error occurred while Obtaining the dedicated and
    dministrator connection (DAC) port. Make Sure That SQL Browser is running, or ch
    eck the error log for the port number [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0: A network-related or in
    stance-specific error has occurred while Establishing a connection to SQL Server
    . Server is not found or not accessible. Check if instance name is correct and i
    f SQL Server is configured to allow remote connections. For more information see
    SQL Server Books Online ..
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0: Login timeout expired. ”
    I can not connect in any way with the server.
    It also finds you the solution!
    Regards

    Like

  16. Thanks David you have saved my life or my job I don’t know jajajajaj, this is the only site with the correct answer to this problem.

    Like

  17. Hi Pinal,

    Great article, unfortunaately, i also have a similar issue to Jan, above. if you or anyone else can provide any assistance it would be greatly appreciated. This is the only site I have found that has been in any way useful.

    Many thanks for a great resource.
    ====================================

    Error message as below:

    E:\SQLDATA\MSSQL10_50.SQLSERVER01\MSSQL\Binn>SQLCMD -s SRV-sql-01 -d master -A
    HResult 0x274D, Level 16, State 1
    SQL Server Network Interfaces: Could not establish dedicated administrator connection (DAC) on default port. Make sure that DAC is enabled [10061].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

    Like

  18. Should have added to the above, I have also tried the command with LocalHost server name. If I can’t resolve this issue, I guess I’m going to have to uninstall SQL and start from scratch which is along way round restoring the master database, re-attaching my databases as and when re-installed?

    Thanks again for any and all responses. it’s greatly appreciated.

    Like

  19. Hi Pinal,

    many thanks for your great articles. One question: Why you don’t handle the worst case in the trigger itself by design:
    IF OBJECT_ID(‘AuditDb.dbo.ServerLogonHistory’, ‘U’) IS NOT NULL
    INSERT INTO AuditDb.dbo.ServerLogonHistory
    SELECT SYSTEM_USER,USER,@@SPID,GETDATE()

    With this check works the login, even if the table is not available. But is this a performance issue?

    Like

  20. Hi Pinal,
    Having fallen into this trap, I would like to ask why a better solution has not been provided? We require such a trigger as SQL Server 2008 Standard Edition doesn’t have the new Audit feature enabled.
    Many Thanks
    D

    Like

  21. i tried this login trigger in sqlexpress. now i can’t get over from it using command line.
    is there any solution for sql express.
    I tried >sqlcmd -S localhost\sqlexpress -d master -A

    the error i got in command line is

    HResult 0xFFFFFFFF: An error occurred while obtaining the DAC port.
    And login timeout expired

    Like

  22. Thanks, It worked but i have noticed that after removing the trigger and the table, the database response has become slower than before. any ideas ?

    Like

  23. Dave….your a life saver.. gods truth….I accidentally dropped the table the logon trigger was writing to…so it left me totally locked out as it kept on failing on the write…mad panic set in…trawled the web…you got it on the button…thx

    Like

  24. This article saved my behind today. Because of an error in the script, the table was not created ,but the trigger was. As expected all applications lost connection to db.

    Like

  25. Pingback: SQL SERVER – Restricting Access to Contained Databases using Logon Triggers | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s