3

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 <local machine>: 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

5
  • 1
    I'm on a personal computer, as a Windows Administrator account that is also a System / Server administrator of the SQL Server I'm trying to connect to.
    – J.D.
    Commented Nov 18, 2020 at 23:30
  • I have SQL Browser service enabled (and the SQL Agent is enabled, but that shouldn't be relevant).
    – J.D.
    Commented Nov 18, 2020 at 23:30
  • Using a SQL Account appears to also give me the same errors as in my post, regardless of which IP / Port combination I use.
    – J.D.
    Commented Nov 18, 2020 at 23:53
  • @nbk How would I go about disabling my firewall and / or checking if it's blocking the connection? I wouldn't expect that to be the issue if I'm accessing a local server instance from my local computer (as a Windows Administrator, SQL Server Administrator, and SQL System Administrator) but I'm no infrastructure expert by any means.
    – J.D.
    Commented Nov 18, 2020 at 23:54
  • you can enable the firewall log, to protocol what is commeing and going, this is a low level log, when all else fails. the firewall is disabled when you cannfewall and disable it. besides are you using the professional version of winodws or enterprise. the home edition is a bit underdeveloped
    – nbk
    Commented Nov 18, 2020 at 23:57

2 Answers 2

2

One thing you could try is to set Enabled to "no" for all individual Ip Addresses, and at the very bottom specify the desired port. I generally use the same port for all IPs, so I catch them all with just one config. If for nothing else, as a troubleshooting measure.

enter image description here

2
  • Hmm interesting, if I disable all individual IPs, how do I determine what IP I should be using?...would 127.0.0.1 suffice then? (Also kind of stupid question but in this context does IP4 mean IPv4 or are these literally just numbered instances of IPs?)
    – J.D.
    Commented Nov 19, 2020 at 12:51
  • 1
    IPCONFIG should give you a clue as to which to use. 127.0.0.1 worked just fine for me, as well as my "real" address according to IPCONFIG. IP4 in this context is just a sequential numbering of your IPs (not IP v4). Commented Nov 19, 2020 at 13:41
2

The query you are running inside a connection to your local server instance, is going to reflect the current connection information.

Input

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 

Output

+---------------+---------------+-------------+-------------------+----------------+--------------------+
| net_transport | protocol_type | auth_scheme | local_net_address | local_tcp_port | client_net_address |
+---------------+---------------+-------------+-------------------+----------------+--------------------+
| Shared memory | TSQL          | NTLM        | NULL              | NULL           | <local machine>    |
+---------------+---------------+-------------+-------------------+----------------+--------------------+

When you open the SSMS logon box and enter your credentials, then you will be connecting using the shared memory connection, which is basically without going over the TCP/IP stack of the computer.

Determine the IP and port of your instance

Run the following command in an elevated Command Prompt to retrieve the processes, executables and process IDs of the running processes:

Input

C:\> netstat -abon

Output

  TCP    127.0.0.1:50644        0.0.0.0:0              ABHÖREN         6964
 [sqlservr.exe]

You might find multiple entries for your sqlservr.exe process. Verify that the number at the end is the same (here: 6964). This is the process ID of your SQL Server Service. Open Task Manager and check the process ID of the sqlservr.exe process for that instance with the output from the nestat -abon command. You could have multiple entries for the same process ID.

Possible Entries

...
  TCP    127.0.0.1:50644        0.0.0.0:0              ABH™REN         6964
 [sqlservr.exe]
  TCP    [::1]:50644            [::]:0                 ABH™REN         6964
 [sqlservr.exe]
  TCP    0.0.0.0:53481          0.0.0.0:0              ABH™REN         6964
 [sqlservr.exe]

Looking at the above information I can determine that one of my local SQL Server instances is configured to listen on IPv4 and IPv6 and using the TCP ports 50644 and 53481.

In your SQL Server Configuration Manager if you scroll down to the bottom you will find the TCP dynamic Port that is being used by your local instance.

TCP Properties window of the SQL Server instance

With this information I can try and connect using the SSMS login box and setting the right protocol.

Logging in to SQL Server Instance with SSMS

Open up SSMS and enter the IP address of your local laptop (or the 169.254.xxx.xxx address from the TCP/IP SQL Server configuration ) and the dynamic port like this:

SSMS Logon Window

And you're in!

Verifying with Your Script

Input

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 

Output

+---------------+---------------+-------------+-------------------+----------------+--------------------+
| net_transport | protocol_type | auth_scheme | local_net_address | local_tcp_port | client_net_address |
+---------------+---------------+-------------+-------------------+----------------+--------------------+
| TCP           | TSQL          | SQL         |    161.78.198.140 |         -12055 |     161.78.198.140 |
+---------------+---------------+-------------+-------------------+----------------+--------------------+

Explanation

The 127.0.0.1:50644 in my netstat -abon output is the IPv4 address used for the Admin connection if the TCP/IP configuration of a local instance is configured to use dynamic ports. In my example 127.0.0.1,50644 wouldn't work.

The [::1]:50644 is the same for IPv6.

The 0.0.0.0:53481 is the line explaining that the sqlservr.exe (32) process is listening on all IP addresses on port 53481 for a TCP connection to the SQL Server instance.

Alternatives

Look at the current ERRORLOG file of your instance during the startup procedure to determine what to use:

...
2020-11-19 15:52:04.29 spid11s     A self-generated certificate was successfully loaded for encryption.
2020-11-19 15:52:04.29 spid11s     Server is listening on [ 'any' <ipv6> 53481]. 
2020-11-19 15:52:04.29 spid11s     Server is listening on [ 'any' <ipv4> 53481]. <<== HERE!!
2020-11-19 15:52:04.29 spid11s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2016CI ].
2020-11-19 15:52:04.29 spid11s     Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2016CI\sql\query ].
2020-11-19 15:52:04.30 Server      Server is listening on [ ::1 <ipv6> 50644].
2020-11-19 15:52:04.30 Server      Server is listening on [ 127.0.0.1 <ipv4> 50644].
2020-11-19 15:52:04.30 Server      Dedicated admin connection support was established for listening locally on port 50644. <<== HERE!!
...

Good luck.


My reference post What are valid connection strings for SSMS login box? on multi-instances setups to get TCP/IP settings up and running on a single computer contains more information on what to expect with multi-instance configurations on one server/laptop.


Not the answer you're looking for? Browse other questions tagged or ask your own question.