July 20, 2019 at 5:22 pm
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
July 21, 2019 at 11:12 am
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_disabled
FROM 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
Lowell
July 21, 2019 at 12:42 pm
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
July 21, 2019 at 2:44 pm
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_disabled
FROM 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
Lowell
July 21, 2019 at 4:23 pm
Let me try this. If work I will share/ info here back
July 22, 2019 at 2:18 pm
its work! exactly like you said above
thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply