Sql-server – the difference between TCP port and TCP dynamic port

sql serversql-server-2012

Can anyone tell me the difference?

I tried to set either 1 port and left another blank also can work. I wonder what is the difference, or anything I missed out?

Best Answer

The reason both work for you is due to the SQL Browser Service running in your services (start - run - services.msc). The default instance of MS SQL Server will listen to port 1433, but named instances will use a 'dynamic' port and the SQL Server Browser service will map you to it. This is not good in large environments where you have security concerns and want to keep using the same ports so you can configure your ACLs.

To test this, you will want to review the links at the bottom, which makes you remove the '0' in the dynamic ports in all of your network interfaces in SQL Server Configuration Manager, and then set a hard coded port in the 'port #' section at the very bottom. You will then need to restart the MS SQL Server service and stop the SQL Server Browser service.

After doing this you can connect to the port number, NOT the instance name. So for example it would change from: SQLServer1\InstanceName

to:

SQLServer1,41450 (whatever custom port number you gave).

You can also make it pretty and clear by:

SQLServer1\InstanceName,41450 (include both the name and port # but it will always go to the port number, not the name).

Configure SQL Server To Listen In On A Specific Port #

SQL Server Browser

Related Question