Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Change the port number for connections to SQL Server

By Dan Hess,

In a previous article, I showed how to find which port SQL Server is using to accept connections to the SQL Server engine (http://www.sqlservercentral.com/articles/networking/73238/). Here I will outline how to change that port number.

By default, SQL Server will use port 1433 for incoming communication. A named instance, by default, uses a dynamic port that it chooses from a list of available ports. 

Some reasons to change the listening port:

1) As the default port 1433 could be targeted by attacks it could be changed to limit exposure.  (This is a weak form of security and should not be used as a sole means of protection)

2) For named instances the port can be specifically defined to allow for disabling of the SQL Server browser service. 

3) For named instances, a specifically defined port enables firewalls to be placed around the SQL Server server.

Note: discussion of the above points is beyond the scope of this article

SQL Server Configuration Manager

We'll use the 'SQL Server Configuration Manager' to assign a specific port to our instance. Open it and expand the 'SQL Server Network Configuration' node, then single click 'Protocols for MSSQLSERVER' (if you're changing the port for a named instance, the node will be 'Protocols for <instance name>'). This will populate the right hand navigation pane; right click 'TCP/IP' and choose 'Properties'. The screenshot shows the navigation, as well as the difference between a default and named instance:

In the pop-up, choose the 'IP Addresses' tab. There will be a list of various adapters ranging from 'IP1' to 'IPAll'; for each adapter remove any zeros (0) from the 'TCP Dynamic Ports' dialog box (a zero in that box indicates that the database engine is listening on a dynamic port). In the 'TCP Port' dialog box, type the port number that you would like the engine to use to accept connections. In the screenshot, I've designated port 2866 as my listening port:

 

Note: make sure to modify all adapters 'IP1' through 'IPAll'

Click 'OK' to close the popup and implement the changes. 

For the changes to take effect, the SQL Server service needs to be restarted. In the left hand navigation pane, single click 'SQL Server Services'. This populates the right hand navigation pane with the services list.  Find the service responsible for the instance that you just changed, right click and choose 'Restart'. The screenshot shows this process, as well as the difference between the default instance and named instance service:

 

Conclusion

This process should be quite straight forward, although there are a few potential pitfalls:

  • Make sure that the port that gets assigned to the SQL Server engine is not already in use.
  • If the SQL Server Browser service is not running, connection strings will have to specify the port number in order to connect.
  • Host based firewall rules may need to be modified to allow connections into the newly specified port.

Total article views: 6018 | Views in the last 30 days: 2
 
Related Articles
FORUM

SERVICE ACCOUNT PASSWORD CHANGE ON CLUSTER INSTANCE

URGENT HELP= SERVICE ACCOUNT PASSWORD CHANGE ON CLUSTER INSTANCE

ARTICLE

Automation: Changing SQL Service Startup Parameters using T-SQL

Quick and efficent way to change SQL service startup parameters for 1000 instances!

FORUM

Change the Service accoutn to get SQl instance access

Change the Service account to get SQl instance access

BLOG

Rename or Change SQL Server Standalone Default Instance

Today, We will go through with the steps of renaming or changing SQL Server Standalone Instance. St...

BLOG

Rename or Change SQL Server Standalone Named Instance

Today, We will go through with the steps of renaming or changing SQL Server Standalone Named Instanc...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones