Question: How to determine or configure the port used by a Microsoft SQL Server database instance? How to make the port number of a Microsoft SQL Server database instance static versus dynamic?
Verify the TCP/IP Port Used by a MS SQL Server Instance
1. From the Microsoft SQL Server database server, launch the SQL Server Configuration Manager. In a typical installation, this can be found at Start > Programs > Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager.
2. Expand SQL Server Network Configuration and select the protocols for the MS SQL Server database instance. In this example, there is a named instance of MS SQL Server called PRIMAVERA.
3. Right click on TCP/IP and select Properties.
4. Select the IP Addresses tab and locate the IP All section. The port should be specified as the TCP Port property. If there is a value for the TCP Dynamic Ports property, then the instance is using a non-static port and the value shown is the current port.
Note: When using Dynamic Ports, each time the database server starts, SQL Server will attempt to use this same port number, however if this port number is in use by another service, a new port number will be dynamically selected by the Microsoft SQL Server engine.
Set the TCPIP Port Used by a MS SQL Server Instance to Static
1. Launch the SQL Server Configuration Manager.
2. Expand SQL Server Network Configuration Manager.
3. Select Protocols for relevant SQL Server instance.
4. In the right-pane, double-click TCPIP to open the TCPIP Properties window.
5. Click on IP Addresses tab and scroll down to access the IPAll section. In this section, remove any value in the TCP Dynamic Ports field and enter a valid, available port in the TCP Port field. Any available port can be used. If there are multiple MS SQL Server instances resident on the same server, they must all use unique ports for TCP/IP connection.
6. Click Apply and then, OK.
7. Restart MS SQL Server instance service for the changes made in Step 5 to be applied.