MSSQL Server on a non-default port - Complete configuration

Hello World,

I've been waking up on the right side of my bed from past couple of days (touch wood). Today is one such day - nothing can beat me today!!
On a serious note, a client of ours approached me to ask about configuring their SQL server instance to run on a non-default port. They had been advised to the like by a "security-expert". Now I won't lie but I did not have any experience of configuring a SQL server to accept connections on a non-default
port until today. Before I delve into the solution, a little background:

SQL Server version: MSSQL 2005 Enterprise Edition
Default Port:            1433
Default connection mechanism:
                              Shared Memory (Local Clients/SQL Server Management Studio)
                              Named Pipes AND/OR TCP/IP for remote connections

                             
I trusted Google to find my way around. It rightly came up with the following Microsoft documentation for connecting to MSSQL on a non-default port: MSDN Link



But you can't always have your cake and eat it too (at least not till you earn it anyway). As it turns out, post configuring the server, you would need to tell your client apps that they should not attempt connections on default port 1433 (isn't that obvious?!). In essence, you would need to modify your connection string to indicate that the client app should connect using TCP/IP and specify the port it should connect to. I am going to summarise the steps required for clarity:

I. Configure SQL server to accept connections only via TCP/IP on a port specified
   1. Open the SQL server configuration manager: Start > All Programs > Microsoft SQL Server 2005 > Configuration tools > SQL Server configuration manager
   2. Expand the node SQL Server Network Configuration on the left pane and click Protocols for where is you SQL server instance name (MSSQLSERVER by default)
   3. Ensure "Status" column for each of the protocols except TCP/IP listed on the right pane is "Disabled"
   4. Double click on the protocol name TCP/IP. This will open the properties window. Click on the "IP Addresses" tab
   5. For each IP address listed, set the value of "TCP Dynamic Ports" to blank. If there is a 0 as value, delete it. Next, set the port number you want SQL server to listen on against the "TCP Port" property.

II. Configure client application to connect to the port setup in the above series of steps
   1. The Data Source key in your connection string should be in the format ,. E.g. If you have configured your SQL server to run on port 1500, you would specify "Data Source=192.168.123.456,1500"
   2. To force your application to attempt only a TCP/IP connection, add NETWORK LIBRARY=DBMSSCON; in your connection string. For details see here
  3. Your connection string would look something like this now:

Data Source=192.168.123.456,1500;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Test your application with these settings. If you are not having a bad day, you should be good ;)
[NOTE: The "Network Library=DBMSSCON" is not mandatory as per my testing]



Happy Coding