Modifying SQL Server 2005 Port Numbers

And so the tests continues...One of the things that I need to test during the upgrade was database connectivity. We know the standard osql, sqlcmd, isql and Query Analyzer tests from a remote client where you supply either a Windows or SQL Server account to login to the database server. Once connected, we are practically sure that database connectivity works fine. But that doesn't stop there. We need to make sure that other applications can connect to the database server as well. This is where the importance of SQL Server port numbers come into the picture. We usually recommend to change the default port numbers 1433 and 1434 for security reasons as anybody who knows SQL Server has a high probability of knowing this port. How do we change the port number that SQL Server uses. Open SQL Server Configuration Manger -> SQL Server 2005 Network Configuration -> Protocols for InstanceName. Open the TCP/IP protocol properties either by right-clicking and selecting Properties or double-clicking. Go into the IP Addresses tab of the TCP/IP Properties window, scroll all the way to the bottom to the IPAll section, change TCP Dynamic Ports to be blank and put any value, say 1234, or whatever your chosen SQL Server port number is, in to the TCP Port box (I just used 1433 for ease of explanation). Make sure you restart your SQL Server service for the changes to take effect.



NOTE: If the value of Listen All on the Protocol tab is yes, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item under IPAll. If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server 2005 is the value of the TCP Dynamic Ports item for a specific IP address.