Change default MSSQL Port and Connect

Changing default port of Microsoft SQL Server differs depending on the version. The following is for the latest version of MSSQL only.

If you’re using 2005, the steps are similar to those below, but the area you need to go is the SQL surface area configuration instead.

If you’re using 2000, it’s time to upgrade. 2000 is pretty out of date. We’ll probably be looking at something newer than SQL 2008 before long.

MSSQL 2008:
1) Goto: SQL Server Configuration Manager > SQL Server Services > (right click on your server instance) > Stop

2) Goto: SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for [Instance Name] > Right click TCP/IP (make sure it’s enabled) > IP Addresses Tab

3) Once you’re at this tab find the IP that corresponds to the address of your network adapter that is connected to the router (IP1 in my case though on servers this could easily be variable).

4) Remove 0 from “TCP Dynamic Ports” and set the “TCP Port” field to the number you want (default is 1433). If you scroll to the bottom you will also note there is an “IPAll” which I also did the same for.

5) Change any firewall rules that may block the connection (add a new port in Windows firewall).

6) Restart service instance. (step 1)

Some blogs and articles I came across mention starting the SQL Server Browser, however, I would like to mention that although you should have this service running for a couple reasons, the above configuration works without the browser and doesn’t technically need it to connect remotely if you know exactly which port you’re connecting to.

To connect remotely on different port make sure TCP/IP is enabled for the server instance in configuration manager, and in management studio or your application connection string specify:
servername,portnumber\instancename

Note that a different port number is specified using a comma, not a : like you may have expected.

References
Microsoft KB, http://support.microsoft.com/kb/914277
SQLServerPedia, http://sqlserverpedia.com/blog/sql-server-tutorial/how-do-i-change-a-sql-port-number/

Advertisements

About Ronnie Diaz

Ronnie Diaz is an enterprise software engineer responsible for front-end and back-end development for companies in many industries. Heavily involved in cloud development, online retail, e-commerce and electronic ordering, fulfillment and customer relational systems.

Posted on April 12, 2011, in Programming & Development and tagged , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: