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: ASP.Net configuration file -> Connection strings for multiple developers and deployment servers

Answer 1

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