Problem
Recently I came across a situation where an existing production SQL Server default instance was configured to use static TCP/IP port 48030 and the default 1433 port was disabled. A number of database applications are hosted on the same default SQL instance and these applications are connecting to SQL Server through port 48030. As per a new business requirement we need to host a new application database on the same SQL instance, but the application is unable to connect to the SQL instance because it us hard corded to use the default port 1433. In this tip we walk through how to configure a SQL instance to listen on multiple TCP/IP ports.
Solution
If there is a need to have SQL Server listen on multiple ports this can be done using SQL Server Configuration Manager.
In my case a third party application needs to connect to SQL Server using the default port 1433, but I already have port 48030 configured. Below is an easy solution without changing the configuration for the other applications.
Step 1:
Go to SQL Server Configuration Manager
–> Click on SQL Server Network Configuration
–> Click on protocols for MSSQLServer (For named instance click on the Protocols for Instance Name)
–> Right Click on TCP/IP protocol select properties
–> Go to IP Addresses tab
–> Under IP All section TCP Port, you can add multiple ports using comma separators as shown below
Step 2: Restart the SQL Server Service using the below commands or do this from with SQL Server Configuration Manager.
— You can start/stop SQL Services from command prompt as below
net stop mssqlserver
net start mssqlserver
Step 3: If a firewall is enabled add the new port number as an exception in the firewall.
Step 4: Check the connectivity using TELNET.
— To check the IP Address, execute the below command from dos prompt
ipconfig
— Place the IP Address and desired port number in TelNet
Telnet <IP Address > <Port Number>
Step 5: You can also verify the connectivity by creating an alias on the client following the below steps.
On a client machine, open the SQL Server Client Network Utility tool by typing the cliconfg command in the run window.
Enable the TCP/IP protocol from the General tab.
Click on the Alias tab and configure the alias as shown below, you can use the alias name while connecting to SQL Server.
Next Steps
- When configuring SQL Server as per best security practices, add a firewall between the database server and the internet and block TCP port 1433 and UDP port 1434 on your perimeter firewall. If named SQL instances are listening on additional dynamic ports, block these too and disable the SQL browser service. Also, assign static TCP/IP ports for SQL Server.
- Document the connectivity method and port details for disaster recovery
