August 1, 2008 at 7:58 am
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 😉
August 1, 2008 at 8:17 am
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
August 1, 2008 at 8:23 am
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 🙂
August 1, 2008 at 8:29 am
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
August 1, 2008 at 8:31 am
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
August 1, 2008 at 8:35 am
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 😉
August 1, 2008 at 8:41 am
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
August 1, 2008 at 9:14 am
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
August 1, 2008 at 9:21 am
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
August 1, 2008 at 1:54 pm
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
August 1, 2008 at 1:59 pm
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
August 29, 2008 at 7:37 am
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
August 29, 2008 at 8:02 am
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
August 29, 2008 at 8:12 am
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
September 22, 2008 at 10:44 pm
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