Enabling TCP and Named Pipe connections (Accessing via IP address)

How to access SQL server direct using an IP address

An out of the box SQL installation will be default not allow you to connect to it via an IP address, an issue I recently came across while trying to connect to my local development SQL instance via a local docker container.

To enable access via an IP address, do the following.

Open SQL Management studio, right click on the server and select properties, then select the connections page.

Now, make sure the Allow remote connections to this server is enabled.

Next, from the run command (Windows key + R), type in mmc, and click Run.

Now, select File -> Add/Remove snap-in, and select SQL Server Configuration Manager.

Expand the Configuration Manager and go to the SQL Server network Configuration\Protocols for SQL2019. Enable both Named Pipes and TCP/IP.

Finally right click on the TCP/IP option and select properties. Then select the IP Addresses tab. Scroll down to the IPAll section and set the port you want, for mine I am using port 9000.

Restart SQL.

Now, you should be able to connect using the IP Address and port.
Using the format [IPAddress]\[Instance],[Port]

In my case, my server would be 192.168.1.7\SQL2019,9000
This is because my computers IP is currently 192.168.1.7, my SQL instance name is SQL2019 and the port I set was 9000. If you have left the instance name as the default you could of just used 192.168.1.7,9000 and it would work.

Enjoy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: