SQL instance connection error – ‘A network-related or instance-specific error occurred’

I was stumped with this error for hours last night while building out some on-premises SharePoint Farms. I was trying to connect to a specific named instance as opposed to the default instance. 

I thought I would blog about the fix because it’s something I’ve never come across before and it took some googling of the above error to point me in the right direction.

I tried all the usual suspects – 

• Checking that port 1433 wasn’t being blocked by the firewall.
• Checking I could ping the SQL box by it’s IP/Machine name.
• Checking the SQL Browser service was started and that port 1434 was being blocked (FYI the browser services uses this port).

I noticed I could connect to the instance if I forced the connection over port 1433 in Management Studio i.e. mysqlserver\myinstance, 1433 so I figured it must be firewall related. Turning off Windows firewall and not specify the port number also worked.. 

I eventually came across something called ‘Dynamic Ports’ which I confess I had no idea existed! It turns out when using a named instance other than the default instance, these are exposed through a dynamic port which can change (although I have read that normally SQL tries to use the same one even if it’s restarted). 

To check what dynamic port the instance is using,  view the TCP/IP properties in the Network Configuration section of Configuration Manager. The screenshot below shows where the Dynamic Port setting can be found. 

The quick fix would simply be to allow this dynamic port through the firewall but in my case I plan to change this to a static port and disable dynamic ports. I’m sure there are some reasons why you would use dynamic ports but for my needs a static one is easier to manage.

So there we go! Every day is indeed a school day!

Hope this helps someone.