How to connect to a local SQL Server instance by IP Address in SQL Server Management Studio?
3
votes
2
answers
33951
views
I'm writing some test code that is required to connect to a local SQL Server instance (SQL Server 2017 - Developer Edition) by IP address. I have the SQL Server instance up and running on my machine, but can't figure out the correct IP + Port to use for my connection string's Server property.
As a starting point, I'm trying to at least manually connect to my server instance with SQL Server Management Studio (via the IP address) but I can't seem to figure it out.
1. How do I find the IP Address and Port of my local SQL Server instance?
2. What is the correct format I should be inputting the IP Address and Port into the Server Name field of SSMS (and is it the same format as a connection string Server property)?
I've tried locating the IP address executing the following SQL code on it:
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
But the local_net_address and local_tcp_port fields are NULL and the client_net_address is set to ``:
I've also tried going into the SQL Server Configuration Manager, under the SQL Server Network Configuration section, and enabling TCP/IP. Then I tried using a mix of the IP4 and IP6 addresses with Port 1433 (which is blank by default in the TCP/IP Properties, but I've tried leaving it blank and setting it to 1433) and that didn't work.
(I made sure to restart the SQL Server service and SQL Server Browser service every time I made changes in here.)
I've received the following errors when trying to connect via SSMS for the different combinations of the IP4 address with and without Port specified:
**No Port Specified:**
**Port Specified w/ Colon:**
**Port Specified w/ Comma:**
I've also tried connecting via 127.0.0.1 with the same combinations of Port 1433 above to no avail. (I receive similar errors except for the following combinations.)
**127.0.0.1 w/o Port:**
**127.0.0.1 w/ Port via comma:**







Asked by J.D.
(40903 rep)
Nov 18, 2020, 10:54 PM
Last activity: Nov 19, 2020, 03:24 PM
Last activity: Nov 19, 2020, 03:24 PM