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?
migrated from stackoverflow.com Jul 2 '13 at 13:56
This question came from our site for professional and enthusiast programmers.
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
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).
By default SQL Server listens on TCP port number 1433, and for named instances, TCP port is dynamically configured. This means they select an available port when the SQL Server service is started. When you are connecting to a named instance through a firewall, you need to configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.