ASP.NET connection string deployment best practice

Question

I've collected a (hopefully useful) summary of the ways I've researched to accomplish the subject of this post, as well as the problems I have with them. Please tell me if you've found other ways you like better, especially if they resolve the problems that the methods I mention do not.

1. Leave connection strings in web.config and use XDT/msdeploy transformation to replace them with settings according to my active build configuration (for example, a web.PublicTest.config file). My problem with this is I merge and bury a few server-specific settings into an otherwise globally identical file with many configuration elements. Additionally, I cannot share connection string definitions among multiple peer-level applications.

2. Specify a configSource="DeveloperLocalConnectionStrings.config" value for connection strings in web.config, and XDT transform this value to point to one of the multiple environment-specific files in my code-base. My problem with this is I send passwords for all my environments to all destinations (in addition to SVN, of course) and have unused config sections sitting on servers waiting to be accidentally used.

3. Specific connection strings in the machine.config file rather than web.config. Problem: who the heck expects to find connection strings in the machine.config, and the probability of surprise name collisions as a result is high.

4. Specify a configSource="LocalConnectionStrings.config", do not transform the value, and edit the project xml to exclude deployment of the connection string config. http://msdn.microsoft.com/en-us/library/ee942158.aspx#can_i_exclude_specific_files_or_folders_from_deployment - It's the best solution I've found to address my needs for a proprietary (non-distributed) web application, but I'm paranoid another team member will come one day and copy the production site to test for some reason, and voila! Production database is now being modified during UAT. (Update: I've found I can't use one-click publish in this scenario, only msdeploy command line with the -skip parameter. Excluding a file as above is the same as setting it to "None" compile action instead of "Content", and results in the package deleting it from the deployment target.)

5. Wire the deployment package up to prompt for a connection string if it isn't already set (I don't know how to do this yet but I understand it is possible). This will have similar results to #4 above.

6. Specify a configSource="..\ConnectionStrings.config". Would be great for my needs, since I could share the config among the apps I choose, and there would be nothing machine-specific in my application directory. Unfortunately parent paths are not allowed in this attribute (like they are for 'appSettings file=""' - note also that you can spiffily use file= inside a configSource= reference).

p.s. some of these solutions are discussed here: http://stackoverflow.com/questions/1221708/asp-net-configuration-file-connection-strings-for-multiple-developers-and-depl

Answer 1

1. Use the hostname as key for the connectionstring, that way you can choose the datasource automagically. Make sure the choosing routine is not buggy (change hostname - test!)...

2. Don't put it in the web.config, write an ini file, that way there is no XML encoding.

3. Encrypt the password therein, with private/public key (RSA/PGP). Don't ever use cleartext, or a symmetric key, which is just as bad.

Answer 2

Check my following blog post: Protecting asp.net machine keys and connection strings

If you do use Quandary's answer, use a key that's not in the site's folder, just like asp.net does with protected config sections.

We manually approve changes to the web.config that go into staging/production. We use integrated instead of username based where possible, but an option we've used in the later case is to just have placeholders for the username/passwords in SVN.

We've used separate config files in the past, but we have run into other type of issues with web.config modifications, so we have been locking it in a single file lately.

Answer 3

When using SQL Server, you can also use Integrated Security / SSPI and add the WebServer Computer Login to the Sql Server.

That way you dont have to expose anything in the web.config and you can grant roles to that login like you would to any other DB user.

Though you have to understand the implications and security considerations to be taken, because any malicious code executed as THAT machine will have access to the Sql Server.

with regards Ole