port number of SQL Server [duplicate]

Question

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

Answer 1

The default instance will, by default, listen on tcp/1433. It could possibly also listen on a named pipe (tcp/445) - but I think that must be explicitly enabled these days.

Named instances, like SQLEXPRESS, listen on a dynamic port. The dynamic port is resolved by the client through SQL Server Resolution Protocol (aka SQL Browser) - which listens on udp/1434¹. This dynamic port is chosen at first startup, and will generally remain the same through future restarts (stored in the Registry) - but if there's a contention, SQL will choose a new port.

You can, and generally should, configure all instances of a production SQL server to use a static port. This makes firewalling much easier.

¹ The only reason that you have to put the named instance in, say, a connection string is so that the client knows to ask SSRP for the dynamic port. If it's a static or otherwise known port, you can simply point the client to Server=server.com:port, leaving off the instance name.