Recently I was doing installation of K2 5.2 on Azure VMs with SQL server named instance hosted on separate Azure VM. I’ve created SQL Server alias on K2 VM but then run into issue – neither K2 Setup Manager nor SSMS were able to connect to SQL through alias. I next tried direct connection via server\instance name which also failed. SSMS showed me the following error:
I first focused on network connectivity between VM:
- Confirmed that I can ping SQL Server VM from K2 Server VM
- Confirmed that no firewall enabled on VM and Azure VMs on the same network with nothing blocking connectivity between them
- I tried to use telnet to test port 1433 – it failed
This is what kept me focused on network connectivity layer a bit longer then necessary. But after confirming that SQL Server not listening on port 1433 using netstat -na | find “1433” it became quite clear that focus should be on SQL Server configuration. First of all – by default named instance listen on dynamic port, and you actually need to have SQL Server Browser Service enabled to ensure you can connect to named instance without specifying port while using dynamic ports. But in my case it was not that as in SQL Server configuration there was explicitly specified custom port (SQL Server Configuration Manager > Protocols for %INSTANCE_NAME% > TCP/IP Properties > TCP Dynamic Ports – if you have anything other than 0 in IPAll section fir this setting you are not using dynamic ports). When your problem is dynamic ports and disabled SQL Server Browser Service error message from SSMS looks as follows:
As you can see error message explicitly tells you “Error Locating Server/Instance Specified. To fix this either set 0 for TCP Dynamic Ports setting and enable SQL Server Browser Service or specify some port number there. You sort of choosing your dependency here – either browser service (may fail to start) or custom port (may be hijacked by other service). It seems that browser service is better approach.
So in my case I was confused by expecting named instance to listen on default port which was, to put it simply, wrong expectation. Here is how you can check on which port your instance is listening:
But obviously having access to SQL Server you can get this data from SQL Server Configuration manager too: SQL Server Configuration Manager > Protocols for %INSTANCE_NAME% > TCP/IP Properties. Just keep in mind that you need to check TCP Dynamic Ports value both for specific address and for IPAll section. But like I said in my case, the problem was not about ports. Once I found out instance port I noticed that I still cannot connect to it using telnet, just because IP address was not enabled in SQL Server Configuration Manager > Protocols for %INSTANCE_NAME% > TCP/IP Properties (meaning it had Enabled=0). I corrected that and telnet connectivity test succeeded.
Still, when I get back to SSMS I was getting the same error – “Could not open a connection to SQL Server. Microsoft SQL Server, Error: 53”. Reason? With SQL Server 2016 and latest versions of SQL Server, I keep forgetting that the latest and greatest version of SSMS still reads alias settings from x86 registry hive (meaning you need to configure SQL alias using cliconfg.exe from C:\Windows\SysWOW64) – I have a hard time getting use to it. Interestingly fully missing x86 alias triggers error message “Could not open a connection to SQL Server. Microsoft SQL Server, Error: 53” while one we configure with non existing server or instance name will give you “SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified”.
Anyhow your key takeaways from this post should be:
- Know your instance port
- Make sure that IP address is enabled
- We still need to configure alias twice (x86/x64) to avoid unpleasant surprises from apps reading setting from non-configured location
I hope this post may save some troubleshooting time for someone.