Reasons for changing the SQL Server port number and how to change it

In my last post, I have shared the information on how to specify the port number to connect to the SQL Server Instance but, now I would like to share some information on 

Why should we change the SQL Server port number? Where and How to change the SQL Server port?

Why should we change the SQL Server port number?

The SQL Server instance can have a static or dynamic port allocation.

By default when SQL server is installed , it installs the default instance, unless you provide a different instance name. So, if it’s a default instance then its given Static port 1433.

But if the SQL Server is a named instance then it will give a Dynamic port the SQL Server which may change when the SQL Services are restarted.

As the default port 1433 of SQL is well known , it’s the security best practice to change the port number to minimize the potential attacks. The other reason is if other application is using that particular port or the administrator can’t open the port then we need to change the SQL Server port number.

Where and how to change the SQL Server port?

To change the SQL Server port and to change the static to dynamic port allocation follow the steps below:

1. Open SQL Server Configuration Manager 

2.  Then click SQL Server Network Configuration

3.  On the right side, select the instance you want to configure to listen on a specific port. In the below screenshot I have just one instance so selecting that.

4.   To change the port, double click the instance and select the TCP/IP protocol  right click TCP/IP protocol and select Properties.

5. Click on the properties and once clicking the properties TCP/IP Properties dialog box appears, in that box click the IP Addresses tab.

 If we see the above screenshot, the TCP Dynamic Ports is not set to any value which means the database engine is listening on static ports. We can change this to Dynamic port by setting it to “0” in the TCP Dynamic Ports field.

If you see the TCP Port field it is set to 1433, this is where we need to change the SQL Server Port value. For example, if you want to change the value to 4141 then in place of 1433 change it to 4141 and click on OK.

6.  Once you set the new value select SQL Server Services in the SQL Server Configuration Manager and in that right click SQL Server and restart it to see the change.