How to keep your ASP.NET database connection strings secure when deploying to Azure from Source
I was working with Rob Conery today during lunch moving the This Developer's Life podcast website to Git. We recorded the whole upgrade and migration experience and it will be up as a video on TekPub soon.
Shameless Plug: Check out my TekPub show "The Source" available on TekPub.tv.
A question came up during the deployment. We moved our database from SQL Compact to a SQL Server instance in Azure. But, how do we keep our database connection strings a secret? We are pushing our source code to GitHub and don't want our connection strings and passwords committed as well.
Sometimes you'll make a Web.Release.Config file and keep them in there. Sometimes you'll make a connectionStrings.config and refer to it from the web.config but never deploy it.
However, Azure lets you keep those configuration settings in Azure securely so they never end up in code. Note the screenshot below. There's a Connection String named "TDL." This matches the name that we reference in code and the name of the connection string in our web.config.
Our ASP.NET Web Pages database call was to Database.Open in WebMatrix.Data. It's pretty simple. Dead simple, in fact.
var db = Database.Open("TDL");
This used to refer to a TDL.sdf SQL Server Compact Edition (SQL CE) file. Then we moved it into a connection string.
<add name="TDL" connectionString="blah blah" providerName="yada yada"/>
The idea is that if your Azure configuration (as seen in the screenshot above) has an value with the same name, that secure value from Azure will get replaced when your app is deployed.
GOTCHA WARNING: I spent twenty minutes trying to figure out why my value wasn't getting updated. My app was was acting as if there was no connection string value at all. I was getting "Connection string "TDL" was not found." After much gnashing of teeth I discovered (thanks to David Ebbo's help) that I had put my <connectionStrings> element inside of <runtime> within the web.config and the error was being swallowed. Apparently that section is pretty relaxed about elements it doesn't understand - certainly more relaxed that the system.web section. Regardless, in the hope that it save some visitor (perhaps you!) time, make sure your connectionStrings element is right at the tine under <configuration/>
All in all, this worked very well for us.
It let us put our code on GitHub, setup automatic deployment to Azure directly from GitHub, while still keeping our SQL connection strings (and any additional production settings) private.