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.
<connectionStrings>
<add name="TDL" connectionString="blah blah" providerName="yada yada"/>
</connectionStrings>
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.
About Scott
Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.
About Newsletter
Just a clarification...
In this approach, only someone with access to your private key can decrypt the connection string info. This would also work on-premise.
Sample config file section to define correct cert to use:
<configProtectedData>
<providers>
<add name="CertificateProvider"
thumbprint="...."
type="Pkcs12ProtectedConfigurationProvider, YourApp.Configuration, Version=1.0.0.0, Culture=neutral, PublicKeyToken=..." />
</providers>
</configProtectedData>
Having said that, the point of being able to adjust application settings and connection strings without redeploying your application is huge advancement. I am in total agreement that having other facilities/options as Scott described would be a great addition.
1. Is there a good article out there to help someone decide whether Sql Azure is going to be good enough for their needs
2. Is there a good reference on ballparking the annual cost of a SQL Server Standard instance
Best
Raj
I'm trying to implement similar thing myself. I understand how Azure overwrites your connection string. That's cool.
What I don't understand is the steps that you go through when you're developing locally. Do you have to enter your local connection info when you're developing locally, and then remember to remove it before you commit to GitHub?
And if there are multiple developers, each with their own local environments, how do you manage connection string info so that they don't get accidentally uploaded to GitHub?
I know that the Database.Open method takes the name of a connection string as a parameter. Is there any way to get this to work with ConfigurationManager or the like?
Cheers!
Comments are closed.
https://github.com/shanselman/thisdeveloperslife