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
An oldie but goodie. Thank you for this information as it came in very handy. And also to others that added more good information.
Thanks MAN !!!!
Thank you very much for your posts.
My pleasure. I am glad that you are liking it Ibrahim!
I have been getting multiple rows for single login is there any solution to get only one row for one login
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
Thanks a lot
Thanks Sanjay.I really struggled a lot to resolve this problem.Your solution eased my work
Thank you ..
Thank you Sanjay & Pinal for such a nice article. I am getting the same error and resolved with the help of this article.
I have the same error … but is ocasional and only with a single user … ?¡?¡?
and what do you see in ERRORLOG and profiler?
merci beaucoup, vous m’avez sauvé
Votre accueil !
Thank you so much!
Your welcome!
Thanks a lot Bro !!! Much appreciated :) :) :)
Yo Bro!
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
First, you need to check Pinal’s another blog.
https://blog.sqlauthority.com/2011/10/23/sql-server-dedicated-access-control-for-sql-server-express-edition-an-error-occurred-while-obtaining-the-dedicated-administrator-connection-dac-port/
Then you need to execute command in this format only (sequence does matter)
sqlcmd -A -d master -S .SQLEXPRESS
This below command doesn’t work.
sqlcmd -s .SQLEXPRESS -d master -A
Thank you so much!
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/
saved the day , thank you so much
Thanks Wael.
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
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
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!
no. You cant. For Security Reasons.
Thanks, Pinal