SQL Server connectivity without specifying tcp/port

Question

Having connection issues out of nowhere in my lab (Azure VMs):

SQL 2016, sp1, cu4 all the way around, Windows 2016 Datacenter

Windows Failover cluster for SQL AG testing.

SQLServer-0:

Default instance (port 1431)

Inst1 (port 1499)

SQLServer-1:

Default instance (port 1431)

Inst1 (port 1499)

From SQLServer-0, I can only connect to the remote instance(s) using tcp:SQLServer-1, 1431 (or tcp:SQlServer-1.kevinsdomain.com,1431).

Same thing for the named instance.

Same thing from SQLServer-1 to SQLServer-0.

Ping of just the NetBIOS name works just fine, ping -a resolves the FQDN.

None of these are running on port 1433, both servers are running SQL Browser under NT Authority\Local Service.

This is my test domain, along with a domain controller, DNS, etc.

Windows firewall on each has all of the ports open that are needed, including UDP 1434.

I'm sure there's something simple I've forgotten to check, flush, whatever. Just can't think of it.

enter image description here

• Solomon Rutzky: Is the SQL Browser NT service running on both servers?
• Kevin3NF: @SolomonRutzky yes
• George.Palacios: Considering you're getting an explicit Access is denied error, have you checked the logs on that box?
• Kevin3NF: SPNs were missing..added, but no change. This was working previously, now not...obviously something changed but I have no idea what

Answer 1

None of these are running on port 1433, both server are running SQL Browser under NT Authority\Local Service

It's not mandatory thing to run SQL services to listen on port# 1433, seems the ports are customized in your case, which is good in security point of view. Also, SQL Browser service is not necessary if you have fixed port for SQL Service (named instance) and you are mentioning that port in connection i.e. hostname,1499

Ping of just the NetBIOS name works just fine, ping -a resolves the FQDN

I think you should also tried, telnet SQLServer-0 1499 to confirm that the SQL Service is listening on particular port and that is not blocked by firewall, these steps may help for further troubleshooting

Windows Failover cluster for SQL AG testing

In this case, you need also to check on Endpoint ports not only the SQL ports. To get configured end points SELECT type_desc, port FROM sys.TCP_endpoints

Answer 2

Just to clarify your question... you wish to connect to the instance on either server without the need to specify the Port Number?

In that case, did you check if the instance name is added into your SQL configuration manager?

1. Open SQL Configuration Manager
2. Expand "SQL Native Client Configuration"
3. Select "Aliases"
4. See that if the instance that you wish to connect exists, if no, create the alias for the instance

Hope I do answer your question...