Error: 17892 Severity: 20 State: 1

  • ismahar

    Valued Member

    Points: 65

    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: 323438

    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:

    SELECT	trz.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

    • This reply was modified 4 months, 2 weeks ago by  Lowell.
    • This reply was modified 4 months, 2 weeks 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

    Valued Member

    Points: 65

    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: 323438

    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:

    SELECT	trz.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

    • This reply was modified 4 months, 2 weeks 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

    Valued Member

    Points: 65

    Let me try this. If work I will share/ info here back

  • ismahar

    Valued Member

    Points: 65

    its work! exactly like you said above

    thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply