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.
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:
Note that a different port number is specified using a comma, not a : like you may have expected.
Microsoft KB, http://support.microsoft.com/kb/914277
Posted on April 12, 2011, in Programming & Development and tagged change, change and connect mssql port number, connect, mssql port, number, port, port number. Bookmark the permalink. Leave a comment.