Sample Header Ad - 728x90

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 ``: SQL Server Connection Property 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. SQL Server Configuration Manager - SQL Server Network Configuration (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:** IP4 No Port **Port Specified w/ Colon:** IP4 With Port via Colon **Port Specified w/ Comma:** Port Specified via 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:** Localhost IP No Port **127.0.0.1 w/ Port via comma:** Localhost IP with Port via comma
Asked by J.D. (40903 rep)
Nov 18, 2020, 10:54 PM
Last activity: Nov 19, 2020, 03:24 PM