Search code, repositories, users, issues, pull requests…
Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
I developed a .NET program to connect to a SQL Server 2088 Express edition and obtain data. That program runs automatically when Windows starts up, and gives me the error:
Error code 17892 Logon failed for login ‘DOMAIN\user’ due to trigger
execution. [CLIENTE: local machine]
After searching the internet, I found a few recommendations. Some said to drop a trigger, but I don´t have any trigger in my databases. Others say it is a problem with the user’s permissions, but I can access this through SQL Server Management Studio using this user (the Windows user) with no problem.
If I launch my application after opening SQL Server Management Studio the application works fine. If I execute 2 times consecutively, the first fail but the second Works fine.
I configured SQL Server for only Windows authentication, and have the auto_shutdown option of the database off.
This is my connection string:
«Data Source=localhost\SQLExpress; Initial
Catalog=mydb;Trusted_Connection=True;Connection Timeout=150»;
Any idea why I’m getting this error?
EDIT: I PUT ERROR LOG to see login attempts
Recovery is complete. This is an informational message only. No user action is required.
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Starting up database 'mydb'.
Error: 17892, gravedad: 20, estado: 1.
Logon failed for login 'DOMAIN\myuser' due to trigger execution. [CLIENTE: <local machine>]
Second attempt(this time Works):
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
Using 'xpstar.dll' version '2007.100.5500' to execute extended stored procedure 'xp_enumerrorlogs'. This is an informational message only; no user action is required.
And after i log through SSMS:
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Starting up database 'mydb2'.
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Login succeeded for user 'DOMAIN\myuser'. Connection made using Windows authentication. [CLIENTE: <local machine>]
Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
Using 'xplog70.dll' version '2007.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
I have been trying to login using one of my sql server logins but I am getting the error message below:
General Error Message
Copy Message Text
TITLE: Connect to Server ------------------------------ Cannot connect to MY_SERVER. ------------------------------ ADDITIONAL INFORMATION: Logon failed for login 'cola' due to trigger execution. Changed database context to 'master'. Changed language setting to us_english. (Microsoft SQL Server, Error: 17892) For help, click: http://go.microsoft.com/fwlink?> ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------
Advanced Information
I am aware of the question below but it is slightly different and I have tried everything that is said there and it did not work for me, that’s why I am putting this question here:
“Logon failed for login ‘sa’ due to trigger execution.” When No SA Login Triggers Defined
This question here is very similar too:
Login failed for user — Error: 18456, Severity: 14, State: 38
And from this one I got the following info:
Login failed for user — Error 18456 — Severity 14, State 38
1 'Account is locked out'
2 'User id is not valid'
3-4 'Undocumented'
5 'User id is not valid'
6 'Undocumented'
7 'The login being used is disabled'
8 'Incorrect password'
9 'Invalid password'
10 'Related to a SQL login being bound to Windows domain password policy enforcement.
See KB925744.'
11-12 'Login valid but server access failed'
16 'Login valid, but not permissioned to use the target database'
18 'Password expired'
27 'Initial database could not be found'
38 'Login valid but database unavailable (or login not permissioned)'
According to the information above I have the following issue:
Login valid but server access failed
Still no joy.
I tried to drop the login and re-create it, but encountered a
Login can not be dropped because it is in use.
I used the script below to find out where it is used, by whom, from where:
USE master
go
SELECT
sdes.session_id
,sdes.login_time
,sdes.last_request_start_time
,sdes.last_request_end_time
,sdes.is_user_process
,sdes.host_name
,sdes.program_name
,sdes.login_name
,sdes.status
,sdec.num_reads
,sdec.num_writes
,sdec.last_read
,sdec.last_write
,sdes.reads
,sdes.logical_reads
,sdes.writes
,sdest.DatabaseName
,sdest.ObjName
,sdes.client_interface_name
,sdes.nt_domain
,sdes.nt_user_name
,sdec.client_net_address
,sdec.local_net_address
,sdest.Query
,KillCommand = 'Kill '+ CAST(sdes.session_id AS VARCHAR)
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec
ON sdec.session_id = sdes.session_id
CROSS APPLY (
SELECT DB_NAME(dbid) AS DatabaseName
,OBJECT_NAME(objectid) AS ObjName
,COALESCE((
SELECT TEXT AS [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
FOR XML PATH('')
,TYPE
), '') AS Query
FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
) sdest
WHERE sdes.session_id <> @@SPID
--ORDER BY sdes.last_request_start_time DESC
I then killed the 2 sessions, re-created the login, tried again, same error.
What am I missing?
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)
Related Posts
Hi,
I’ve been struggling for 5 days to solved this issue.
We have a PHP/MySql application. In the final steps, we need to post data to MsSql server. We are using mysql connect.
Everytime we run our api and try to login, its Failed.
The error show -> Error: 17892 Severity: 20 State: 1
Logon failed for login 'sysCB5' due to trigger execution.
Error: 17892<c/> Severity: 20<c/> State: 1.
Our findings shown its related to trigger, but when we query for trigger, there is no trigger listed. been looking around for solution and none solved.
From some resources, it ask to access DAC which we did and cannot access
sqlcmd -S 127.0.0.1 -U sa -P dev -d master -A
Kindly help
Lowell
SSC Guru
Points: 323498
on the SQL server itself, someone has created a logon trigger on all server.
that trigger is preventing the login…it might be doing it on purpose, or it might be an artifact of bad coding or insufficient permissions(ie writing to a table). you need to get with the DBA and tell him it is preventing the logins, and he can disable the logon trigger; if it is you, then disable it yourself:
DISABLE TRIGGER TR_LogonTriggerAuditingName ON ALL SERVER-- change to the REAL name of the trigger
you can make a logon trigger that might prevent a login any of the following things: track a history of logins, prevent logins due to explicit loginname, time of day, or number of connections, hostname, application name not in expected values, for example.
if you are sysadmin on the server, you can see the list of server triggers and their definition like this:
Object Explorer -> Server Objects -> Triggers
or via TSQL:
SELECTtrz.name,
'DISABLE TRIGGER '
+ QUOTENAME(trz.name) COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' ON ALL '
+ trz.[parent_class_desc] COLLATE SQL_Latin1_General_CP1_CI_AS AS DisableCommand,
ssmod.definition AS [Definition],
trz.parent_class_desc,
trz.create_date,
trz.modify_date,
trz.is_disabledFROM master.sys.server_triggers AS trz
LEFT OUTER JOIN master.sys.server_assembly_modules AS modz ON modz.object_id = trz.object_id
LEFT OUTER JOIN sys.server_sql_modules AS ssmod ON ssmod.object_id = trz.object_id
- This reply was modified 4 years, 2 months ago by Lowell.
- This reply was modified 4 years, 2 months ago by Lowell.
Lowell
—help us help you! If you post a question, make sure you include a CREATE TABLE… statement and INSERT INTO… statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
ismahar
SSC Enthusiast
Points: 175
Hi Lowell,
Thanks for your reply. We already try to DROP and DISABLE the trigger but it seems no trigger running. I attached the Screenshot.
We also try using DAC with admin login, but cannot access. We plan to run below
USE master
GO
DROP TRIGGER Tr_ServerLogon ON ALL SERVER
GO
Attachments:
You must be logged in to view attached files.
Lowell
SSC Guru
Points: 323498
are you sure you are sysadmin on the server? the error implies either the spelling of the trigger is incorrect, or you do not have permissions.
if you have view any database permissions, this would generate the disable commands for you:
SELECTtrz.name,
'DISABLE TRIGGER '
+ QUOTENAME(trz.name) COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' ON ALL '
+ trz.[parent_class_desc] COLLATE SQL_Latin1_General_CP1_CI_AS AS DisableCommand,
ssmod.definition AS [Definition],
trz.parent_class_desc,
trz.create_date,
trz.modify_date,
trz.is_disabledFROM master.sys.server_triggers AS trz
LEFT OUTER JOIN master.sys.server_assembly_modules AS modz ON modz.object_id = trz.object_id
LEFT OUTER JOIN sys.server_sql_modules AS ssmod ON ssmod.object_id = trz.object_id
- This reply was modified 4 years, 2 months ago by Lowell.
Lowell
—help us help you! If you post a question, make sure you include a CREATE TABLE… statement and INSERT INTO… statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
ismahar
SSC Enthusiast
Points: 175
Let me try this. If work I will share/ info here back
ismahar
SSC Enthusiast
Points: 175
its work! exactly like you said above
thanks