Sql-server – Unable to connect to Listener after rebuild

availability-groupslistenersql serversql-server-2016ssms

I dropped an AG with

DROP AVAILABILITY GROUP [MyDb] 

it had a listener.

I left the listener open in the object Explorer of SSMS 17.3. While the AG and Listener were down. I hit refresh on the listener a couple times in SSMS. (because I have OCD and had to make sure it was gone)

I recreated the AG with the same listener and when I hit refresh it would not connect. I get the message below. I tried the following and was not able to connect, receiving the same error.

  • Disconnect the listener in 17.3 and try to create a fresh connection
  • Open SSMS 16 (13.0.15900.1) and try and connect
  • Close and reopen both SSMS sessions.
  • Try adding MultiSubnetFailover=True to the Additional Connection Parameters tab of the connect popup (this a multi-subnet listener)

I was able to connect using the fully qualified domain name 'MyListener.fully.domain.net', but was still unable to connect with just 'MyListener'

A coworker was able to connect to the listener without issue, using both 'MyListener' and 'MyListener.fully.domain.net'

I rebooted my Windows 10 PC, and after reopening SSMS I was able to connect with just the listener name 'MyListener'.

I upgraded SSMS to 17.4 and the same thing happens.

What is happening here? How can clear the issue without rebooting my PC?

TITLE: Connect to Server

Cannot connect to MyListener.


ADDITIONAL INFORMATION:

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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

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


The network path was not found


BUTTONS:

OK

Note in all cases I created the AG and added the listener using a variation of the T-SQL from https://www.brentozar.com/archive/2016/06/sql-server-2016-availability-groups-direct-seeding/ The AG is on an instance running SQL Server 2016 RTM, the database is AdventureWorks2014.

Best Answer

It worked for me after setting Network Connection to TCP and trusting server certificate in the connection properties.