Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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

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

share|improve this answer
2  
Note that there is a comma separating the server name and the port number, not a colon (which web browsers use). I have often typed a colon and wondered why it didn't work. – Greenstone Walker Jul 2 '13 at 23:28
1  
Ha.... I had set it for IPn, per the manual. But didn't do it for IPAll. That fixed my problem. Thx – Wouter Jun 23 at 10:43

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.