Managing connection strings in source controlled application that is continuously deployed to Azure websites

Question

Here's the scenario: I have multiple developers on an asp.net mvc 4 project. Each developer has a local database. The source control system is TFS at http://tfs.visualstudio.com. We're using Azure websites to host the site. We have also configured Azure websites for continuous deployment.

The source control system could be git, mercurial, TFS, etc. Honestly, I don't think it matters.

My question is how to accomplish these three things:

1. Each developer has his/her own connection string(s) locally (without them being in source control)
2. Azure has its own connection string(s) (without it being in source control)
3. Source Control doesn't show any connection information
4. The ability for each developer to F5 and run/debug/test the app locally.

We accomplished #1 by adding our individual connection strings to our machine.config so that there's no conflict between developer workstation setups.

I originally removed the connectionstrings section from web.config. In the Azure website (using the management portal, under Configure), I configured the connection strings, and after watching a Scott Hanselman video was under the impression that these would be dynamagically merged into my web.config upon deployment, but that doesn't appear to happen. Whenever I go to any page that hits the db, I get an error saying can't find the connection string (or some other db error related to the connection)

If I put the Azure connection string directly in web.config, Things work on Azure, but then the connection details are in source control visible to everybody.

After reading some more posts from Scott and David Ebbo it seems that I could put a blank connection string in web.config (with the correct name) and then Azure will overwrite the values correctly. I would then have to have the developers put their connection strings in their web.debug.config and then install the Slow Cheetah plugin so that they could F5 and test locally. They would also have to not check in the web.debug.config into source control. (Not that easy with TFS) This seems like a seriously burdensome kludge, that's bound to fail somewhere along the line.

I have to believe that this isn't that rare of a problem. How do other teams accomplish this?

Answer 1

After looking around, it appears that what I was asking isn't actually supported without a bunch of command line hacks to the pre/post build process. What we ended up doing is forcing developers to all create their own local databases, use trusted authentication, and establish a SQL alias that was used by all developers in the web.config. That way, it works locally for everybody, it doesn't expose any user names/passwords within source control, and Azure can still overwrite it when automatically pulled from source control.

Answer 2

Slow Cheetah is actually a nice solution. It's an extension to web.config transformations. Those transformations let you keep one web.config file and then for each deployment scenario you specify which changes you want to it. For example, your Release configuration will probably remove the debug attribute.

This can also be used to change connection strings. The transformations are applied during the deployment of your project to Azure.

What I've done in the past to make this also work with local development machines is use a web.config with an externalized connections.config file. Each developer created a connection.machinename.config file that was copied to connection.config on build in the post-build step. Those files don't have to be checked in and they can never cause conflicts because each machine name is unique.

The release/staging/.. configurations used a web.config transformation to replace the connection string element with a specific connection string for that deployment (and that way remove the dependency on the external config file).

Slow Cheetah offers some nice helpers for checking the result of these transformations at design time.