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
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.
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
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?
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
I know this reply is about 2 years too late, but it may help someone else. By default, DAC is disabled with the express version. SQL server needs to be restarted with startup flag 7806. https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators?view=sql-server-2017
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.
Very Very Thanks Sir…………..
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.
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.
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?
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
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
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 ?
Thank goodness for your instructions on how to drop triggers via “DAC.” I almost had a heart attack.
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
Pinal,
You really ROCKS! I was facing the issue, just got a link from your blog and get confidence that the issue has been fixed.
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.
I had tried via DAC and command prompt, but it says “Login failed for user Administrator”
What should i do now??
Please help asap.
Regards!
Bundle of Thanks…
Rashad – Thanks for the comment.
Dave, as always this is useful info. However I did not have DAC enabled, and as I read the responses, neither did some other users. What I had to do was put the sql service in single user mode (-m) and restart the service, then use sqlcmd to disable the trigger. Hope this helps.
At certain cases, like mine you will never be able to enable DAC. In that case please follow the steps below:
1. Stop the SQL service.
2. Switch to single user mode ” c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Binn\sqlservr.exe -m -s SQLExpress -c -f ”
3. In new command prompt Login to the sql server ” sqlcmd -S (local)\SQLExpress -d master -E -X ”
4 . Execute the following:
1> drop trigger triggername on all server;
2> go
1> exit
5. Precc Ctrl-C on SQL Server console, and answer Y (close it).
6. Start SQL Server service.
Note: One thing missing this is now to list the active trigger. If any one know how to do that, please add it here and this will be complete.
–Enjoy.