Configuring SQL Server to Use Multiple Ports

By: Jugal Shah | Read Comments (1) | Print Jugal has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2. Related Tips: More

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 Step

• 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

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code