Error conecting because of Login Trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Error conecting because of Login Trigger

19 posts, Page 1 of 2

1 2 »»

Error conecting because of Login Trigger

Rate Topic

Display Mode

Topic Options

Author

Message

JMSM

Posted Friday, August 01, 2008 7:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:03 AM
Points: 185, Visits: 751

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

GilaMonster

Posted Friday, August 01, 2008 8:17 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

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

JMSM

Posted Friday, August 01, 2008 8:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:03 AM
Points: 185, Visits: 751

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

GilaMonster

Posted Friday, August 01, 2008 8:29 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

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

GilaMonster

Posted Friday, August 01, 2008 8:31 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

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

JMSM

Posted Friday, August 01, 2008 8:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:03 AM
Points: 185, Visits: 751

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

GilaMonster

Posted Friday, August 01, 2008 8:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

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

JMSM

Posted Friday, August 01, 2008 9:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:03 AM
Points: 185, Visits: 751

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

GilaMonster

Posted Friday, August 01, 2008 9:21 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 37,738, Visits: 30,010

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

K. Brian Kelley

Posted Friday, August 01, 2008 1:54 PM

Keeper of the Duck

Group: Moderators
Last Login: 2 days ago @ 1:55 PM
Points: 6,584, Visits: 1,789

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 »

19 posts, Page 1 of 2

1 2 »»

Permissions