I am very confused as tcpview shows sqlservr.exe listening on these ports 49371, 49373, 52732, but in the SqlServer Configuration Manager->SQL Native Client 10.0 Confgiuration->Client Protocols and properties of TCP/IP, the default port is 1433. Why is there no 1433 I see being listened on though? I find this very odd.

All my instances have a Dynamic Port set to some number as well. Any idea what is going on here? as I would like to connect my SQL Management Studio remotely but can't right now. I can connect the local one.

My end game is connectnig Visual Studio for entity generation and if I can get SQL Management Studio working, I think I can take it from there, but can't seem to get this to work.

EDIT: more notes. On my clean install machine, it is also not listening on port 1433. TCP is enabled and is order 2(shared memory is order 1). I am confused, how come a default install says default port is 1433 but no one listens on that port?

Version: 2008 R2 and just installed service packs.

Basic question though...how is this supposed to work? I have installed two instances locally and each one ends up with a dynmaic port and with tcpview, I confirm it takes up that dynamic port. This port 1433 everyone refers to, is that like a proxy instance that redirects to these dynamic ports or am I just supposed to change from dynamic port to fixed tcp port of 1433 myself?

What happens when you keep installing MS SQL instances? It can't put them all on port 1433, right? Was it supposed to put the first one on port 1433? What was your experience here?

thanks, Dean


It could be that not sqlserver.exe is listening, but another process

Verify with netstat. My SQL says:

C:\Users\Administrator.ENV1>netstat -a

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP             ENV1S08:0              LISTENING
  TCP            ENV1S08:0              LISTENING
  TCP            ENV1S08:0              LISTENING
  TCP           ENV1S08:0              LISTENING
  TCP           ENV1S08:0              LISTENING
| improve this answer | |

Look in the SQL Server ERRORLOG file. The log will clearly state what port SQL Server is listening on.

The other ports you see with netstat, etc, are not always useful for client connections.

| improve this answer | |

This answer is a little long; if you don't read anything else, go read some detail on SQL Browser. That page is from SQL 2005, but I do not think that SQL Browser behavior has materially changed since then.

Easily overlooked, the SQL Browser is an important part of Microsoft's scheme. SQL Browser runs as a Windows Service and uses 1434 to act as a sort of directory service that can find the instances installed on the local machine. When the database connection is being set up at runtime, the driver can interact with this service to figure out what port the driver needs to connect to. So, SQL Browser isn't a proxy either. It's really a sort of super-simple directory service.

Typically, SQL Server connections are made to the default (or "unnamed") instance:

  • hal9000

or connections are made to a "named" instance:

  • hal9000\development
  • hal9000\test
  • hal9000\whatever

In the case of the default instance, it should be listening on 1433. IIRC, the standard VS installer installs a (SQL Server Express) named instance only. In that case, there would not be anything listening at 1433.

Named instances will be assigned a port when they start up. Either way, it doesn't matter because the SQL Browser service knows where they are and what their names are and can look up a port number, given an instance name. A connection string normally just passes the instance name because user applications don't normally know anything about the ports.

You mention "all of your instances", but you don't discrimate between named and unnamed instances, which makes me suspect that you don't have a default (a/k/a "unnamed") instance.

It is possible to configure all of this manually and specify port values, then disable the SQL Browser service. This is somewhat analogous to turning DHCP off on your network.

Manual configuration is normally done to support firewall rules and I've only seen it done on servers, not development workstations. You statement about not being able to connect "remotely right now", but that you can connect to the "local one" is a little confusing to me because I think that you would be talking about instances all on a single machine. If you are trying to connect to named instances running on a remote machine even if it is on your LAN/WAN and not the open internet, it is very possible that you are being stopped by (either software or hardware) firewalls.

| improve this answer | |

okay, so for some reason, NONE of our instances on two servers created an instance on port 1433 so while our settings say default port is 1433, every time we install an instance, it is given a new dynamic port. I finally went in and changed instance A from dyanmice to fixed at 1433 and everything works great!!! I also changed all the other instances to fixed because the dynamic ports are all in a very high range that is used by other applications to bind to and connect to servers from and it's typically better to have a port in the lower ranges as those are not used by client sockets in general.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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