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 01, 2008 7:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:57 PM
Points: 186, Visits: 805
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 ;)
Post #545146
Posted Friday, August 01, 2008 8:17 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 @ 7:59 AM
Points: 41,530, Visits: 34,447
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 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 #545167
Posted Friday, August 01, 2008 8:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:57 PM
Points: 186, Visits: 805
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 :)
Post #545172
Posted Friday, August 01, 2008 8:29 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 @ 7:59 AM
Points: 41,530, Visits: 34,447
Off hand, I don't know. What does Books Online say? ;)


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 #545177
Posted Friday, August 01, 2008 8:31 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 @ 7:59 AM
Points: 41,530, Visits: 34,447
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 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 #545182
Posted Friday, August 01, 2008 8:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:57 PM
Points: 186, Visits: 805
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 ;)
Post #545185
Posted Friday, August 01, 2008 8: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 @ 7:59 AM
Points: 41,530, Visits: 34,447
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 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 #545196
Posted Friday, August 01, 2008 9:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:57 PM
Points: 186, Visits: 805
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
Post #545232
Posted Friday, August 01, 2008 9:21 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 @ 7:59 AM
Points: 41,530, Visits: 34,447
Did you come right then?


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 #545239
Posted Friday, August 01, 2008 1:54 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #545461
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse