SQL Server Port Number

Question

I am wondering which ports are used by the SQL Server database engine? I need a port number for recording configuration scripts to provide access to a specific port on a computer installed with SQL Server to make it secure. A related question is whether the SQL Server database engine will use one static port number to serve all client requests or using one port for each request?

By the way: my background is SQL Server 2008.

thanks in advance George

Answer 1

The default instance will by default listen on tcp / 1433. It may also listen on the named pipe (tcp / 445), but I think it should be explicitly enabled these days.

Named instances, such as SQLEXPRESS, listen on the dynamic port. The dynamic port is resolved by the client through the SQL Server resolution protocol (also known as SQL Browser), which listens on udp / 1434 ¹ . This dynamic port is selected at the first start and, as a rule, remains unchanged in future restarts (stored in the registry), but if there is competition, SQL will choose a new port.

You can and should usually configure all production SQL server instances to use a static port. This makes the firewall a lot easier.

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

Answer 2

1433 is what SQL Server uses by default. It has at least SQL Server 6.0.

As a rule, for security reasons, you do not want to open it to the world. People should only access your database through the app / web service. SQL Server direct pipe connections are teeming with security risks.

All sessions will use this port (port 80 for the website), but you can change it using the SQL Server configuration tool described here .

Answer 3

A very simple and programmatic way is to simply query the local_tcp_port column in sys.dm_exec_connections:

 select local_tcp_port from sys.dm_exec_connections where local_tcp_port is not null 

If this does not return any results, this is probably due to the fact that the server is on this computer and there are no connections to other machines, so there are no tcp connections.

In this case, if you are querying sqlcmd, just put "tcp:" in front of the server name, for example.

 sqlcmd -E -S tcp:(local) 

Or, if you are connecting to SqlClient, you can add "Network Library = dbmssocn" to force tcp, for example.

 string connStr = "Server=(local); Integrated Security=true; Network Library=dbmssocn"; 

Answer 4

: 1433 by default. However, this port can be changed, and if you are dealing with multiple instances, each of them will have a different port.

A quick Google search includes the following link:

http://decipherinfosys.wordpress.com/2008/01/02/finding-the-port-number-for-a-particular-sql-server-instance/

... and I'm sure Technet will have more information.

Answer 5

Sometimes the port is not 1433

From http://www.php.net/manual/en/function.mssql-connect.php#76256 Go to the registry using regedit.exe in HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ SQLEXPRESS \ MSSQLServer \ SuperSocketNetLib \ Tcpp .

One of the nameValue pairs has the name TcpPort and a value that is the port that SQL Server listens on.