SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error conecting because of Login Trigger


Error conecting because of Login Trigger

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232801 Visits: 46361
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


alen teplitsky
alen teplitsky
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7348 Visits: 4674
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232801 Visits: 46361
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. BigGrin

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


alen teplitsky
alen teplitsky
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7348 Visits: 4674
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
db_bunny
db_bunny
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 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!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232801 Visits: 46361
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. Wink

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, 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


db_bunny
db_bunny
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)SSC Guru (232K reputation)

Group: General Forum Members
Points: 232801 Visits: 46361
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, 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


Dove-265763
Dove-265763
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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
jayoub
jayoub
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 587
Logging in with admin:servername and using windows authentication worked for me. It gave me an error during the first one or two tries, but i just kept trying and it finally allowed me to log in.

I disabled the trigger and was then able to get logged in normally. I then deleted the trigger

You saved my day. I would of had to reinstall SQL on this dev system

I tried to mark as solution but did not see the that option.

Thanks for the help

Jeff
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search