Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Configuring SQL Server to Use Multiple Ports

MSSQLTips author Jugal Shah By:   |   Read Comments (2)   |   Related Tips: More > SQL Server Configurations
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

SQL Server Configuration Manager

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.

SQL Server Client Network Utility

Enable the TCP/IP protocol from the General tab.

SQL Server Client Network Utility Enable

Click on the Alias tab and configure the alias as shown below, you can use the alias name while connecting to SQL Server.

SQL Server Client Network Utility Ports


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


Last Update: 9/13/2011


About the author
MSSQLTips author Jugal Shah
Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, October 04, 2012 - 1:02:04 PM - Hima Read The Tip

Thank you for this article. This helped resolve one of our issues.


Monday, November 28, 2011 - 10:55:45 AM - robin Read The Tip

good




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.