2

I'm having a problem connecting using my dedicated admin connection in SQL Server 2014. I just wanted to test it out.

I have DAC enabled in sp_configure. I have performed RECONFIGURE.

I have prefixed my connection string with ADMIN: in SSMS Connect To Server box.

I have not put a space between ADMIN: and the server name. The server name works just fine so long as I don't prefix it with ADMIN:

I am not trying to connect to object Explorer. I am attempting connection when I open a new query window.

I am using Windows Authentication, which works fine when not prefixing ADMIN: to the server name.

Its not a firewall issue, as I've tried it with the firewall turned off.

Having checked the SQL Server error log it says "dedicated admin connection support was established for listening remotely on port 1434". Does that mean it is connected? If so, why did I get the error below?

I've verified TCP port connectivity with the Powershell script mentioned by Dan Guzman, and it works fine. Powershell returns the message "server listening on TCP port 1434"

I am getting the following error:enter image description here

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) (Microsoft SQL Server, Error: 11001)

No such host is known

Your help would be appreciated.

NO WAR WITH RUSSIA
  • 54,954
  • 34
  • 200
  • 411
user3469285
  • 651
  • 2
  • 7
  • 19

2 Answers2

1

Check the SQL Server error log to determine the remote DAC port number. It will be contained in the message Dedicated admin connection support was established for listening remotely on port nnnn.. Verify that port is allowed through the firewall.

You can verify TCP port connectivity with TELNET or the Powershell one-liner below, specifying the port number as the argument piped to the echo command.

nnnn | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }
Dan Guzman
  • 23,595
  • 2
  • 37
  • 64
  • Its not a firewall issue, as I've tried it with the firewall turned off. – user3469285 Mar 23 '15 at 10:41
  • @user3469285 Do you see the DAC message in the SQL error log? Did you verify remote DAC port connectivity? It's not necessarily firewall. – Dan Guzman Mar 23 '15 at 11:08
  • Thanks for your help. I've verified TCP port connectivity with the Powershell script you mentioned, and it seems to work fine. Powershell returns the message "server listening on TCP port 1434". Having checked the SQL Server error log it says "dedicated admin connection support was established for listening remotely on port 1434". Does that mean it is connected? If so, why do I keep getting the error? – user3469285 Mar 23 '15 at 11:30
  • @user3469285, yes, the message indicates SQL Server is listening on the DAC port, not that a client has connected. TCP port 1434 is the default value so the SQL Browser service isn't needed to resolve the port number. Can you connect from SSMS using "servername,1434" instead of "ADMIN:servername"? What about SQLCMD? – Dan Guzman Mar 23 '15 at 12:10
  • Ah, using "servername,1434" in SSMS does not work either. – user3469285 Mar 24 '15 at 11:08
  • @user3469285m but what about SQLCMD? Note that SSMS may throw an error when connecting to via the DAC but the error will be different than the one in your question and the connection actually succeeds. The underlying reason in that case is that SSMS tries to open more than one DAC connection. – Dan Guzman Mar 24 '15 at 12:06
  • SQLCMD works fine when just specifying the server name, but with Admin: or ,1434 it returns the following error note: Microsoft ODBC Driver 11 for SQL Server: Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resources limitation (memory or maximum allowed connections) on the server. Error: TCP Provider: An existing connection was forcibly closed by the remote host. – user3469285 Mar 24 '15 at 12:45
0

I would start identifying if it's a SQL config issue or a Network/Firewall problem. RDP into the server and try connecting from there; if that works, you know that you have an issue with network, more likely a firewall one. If it doesn't, you have to review how you've configured DAC on SQL Server.

Even if you don't have SSMS installed on the server, you can use sqlcmd to test. Follow this Microsoft document "Using a Dedicated Administrator Connection", everything is explained there.

Salvador L
  • 131
  • 5