Jeremy highlights some foobar code that exposed a connection string to a database via a public static property on a class.  In the comments, there was some discussion about what to do with the connection string.  I’d like to address some common thoughts on connection strings (mostly concerning keeping the connection string secure).

1. Secure the connection string:
   What exactly does that mean?  Secure from whom?  Hackers?  Developers? Random company employees who don’t have access to the server anyway?  The connection string has the information needed to log on to a database as a particular user (ignoring integrated authentication for now).  The question each organization has to answer is “who do I want to keep this information from”?  If the answer is the general population of company employees and outsiders, then you don’t have to do much of anything.  If you store it in plain text in a configuration file on the server, only those with access to the server can see that information.  This assumes that security on the server is set up correctly on the server.  If you want to keep it from server administrators, then I’d ask why you have server administrators working for you if you distrust them that much.


2. Select the simplest deployment scenario that will secure your connection string from the people you don’t trust




• General population – anything will work because they don’t have access to the server


• Server Administrators – you have a management problem, and the admins you don’t trust need to be fired. 


3. Next plan for “IF” the server is compromised.




• Use an account in the connection string that only has read privileges.  Grant execute explicitly for stored procedures that do updates.


• Don’t use the “sa” account.


• Use network restrictions to only allow communication to the database server from the application server (yes, put your network admins to work).  This is really simple, actually.  The network simply won’t route packets to the database server unless they come from the application server. 

The above are my preferences.  The above tends to simplify deployments because there are few hoops to jump through because the connection string often resides with other application configuration.  I’ll also address the sometimes popular thought to store the connection string in the registry and even encrypt it!

• I think storing the connection string in the registry is pointless.  Why?  Because it doesn’t gain you anything.  It only makes deployment more difficult.


• Encrypting the connection string is making deployments even more difficult and just try to cope with a broken IT department.  Often the justification is to protect _when_ the machine is compromised.  First, if someone gains ADMINISTRATIVE access to the machine (in order to read the encrypted value from the registry), they own the machine.  This is really bad, and if they care about your database, they can just get the private key from the application (that is on that machine) and decrypt it anyway.  (even with DPAPI).

My preference?  Store it in plain text in the configuration file for most applications.  If configuration files float around, and there is risk within the company (you have co-workers who should be fired), 1.  stop the configuration from floating around, and 2.  perhaps encrypt the connection string inside the configuration file (but realize that anyone with access to the private key can still get it).  If you are really concerned, run your application with a service account and use integrated authentication.  This poses another problem.  How are you securing the password to the service account?  And the cycle repeats itself.

Bottom line:  Security is not an absolute.  It’s not a switch that’s turned on by using a published “best practice”.  It’s a number of steps that are determined by the established risks.  Evaluate the real risks with your particular application and make objective decisions based on _real_ needs.  Some “security steps” like storing the connection string in the registry often don’t deliver value but instead hurt deployment ease and make application support more difficult.  Don’t fall prey to Mordac, the Refuser, working in your I/T department,  who may insist on some inane “security measures”.  Be practical.