InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 11:15:09 GMT20http://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxI cant thank you enough....... its the major goof up i had done in my 6 years as a DBA. I was testing THE logon triggers and created it on sysadmins and worse without knowing the sa password. Thanks to this forum, i now cleared the problem with out getting all the attention.[b]C:\Documents and Settings\dove165>sqlcmd -s ghdb78 -e -A -q "Disable trigger trigLogon_CheckForSysAdmin ON ALL SERVER"Disable trigger trigLogon_CheckForSysAdmin ON ALL SERVER1>[/b]It worked like MAGIC!thanks allWed, 15 Dec 2010 22:19:45 GMTDove-265763http://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxAh. Makes sense.Biggest things to keep in mind is to not reference any DB other than master within the trigger, make sure that there are no permissions issues (I recommend the use of the EXECUTE AS clause) and check that DBs are online before calling procs that reference those DBs.Wed, 24 Sep 2008 00:41:16 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxThank you Gail - very helpful as always. I am using logon triggers because once management decide which logins they want to prevent, it will be easy to roll them back using logon triggers. But I agree with you, if I was purely after just login audits then a trace would make much more sense.CheersJoannaTue, 23 Sep 2008 17:55:41 GMTdb_bunnyhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspx[quote][b]joannapea (9/22/2008)[/b][hr]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![/quote]Make sure that it can't ever throw a sev 16 or higher error. ;)What I did with a login trigger for that was kinda the following. (pseudocode). The purpose of the login trigger was to prevent people from using certain application's accounts (for which they knew the passwords) from any querying tool. I had a table with the accounts to be monitored and a second table where logging or violations was done.[code]CREATE TRIGGERFor LogonASIF Original_login() in SELECT login name from master.dbo.restrictedlogins AND App_name in ('Query analyser',...)BEGIN IF DATABASEPropertyEX('DBA','Status') = 'Online' EXEC master.dbo.LogViolation ELSE Print 'Login violation detected ....' -- goes into error log ROLLBACK TRANSACTION[/code]The only database that's guaranteed to be available is master, so the trigger should only reference master within itself. The proc that it calls after checking the DB status can reference the other DBs, as it has been checked that they are available.If I may suggest, login triggers are possibly not the appropriate tool for logging all logins onto a server. A server-side trace capturing the Audit Login event would be much lighter on the server, and would give you the ability to import the trace onto a central auditing server (if you have one)You could also enable the 'audit successful and failed logins' on the server properties.Logon triggers are, imho, better for when you want to selectively prevent logins, or log under certain conditions, or run arbitrary code whenever someone logs in.Tue, 23 Sep 2008 00:53:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxHi 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!Mon, 22 Sep 2008 22:44:35 GMTdb_bunnyhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxthe 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 daytimeFri, 29 Aug 2008 08:12:17 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxDoes sql_login have rights on the blacklist table?What you can do to debug is something like this (untested):[code]CREATE TRIGGER connection_limit_triggerON ALL SERVER WITH EXECUTE AS 'sql_login'FOR LOGONASBEGINBEGIN TRYIF ORIGINAL_LOGIN()= 'sql_login' AND host_name() in (select hostname from admindb..sqllogin_hostname_blacklist) ROLLBACKEND TRYBEGIN CATCH PRINT Error_message() -- goes into the SQL error log so that you can see what is causing the rollback ROLLBACK TRANSACTIONEND CATCHEND [/code]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. :DHow are they figuring out the encrypted passwords?Fri, 29 Aug 2008 08:02:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxi 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_triggerON ALL SERVER WITH EXECUTE AS 'sql_login'FOR LOGONASBEGINIF ORIGINAL_LOGIN()= 'sql_login' AND host_name() in (select hostname from admindb..sqllogin_hostname_blacklist) ROLLBACKENDFri, 29 Aug 2008 07:37:20 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxStarting SQL with -f works too. Had to do that once with a failed login trigger on a cluster. The DAC was set local only.Fri, 01 Aug 2008 13:59:05 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxTo 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.Fri, 01 Aug 2008 13:54:25 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxDid you come right then?Fri, 01 Aug 2008 09:21:47 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxHi 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 ;)JMSMFri, 01 Aug 2008 09:14:25 GMTJMSMhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxFirst 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 viewAlso, the syntax is ON ALL SERVER, not ON ALL SERVERSFri, 01 Aug 2008 08:41:41 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxHi Gila, I've try the following command but still get this error. when i use the disable trigger command i've got 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 -APassword: Msg 18456, Level 14, State 1, Server PJTUMS06\SQLUMS_T1, Line 1Login failed for user 'sqlumssrv'.Thanks and regards,JMSM ;)Fri, 01 Aug 2008 08:35:41 GMTJMSMhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxLooks like this is the syntax. Can't test. Don't have any named instances around.If you're using windows authentication[code]sqlcmd -S servername\instancename -E -A [/code]If you're using SQL authentication[code]sqlcmd -S servername\instancename -U username -P password -A [/code]Fri, 01 Aug 2008 08:31:24 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxOff hand, I don't know. What does Books Online say? ;)Fri, 01 Aug 2008 08:29:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxLike 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 regardsJMSM :)Fri, 01 Aug 2008 08:23:48 GMTJMSMhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxConnections 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 switchOnce in, you can disable the trigger.[code]DISABLE TRIGGER MyBrokenLoginTrigger ON ALL SERVER[/code]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.Fri, 01 Aug 2008 08:17:28 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic545146-146-1.aspxHello,The following error appears after i've created one login trigger.sqlcmd -S localhostMsg 17892, Level 14, State 1, Server Hostname\Instance_name, Line 1Logon 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 ;)Fri, 01 Aug 2008 07:58:35 GMTJMSM