Why SQL Server port number is not default 1433?

Posted on

Question :

Installed latest SQL Server. It is listening on port number 62628 according to server log. Why is the port number is not the default of 1433?

How to change the port number back to the default 1433?

It was installed on Windows 10. The server name is MachineNameSQLEXPRESS. I did not give a name.

Answer :

Contributed by AlwaysLearning:

Configure the Windows Firewall to Allow SQL Server Access:

By default, named instances (including SQL Server Express) use dynamic ports. That means that every time that the Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. If other instances of the Database Engine are installed, it will probably use a different TCP port.

Why would it listen to 1433? 🙂 1433 is a well-known port for a default instance. For a named instance ( like your express), the db engine asks Windows for an available port number (there’s a range for these purposes). This port number is stored in the registry and used for subsequent starts of the engine. Unless the port is unavailable, then Windows is asked for a different port (or, if “static” port is chosen, the engine refuses to start).

Note that the documentation referred to in the other answer is incorrect. It suggests that a different number is chosen at every startup. That isn’t correct. Imagine the amount of work the “firewall-people” would have! 🙂

For instance, here’s the registry path for an instance on my machine, named “A”:
ComputerHKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerAMSSQLServerSuperSocketNetLibTcp

In here, there’s an entry named “TcpPort” with the value of the port number that this instance is using. This is why I see the same port number even after re-starting my instance.

Leave a Reply

Your email address will not be published. Required fields are marked *