2

I am wondering what ports are used by SQL Server database engine? I need such port number to write configuration scripts to grant access to specific port of the machine installed with SQL Server to make it safe. A related question is whether SQL Server database engine will use one static port number to serve all client requests, or using one port for each request?

BTW: my background is SQL Server 2008 enterprise.

thanks in advance, George

George2
  • 1,137
  • 6
  • 22
  • 41

1 Answers1

4

The default instance uses port 1433. Named instances will use dynamically negotiated ports (but can be configured to listen on specified ports-- see http://msdn.microsoft.com/en-us/library/ms177440.aspx).

Edit:

By default when a named instance of SQL Server starts it chooses a randomly unused port and registers that with the SQL Browser service. Clients send requests to that service (UDP port 1434) to retrive the port number for the instance they're trying to connect. You are correct in that the dynamic port number will remain the same throughout a single execution of a given instance. If you stop / restart that instance, though, the port number will change. So, there is no "fixed" value for the dynamic port.

Evan Anderson
  • 141,881
  • 20
  • 196
  • 331
  • 1
    Heh heh... I've been cooped up in a car most of the day (driving cross-country to go to a wedding tomorrow), so now that I have Intenret access I'm surfing like mad. I've been looking for my Server Fault fix most of the day. I think my wife might be tired of hearing me muse about the questions I might be missing. >smile> – Evan Anderson Aug 15 '09 at 04:42
  • Thanks Evan, 1. my further confusion is about dynamic port in SQL Server Configuration Manager, the word dynamic makes me think that each time a client connects to SQL Server, SQL Server will serve the client using a new (dynamic) port, so the port number value is hard to determine. But from my study, it seems the dynamic port is actually fixed value (an unused port number chosen by SQL Server, next time when client connects to server, for "fixed" I mean the same fixed number will be used), my understanding correct? – George2 Aug 15 '09 at 11:35
  • 2. when we configure dynamic port in SQL Server Configuration Manager, we need to specify value 0, is it possible for us to know the "fixed" value of dynamic port from some way? – George2 Aug 15 '09 at 11:36
  • Thanks Evan! If we are using dynamic port, how to know which is the port number used? – George2 Aug 15 '09 at 14:58