Tuesday, September 21, 2010

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

Tuesday, September 7, 2010

A UI to edit/view config files for .NET Apps

Hello World,

I was wondering if I could provide my deployment team with a nice UI that would present app.config or web.config AppSettings section for viewing or editing. After some deliberation, I fired up my Visual Studio and started playing around with the methods of the Configuration class (found under the System.Configuration namespace). I noticed a method called OpenExeConfiguration that looked promising; it had all the methods that looked like having the ability to traverse the config file and read/write key value pairs.
Each discovery though comes with issues! I tried several ways of accessing the config file from another app but no matter what I tried, the HasFile method of Configuration class would return "false" indicating that there was no config file in the location I specified!! Then I began googling (what else would you do?!) and found this useful link: Blog Post
It appears that you need to tell the OpenExeConfiguration method where the EXE is and it will try to locate the config file by concatenating the exe name with ".config". E.g., if your exe is called MySuperApp.exe and could be found in the directory C:\, you would write something like this:

Configuration conf = ConfigurationManager.OpenExeConfiguration(@"C:\MySuperApp.exe");

Once I am through writing this UI app, I will try posting the code in this blog.

Until next time,
System.AllIsWell = true;
:)