0

  1. I am asking two questions in this post. If I see activity in netstat with port 1433 for an unnamed instance, whose tcp addresses are not enabled, what does that mean?

  2. What is the preferred way to configure a dynamic port that occurs with my second, named instance? Here are the details:

This post is slightly different than my first post, because in the last post I overlooked a critical detail with the default, unnamed instance, which was I could find no evidence that any of the TCP addresses were enabled. Here is some background:

I have a Windows 2016 server with an unnamed and a named MS SQL SERVER instances installed. TCP and Memory connections are turned on for both, until you get to the protocol section for TCP.

Both instances use the same IP address. The unnamed instance uses port 1433; and the named instance uses 49365.

The unnamed instance was installed first, and hence was assigned port 1433. The firewalls on the server and the client (Windows firewall) are set to allow the necessary UDP and TCP traffic.

Here are two pictures denoting most of the port configurations for the unnamed instance. Please note enabled is set to No for all settings, there is no dynamic port, and the port used is 1433.

Unnamed Instance TCP Ports Partial 1

Unnamed Instance TCP Ports Partial 2

This database belongs to a vendor, whose clients are able to connect to the database, so I am trying to figure out that happens in the hope that information will help me to connect to the named instance.

I've tried all suggestions in the prior post, read on-line, and my named database was able to do an import from an older SQL Server database, 2008.

Using netstat, I can see port 1433 is in use, so there is no point in co-opting it for the named instance. Should I just pick a port I know won't be used, or is not likely to be used?

Improve this question
5
  • You have added an answer, is that a correct answer or you were trying to edit your question?
    – McNets
    Commented Jul 13, 2020 at 15:32
  • @McNets Both. I added an answer, because I can connect to the database now. I also edited the original OP. I put an answer in just for good communication that I had a workaround, but I am still not sure this is the best, or approved, way to do this. I also don't know how the clients connect, if TCP is turned off, unless the clients, whose source code I don't have are somehow appearing local to their database sever. I know how that's done on Linux with terminal emulation, but not in this case.
    – octopusgrabbus
    Commented Jul 13, 2020 at 15:49
  • If this software is located in the same sever or clients connect to some windows service, it can use a Shared memory connection.
    – McNets
    Commented Jul 13, 2020 at 16:02
  • The clients are on individual Windows workstations, and the server contains everything else. Shared memory and tcp/ip are turned on, and it appears they are turned on by default in the installation, because named pipes are by default disabled. My application -- I do have the source code -- has to connect through ODBC -- because that's how the app works. I will just have to make sure the connection string can be modified to take a ',' and port number after the DB name.
    – octopusgrabbus
    Commented Jul 13, 2020 at 16:05
  • Is the SQL Server Browser service enabled and running on the server? Is there a firewall blocking UDP 1434 from clients to the server?
    – Hannah Vernon ♦
    Commented May 14, 2023 at 14:21

2 Answers 2

Reset to default
2

I believe we have several misunderstandings here, so let's try to sort them out one by one and see if that clarifies the big picture in the end.

What you refer to as an "unnamed instance" is what we call a default instance. Just as FYI.

It is not the first installed instance that gets port 1433. 1433 is a Well Known Port for the default instance. This is from back in the days when we couldn't have more than one instance (pre-SQL Server 2000).

A named instance gets a port number from the Windows dynamic range at first startup. "Dynamic" does not mean a different port number at every startup. The assigned port is stored in the registry and is attempted to be used at following startups. "Dynamic" controls what happens if that fails (i.e., the port last used is now busy). "Dynamic" means "get a different port from Windows". The other option means "failed to start SQL server".

What you see in the GUI you posted as an IP being "disabled" is bad GUI presentation. The individual IP sections are being overridden by the bottom-most config (IPAll). Having a port there means that all IP addresses are using that port (as far as I understand). Readers are welcome to comment/edit here.

As for your named instance, I suggest you set the port number under IPAll to 0 (and have nothing for the "static port") and re-start your SQL Server. It should now get a new port from the dynamic Windows range.

Improve this answer
0

I have a solution, but not necessarily the best solution. I took the assigned dynamic port 43965, treated it as a permanent port; assigned it the named instance, and I can connect using the dbname,port# .

Is there a one and only preferred way to use the dynamic port that was assigned when the named instance was installed?

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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