Can't connect to a cluster instance because of a problem with a LOGON trigger (Error 17892)

Question

Post reply

Can't connect to a cluster instance because of a problem with a LOGON trigger (Error 17892)

morgman

SSC Veteran

Points: 239
More actions
October 26, 2009 at 12:32 pm

#142718

Please help. I have created a LOGON trigger on a named instance and I now nobody can log on to the server database engine. I have read various pages that say the only fix is to use DAC to connect without firing the trigger so that the trigger can then be disabled. However, the named instance is part of a cluster and when I log on to the active node it isn't seeing the instance as local. Remote DAC is disabled and I can enable it without first connecting. This is a production server and I really need to resolve the issue quickly. I assume the error would persist after a server reboot?
Any advice is most welcome.
Your in hope
Mark M

Viewing 8 posts - 1 through 8 (of 8 total)

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

MG-253362 SSC Veteran Points: 282 More actions · Answer 1

Have you tried using the Surface Area Configuration Manager to enable the DAC?

SSAC --> Surface Area Configuration for Features --> Database Engine --> DAC Enable remote DAC

morgman SSC Veteran Points: 239 More actions · Answer 2

Thanks

Yes I've tried that but I can't connect to the instance (because of the LOGON trigger problem) and thus can't get to the point where I can enable remote access to DAC.

MG-253362 SSC Veteran Points: 282 More actions · Answer 3

First off, if you're not already on the horn with Microsoft, you should file a case with them ASAP. Meanwhile, it might help if you explain what the trigger does. or post the code.

morgman SSC Veteran Points: 239 More actions · Answer 4

Basically the trigger is trying to insert a record for each logon Unfortunately, I didn't fully qualify the table and as a consiquence it is reporting as invalid.

I'll get on to Microsoft as soon as I can but I was hoping to find a solution

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 5

You can take SQL Server offline from cluster administrator. Once it is offline, on the same node that was hosting SQL Server - you can then start SQL Server in single user mode from the command line.

You should then be able to disable the logon trigger - shut down SQL Server from the command window and bring the resource back online.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Go into cluster admin and shut the SQL instance down. Make sure it doesn't try to restart.

Open a command prompt. Navigate to the directory where the SQL executable is and start SQL from there as a standalone executable. Start it in minimal config, single-user mode (make sure there is nothing that can grab that single connection)

If this is the default instance, the command will be

sqlservr.exe -m -f

If it's a named instance

sqlservr.exe -m -f -s <instance name>

Open another command prompt and connect via SQLCMD.

SQLCMD -E

or

SQLCMD -E -S <Servername\Instance Name>

That should bypass the logon trigger and allow you to disable it. Once you've done so, stop the standalone exe (Ctrl-C) and restart SQL from cluster admin.,

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

morgman SSC Veteran Points: 239 More actions · Answer 7

Thanks for all your help I took the cluster group containing the instance off line and started it in single user mode. Had to use the -m "sqlcmd" to stop application services grabbing the connection.