As you probably know, the default communication port used by SQL Server for TCP / IP mode connections is TCP 1433. This simple - but far from trivial - configuration parameter is often overlooked by system engineers and developers because most client software and drivers that interact with SQL Server are using it without letting you know about that.
A perfect example of this is given by most Firewalls, such as Kerio Connect:
Or even firewalld, the well-known firewall which is built-in in most modern Linux distributions, such as CentOS, together with the firewall-config configuration GUI as shown below:
In both cases, as you can see, MS SQL Server is configured as a service and not as a port, assuming that the port used to connect is the default one (TCP 1433). Needless to say, if we are forced to use a non-standard port, this type of configuration will no longer be fine: we will be forced to specify it manually.
How do you change the default TCP / IP port used by an instance of MS SQL Server? Unlike most similar products, this configuration parameter is not immediately accessible. To change this setting we need to make good use of the Sql Server Configuration Manager tool, a configuration software shipped with the SQL Server distribution package: this practical GUI takes the place of the classic configuration file used by products like MySQL Server, PostgreSQL Server, MariaDB and other DBMS solutions available for Windows and/or Linux.
Once the GUI is launched, we have to expand the SQL Server Network Configuration node and select the item related to the protocols corresponding to the SQL Server instance that we want to configure: in the example above there are two instances installed on the system , called MSSQLSERVER and SQL2016 respectively. Once done, we need to double-click on it (or right-click > Properties) on the protocol node that we want to modify necessary from the list shown in the right part of the screen - in our case, TCP / IP.
This will allow us to access the protocol configuration panel, where we'll be able to configure various parameters such as the Keep-Alive duration (in milliseconds).
In order to change the TCP port, navigate to the IP Addresses tab and change the following parameter for each of the available IP:
- TCP Dynamic Ports: leave the field blank (if there's a zero, remove it)
- TCP Port: the desired TCP port (for example 1435)
IMPORTANT: Be sure to change all the IP Address listed there (IP1, IP2, IP3 and so on)![/alert]
After making the changes, remember to restart the SQL Server service and to properly configure your applications (firewall, connection strings and such) to use the new TCP port.
That's it for now: happy configuration!
