Error conecting because of Login Trigger

  • Hello,

    The following error appears after i've created one login trigger.

    sqlcmd -S localhost

    Msg 17892, Level 14, State 1, Server Hostname\Instance_name, Line 1

    Logon failed for login 'Domain\username' due to trigger execution.

    I dont have any idea how can i revert this situation.

    Can anyone help me, PLEASEEEEE.......

    Thanks and regards,

    JMSM 😉

  • Connections via the DAC don't fire login triggers. From management studio, open a new query and specift admin: before the server name (admin:MyServerName) and use windows authentication. You must be sysadmin.

    If remote DAC hasn't been enabled (the default), you will have to do that from a querying tool on the server itself. If the server doesn't have management studio, you can use SQLCMD. Specify the -A switch

    Once in, you can disable the trigger.

    DISABLE TRIGGER MyBrokenLoginTrigger ON ALL SERVER

    Be very, very careful when writing login triggers. If, for any reason, the trigger fires an error of Sev 16 or higher (object does not exist, database not found, permission denied) the trigger fails and rolls back the login.

    I've had a couple panicked, late-night phone calls because of these.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Like this 'sqlcmd -S localhost -U sqlumssrv -A'

    The point is that i've got a named instance and i'm not sure what command should i use.

    Thanks and regards

    JMSM 🙂

  • Off hand, I don't know. What does Books Online say? 😉

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Looks like this is the syntax. Can't test. Don't have any named instances around.

    If you're using windows authentication

    sqlcmd -S servername\instancename -E -A

    If you're using SQL authentication

    sqlcmd -S servername\instancename -U username -P password -A

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gila,

    I've try the following command but still get this error. when i use the disable trigger command i've go

    t to put the name that i use when i create the logon trigger, right?

    sqlcmd -S PJTUMS06\SQLUMS_T1 -q "disable trigger AuditLogin_Profiler on all servers" -U sqlumssrv -A

    Password: Msg 18456, Level 14, State 1, Server PJTUMS06\SQLUMS_T1, Line 1

    Login failed for user 'sqlumssrv'.

    Thanks and regards,

    JMSM 😉

  • First thing is to log in. Just try the sqlcmd without any -q and see if you can get a connection to the server. If not, is it saying that the transaction was rolled back in the trigger, or is it just saying that login failed.

    Once you're in, it's easy to get the trigger name from the sys.server_triggers view

    Also, the syntax is

    ON ALL SERVER, not ON ALL SERVERS

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi Gila,

    Thanks a lot.... like i tell u any time u be usefull, hope one day have........ so knowledge as u ;o)

    Thanks and regards 😉

    JMSM

  • Did you come right then?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • To piggy-back on what Gail has said, you can use any login which is a member of the sysadmin fixed server role (such as SA, but also your Windows account if it has such rights). Once in your the DAC, you can disable the login trigger. That's the only way in.

    K. Brian Kelley
    @kbriankelley

  • Starting SQL with -f works too. Had to do that once with a failed login trigger on a cluster. The DAC was set local only.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i get the same thing. i created a trigger with the code below and have on row in the table i want to use as a blacklist.

    we are getting ready for SOX and need to lock devs out of production databases. problem is that it's easy to figure out the encrypted passwords for the logins and they insist on accessing production, even if it's for troubleshooting purposes.

    plan will be on each production server to create a table with hostnames, sql logins and app names and then create login triggers that will fire only in very specific conditions.

    copied the code below from BOL and modified just a bit. problem is that when i run it the trigger locks everyone out and i have to go through the DAC to disable it.

    CREATE TRIGGER connection_limit_trigger

    ON ALL SERVER WITH EXECUTE AS 'sql_login'

    FOR LOGON

    AS

    BEGIN

    IF ORIGINAL_LOGIN()= 'sql_login' AND host_name() in (select hostname from admindb..sqllogin_hostname_blacklist)

    ROLLBACK

    END

  • Does sql_login have rights on the blacklist table?

    What you can do to debug is something like this (untested):

    CREATE TRIGGER connection_limit_trigger

    ON ALL SERVER WITH EXECUTE AS 'sql_login'

    FOR LOGON

    AS

    BEGIN

    BEGIN TRY

    IF ORIGINAL_LOGIN()= 'sql_login' AND host_name() in (select hostname from admindb..sqllogin_hostname_blacklist)

    ROLLBACK

    END TRY

    BEGIN CATCH

    PRINT Error_message() -- goes into the SQL error log so that you can see what is causing the rollback

    ROLLBACK TRANSACTION

    END CATCH

    END

    You may want to add a print host_name() before you do the rollback, to ensure that you have a log of unauthorised access attempts. The SOX auditors I worked with wanted to see that.

    I would also suggest, if possible, rather have a whitelist of hostnames that are allowed to use that username, so that some bright spark doesn't try using a virtual machine or similar to get around the limitations.

    Regarding the devs, perhaps have a word with management about creating an IT policy (if one does not exist) and including in that that accessing system using system logins (like your sql_login) is an offence and will merit a disciplinary hearing (or whatever equivalent you have there). Trust me (been there, done that), make an example of one person (even if it's just a public warning) and the number of people breaking the rules will drop rapidly. 😀

    How are they figuring out the encrypted passwords?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • the encryption algorithm is very simple and has been around for years. we have a bunch of lookup db's and tables with server names, db names, logins, etc where apps select * from and then the data points them to the right server and database. not the best solution but it makes repointing in case of emergency very fast and easy. all they have to do is look at the password in the lookup table, apply the algorithm and get the real password.

    management never did anything about this, including dev management and now everyone is scrambling because we are getting ready for SOX. for us i think the blacklist is simpler because we can audit everything and the way some apps work it's too many hostnames to keep track of. we can easily find the developer PC's in an audit and add them to the blacklist.

    i'll try this over the weekened. QA said no breaking their server in the daytime

  • Hi Gail,

    Just wondering if you have any clever suggestions for preventing the login trigger from stopping logins altogether? I've written a trigger which logs all logon activity to a database, but if the database was to go offline or disappear for whatever reason, I wouldn't want my entire server to come crashing down. :o) Thanks!

Viewing 15 posts - 1 through 15 (of 19 total)

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