Enable a Named Instance to Connect on 1433

,

My default setup for a few years is to only use named instances, usually with a standard SQL2014 or SQL 2016 as the name of the instance. Recently, I set up a new instance and needed to connect from another machine. I’ve done this lots of times, but in this case, I wanted to both enable TCP/IP as well as move the port.

This post explains how to get your named instance to listen on 1433.

First, run the Configuration Manager and check your protocols for the server. You likely see something like this, where TCP/IP is disabled.

2019-04-15 20_15_55-WS2016 SQL2016 Packt c - VMware Workstation

Right click TCP/IP and enabled it from the menu.

2019-04-15 20_16_06-WS2016 SQL2016 Packt c - VMware Workstation

You’ll have to restart the instance, but wait. Cancel the message that appears. Now open the properties for the TCP/IP service.

2019-04-15 20_16_29-WS2016 SQL2016 Packt c - VMware Workstation

At the bottom, there’s an IPAll item. This is likely set for Dynamic ports for your named instance. Remove the 0 and enter 1433 for the TCP Port line. You should get something like this:

2019-04-15 20_16_52-WS2016 SQL2016 Packt c - VMware Workstation

When you click OK or Apply, you’ll see this message.

2019-04-15 20_16_13-WS2016 SQL2016 Packt c - VMware Workstation

Now restart your database engine service and you should be able to connect over 1433.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate