Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Error conecting because of Login Trigger Expand / Collapse
Author
Message
Posted Friday, August 1, 2008 1:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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 2008, MVP
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

Post #545466
Posted Friday, August 29, 2008 7:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:02 PM
Points: 1,414, Visits: 4,545
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #561193
Posted Friday, August 29, 2008 8:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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. :D

How are they figuring out the encrypted passwords?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #561215
Posted Friday, August 29, 2008 8:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 15, 2014 1:02 PM
Points: 1,414, Visits: 4,545
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #561225
Posted Monday, September 22, 2008 10:44 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 4:57 PM
Points: 93, Visits: 169
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!
Post #574022
Posted Tuesday, September 23, 2008 12:53 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
joannapea (9/22/2008)
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!


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.

CREATE TRIGGER
For Logon
AS

IF 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

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.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #574071
Posted Tuesday, September 23, 2008 5:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 4:57 PM
Points: 93, Visits: 169
Thank 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.

Cheers

Joanna
Post #574832
Posted Wednesday, September 24, 2008 12:41 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
Ah. 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.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #574922
Posted Wednesday, December 15, 2010 10:19 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 1:36 PM
Points: 20, Visits: 72
I 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.


C:\Documents and Settings\dove165>sqlcmd -s ghdb78 -e -A -q "Disable trigger tr
igLogon_CheckForSysAdmin ON ALL SERVER"
Disable trigger trigLogon_CheckForSysAdmin ON ALL SERVER
1>



It worked like MAGIC!

thanks all






Regards,
Dove
Post #1035648
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse