Error conecting because of Login Trigger

Question

Error conecting because of Login Trigger

Viewing 5 posts - 16 through 19 (of 19 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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, 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 Right there with Babe Points: 763 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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 SSC Veteran Points: 264 More actions · Answer 4

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 SSCertifiable Points: 6991 More actions · Answer 5

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