In a previous article, I showed how to find which port SQL Server is using to accept connections to the SQL Server engine (http://www.sqlservercentral.com/articles/networking/73238/). Here I will outline how to change that port number.

By default, SQL Server will use port 1433 for incoming communication. A named instance, by default, uses a dynamic port that it chooses from a list of available ports. 

Some reasons to change the listening port:

1) As the default port 1433 could be targeted by attacks it could be changed to limit exposure.  (This is a weak form of security and should not be used as a sole means of protection)

2) For named instances the port can be specifically defined to allow for disabling of the SQL Server browser service. 

3) For named instances, a specifically defined port enables firewalls to be placed around the SQL Server server.

Note: discussion of the above points is beyond the scope of this article

SQL Server Configuration Manager

We'll use the 'SQL Server Configuration Manager' to assign a specific port to our instance. Open it and expand the 'SQL Server Network Configuration' node, then single click 'Protocols for MSSQLSERVER' (if you're changing the port for a named instance, the node will be 'Protocols for <instance name>'). This will populate the right hand navigation pane; right click 'TCP/IP' and choose 'Properties'. The screenshot shows the navigation, as well as the difference between a default and named instance:

In the pop-up, choose the 'IP Addresses' tab. There will be a list of various adapters ranging from 'IP1' to 'IPAll'; for each adapter remove any zeros (0) from the 'TCP Dynamic Ports' dialog box (a zero in that box indicates that the database engine is listening on a dynamic port). In the 'TCP Port' dialog box, type the port number that you would like the engine to use to accept connections. In the screenshot, I've designated port 2866 as my listening port:

Note: make sure to modify all adapters 'IP1' through 'IPAll'

Click 'OK' to close the popup and implement the changes. 

For the changes to take effect, the SQL Server service needs to be restarted. In the left hand navigation pane, single click 'SQL Server Services'. This populates the right hand navigation pane with the services list.  Find the service responsible for the instance that you just changed, right click and choose 'Restart'. The screenshot shows this process, as well as the difference between the default instance and named instance service:

Conclusion

This process should be quite straight forward, although there are a few potential pitfalls:

• Make sure that the port that gets assigned to the SQL Server engine is not already in use.
• If the SQL Server Browser service is not running, connection strings will have to specify the port number in order to connect.
• Host based firewall rules may need to be modified to allow connections into the newly specified port.