Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

2 Answers 2

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
    
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

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.