Having trouble connecting to a cluster in single-user mode with a DAC connection.

Question

Post reply

Having trouble connecting to a cluster in single-user mode with a DAC connection.

WayneS

SSC Guru

Points: 95460
More actions
December 27, 2009 at 7:46 pm

#137779

I'm trying to start up a sql server that runs on a cluster in single user mode, and to connect via a DAC connection.
I can do all of this EXCEPT for the DAC connection... it times out. I can connect through a normal connection.
My steps:
1. In cluster administrator, take the sql server and the sql server agent offline.
2. From a CMD prompt, run sqlservr -m -s<instance name>
3. Open SSMS disconnected.
4. Open a new query. Connect as "ADMIN:ServerName" - login fails.
5. Change query connection to "ServerName". Same admin credentials. Login succeeds.
6. Run sp_configure to verify that "Remote Admin Connections" are enabled... they are.
So, what am I missing to establish the DAC connection for this server? For all of the non-clustered servers, steps 2-4 works every time.
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 12 posts - 1 through 11 (of 11 total)

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

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 1

Have u tried sqlcmd with -A switch?

http://msdn.microsoft.com/en-us/library/ms189595.aspx

MJ

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

WayneS (12/27/2009)
4. Open a new query. Connect as "ADMIN:ServerName" - login fails.

With what message? Check the error log for the reason that the login failed. Should a non-zero state for the login failed error.

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

WayneS SSC Guru Points: 95460 More actions · Answer 3

GilaMonster (12/28/2009)
WayneS (12/27/2009)
4. Open a new query. Connect as "ADMIN:ServerName" - login fails.
With what message? Check the error log for the reason that the login failed. Should a non-zero state for the login failed error.

Hi Gail!

Error message is:

TITLE: Connect to Server

------------------------------

Cannot connect to ADMIN:<MyServerName>.

------------------------------

ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 43 - An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number) (Microsoft SQL Server, Error: -1)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

For MyServerName, I have used:

1. The Virtual SQL Name for the cluster.

2. The Node\Instance name

3. Both of the above, followed by ",2160" (without the quotes) to specify the DAC port. (as determined from the command prompt with starting in single user mode: "2009-12-28 18:23:17.27 Server Dedicated admin connection support was established for listening remotely on port 2160."

If I remove the ADMIN:, I can connect.

In both cases, I'm using Windows Authentication with the account that I'm logged into the server with. It does have sysadmin rights.

Thanks for your help!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

MANU-J.

SSC-Dedicated

Points: 31126

December 28, 2009 at 6:20 pm

#1096875

Make sure browser service is running.

MJ

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

WayneS (12/28/2009)
GilaMonster (12/28/2009)
WayneS (12/27/2009)
4. Open a new query. Connect as "ADMIN:ServerName" - login fails.
With what message? Check the error log for the reason that the login failed. Should a non-zero state for the login failed error.
Hi Gail!
Error message is:
TITLE: Connect to Server
------------------------------
Cannot connect to ADMIN:<MyServerName>.

Not after the error message that you get. I asked for the message from the error log, if there is one.

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

WayneS SSC Guru Points: 95460 More actions · Answer 6

GilaMonster (12/28/2009)
WayneS (12/28/2009)
GilaMonster (12/28/2009)
WayneS (12/27/2009)
4. Open a new query. Connect as "ADMIN:ServerName" - login fails.
With what message? Check the error log for the reason that the login failed. Should a non-zero state for the login failed error.
Hi Gail!
Error message is:
TITLE: Connect to Server
------------------------------
Cannot connect to ADMIN:<MyServerName>.
Not after the error message that you get. I asked for the message from the error log, if there is one.

Sorry, I mis-read your post.

In the application event log, the only worthwhile item I see is:

Description:

Dedicated admin connection support was established for listening remotely on port 2160.

In the SQL Logs, I see the same message.

I do not see any logs (SQL, Application, Security, System) that deal with failed logins during this time. It either works (without the ADMIN:), or it doesn't work (with the ADMIN:) and I get the above error message. And yes, the SQL Browser service is up and running.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

So it's not a login failure. The connection request is never reaching SQL. If it was, there would be a 'login failure' in the error log.

Is there a firewall between you and the server? If so, is the DAC port open?

Try connecting from the active node of the cluster (if you aren't already), maybe with SQLCMD. Eliminate as many possible causes.

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

WayneS SSC Guru Points: 95460 More actions · Answer 8

GilaMonster (12/29/2009)
So it's not a login failure. The connection request is never reaching SQL. If it was, there would be a 'login failure' in the error log.
Is there a firewall between you and the server? If so, is the DAC port open?
Try connecting from the active node of the cluster (if you aren't already), maybe with SQLCMD. Eliminate as many possible causes.

I've been running these directly from the active node through a remote desktop connection, logging in with my Administrator login.

When running with SQLCMD (SQLCMD -S<MyServerName> -E -A), I get

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DA
C) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFF
F].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection t
o the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under
the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

You're 100% sure that remote DAC is enabled?

EXEC sp_configure 'remote admin connections'

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

WayneS SSC Guru Points: 95460 More actions · Answer 10

GilaMonster (12/29/2009)
You're 100% sure that remote DAC is enabled?
EXEC sp_configure 'remote admin connections'

Yes. Above code returns:

nameminimummaximumconfig_valuerun_value
remote admin connections0111

(wish there was an IFCode for doing tables...)

And in the sql logs when starting up:

Dedicated admin connection support was established for listening remotely on port 2160.

This is what is so puzzling to me... it should be working. This works on every other server I've been to. But not this one... this one is also the first clustered instance. Which is what makes me wonder if there is something special I have to do when DAC-ing into a cluster.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

I am out of ideas.

Nothing needed on a cluster other than remote DAC. I have done it before, no problems at all.

Way out idea. Does the cluster have a firewall running? If so, try disabling it, just to test. It shouldn't, but...

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