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.

Solarwinds
/* 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)

SQL SERVER - Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to 'master'. errortrigger

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

SQL SERVER - Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to 'master'. sqlcmddac

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution
Next Post
SQL SERVER – SQL Server Management Studio New Features

Related Posts

77 Comments. Leave new

  • An oldie but goodie. Thank you for this information as it came in very handy. And also to others that added more good information.

    Reply
  • Thanks MAN !!!!

    Reply
  • Thank you very much for your posts.

    Reply
  • I have been getting multiple rows for single login is there any solution to get only one row for one login

    Reply
  • Hi Pinal,

    I am in unusual situation now!

    I was doing DB restore test on my test server from production . When I restore one of our production database I got this problem “SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master”

    After running your query on command prompt, I got below error.

    Cannot drop the trigger ‘Tr_ServerLogon’, because it does not exist or you do not have permission.

    I have SA permissions

    How to fix this prob , Please suggest

    Kind Regards

    Braj

    Reply
  • Thanks a lot

    Reply
  • Thanks Sanjay.I really struggled a lot to resolve this problem.Your solution eased my work

    Reply
  • Thank you ..

    Reply
  • kasi Viswanath
    October 20, 2016 4:18 am

    Thank you Sanjay & Pinal for such a nice article. I am getting the same error and resolved with the help of this article.

    Reply
  • I have the same error … but is ocasional and only with a single user … ?¡?¡?

    Reply
  • merci beaucoup, vous m’avez sauvé

    Reply
  • Thank you so much!

    Reply
  • Thanks a lot Bro !!! Much appreciated :) :) :)

    Reply
  • Hey I tried your solution but it is saying :

    C:\Users\bhavi>sqlcmd -S LocalHost -d master -A
    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF]. .
    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : 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..

    SQL Browser is also running…I don’t know whats going wrong but I really need to fix this. Its really IMPORTANT
    THANKS IN ADVANCE

    Reply
  • Message means that SQLCMD is connecting to wrong instance of SQL. can you please check?

    if this is IMPORTANT, please look for forums support as I may not be able to reply on time every time. Or https://blog.sqlauthority.com/hire-me/

    Reply
  • saved the day , thank you so much

    Reply
  • Hello Pinal, i tried this but im still getting error as follow

    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF]. .
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
    Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : 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..

    Please Help and inform me how to fix this error as i am the beginner and i was learning SQL server from tube videos and now i can not learn anymore as i can not login, your help will be much appreciate. Thank you

    Reply
  • USE [master]
    GO

    /****** Object: DdlTrigger [TRG_Block_ManagamentStudio] Script Date: 9/4/2017 9:22:56 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER [TRG_Block_ManagamentStudio]
    ON ALL SERVER WITH EXECUTE AS ‘sa’
    FOR LOGON
    AS
    BEGIN

    DECLARE @data XML
    SET @data = EVENTDATA()

    DECLARE @AppName sysname
    ,@LoginName sysname
    ,@LoginType sysname
    ,@HostName sysname

    SELECT @AppName = [program_name]
    FROM sys.dm_exec_sessions
    WHERE session_id = @data.value(‘(/EVENT_INSTANCE/SPID)[1]’, ‘int’)

    SELECT @LoginName = @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘sysname’)
    ,@LoginType = @data.value(‘(/EVENT_INSTANCE/LoginType)[1]’, ‘sysname’)
    ,@HostName = @data.value(‘(/EVENT_INSTANCE/ClientHost)[1]’, ‘sysname’)

    IF @AppName in (‘Microsoft SQL Server Management Studio’, ‘Microsoft SQL Server Management Studio – Query’) AND @LoginType = ‘SQL LOGIN’ AND @LoginName != ‘sa’
    BEGIN
    ROLLBACK; –Disconnect the session
    –Log the exception to our Auditing table
    INSERT INTO master.dbo.LogonAudit(PostTime, SPID, LoginName, ClientHost, AppName, LoginType)
    SELECT convert(datetime,@data.value(‘(/EVENT_INSTANCE/PostTime)[1]’, ‘sysname’))
    ,convert(int, @data.value(‘(/EVENT_INSTANCE/SPID)[1]’, ‘sysname’))
    ,convert( varchar(20), @data.value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘sysname’))
    ,convert(varchar(20), @data.value(‘(/EVENT_INSTANCE/ClientHost)[1]’, ‘sysname’))
    ,@AppName
    ,convert(varchar(10), @data.value(‘(/EVENT_INSTANCE/LoginType)[1]’, ‘sysname’))
    END
    END;

    GO

    ENABLE TRIGGER [TRG_Block_ManagamentStudio] ON ALL SERVER
    GO

    Reply
  • Hi Pinal!!

    Can I customize the message “Logon failed for login ‘USER_X’ due….”,
    with something like ‘You can not use this user for MSSQL’ ??

    Your help will be appreciate. Thank you!

    Reply
  • Thanks, Pinal

    Reply

Leave a Reply

Menu