Scott Hanselman

How to keep your ASP.NET database connection strings secure when deploying to Azure from Source

January 8, '13 Comments [13] Posted in ASP.NET | Azure
Sponsored By

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.

Azure hides connection strings

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.

Git Deployments from GitHub directly into Azure

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.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by ORCS Web
Tuesday, January 08, 2013 3:31:52 AM UTC
Tuesday, January 08, 2013 3:34:39 PM UTC
Scott, one thing I'd note is that this handling of connection strings only applies to the WebSites service in Azure. If you're using the more sophisticated PaaS Cloud Service approach you don't get this nice feature.

Just a clarification...
CuriousTechie
Tuesday, January 08, 2013 6:57:16 PM UTC
Thanks CuriousTechie. Sounds like that SHOULD be a feature, eh?
Tuesday, January 08, 2013 8:03:13 PM UTC
Yes, that would be a welcome feature for cloud services, too.
Jeff Madison
Tuesday, January 08, 2013 8:15:46 PM UTC
For PaaS Cloud Service, we encrypt the connection string section and any other sensitive section with the SSL certificate. The developer's can encrypt having only access to the public key. The SSL certificate administrator adds the SSL certificate to the management portal. How to do this can be found at http://archive.msdn.microsoft.com/pkcs12protectedconfg

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>
Phil Bolduc
Saturday, January 12, 2013 4:52:14 PM UTC
Also of note is the "app settings" section in the screen shot as well. This is useful for doing the same type of transform as the connection strings, but with other app settings. I am using that for storing an SMTP password for a project that is publicly available in github.
Tuesday, January 15, 2013 3:29:32 PM UTC
@Phil Bolduc That still doesn't give the separation that a lot of companies require between the Development teams and operational ones. The approach above applied to the PaaS offerings would be a great addition.
Thursday, January 17, 2013 2:25:26 PM UTC
I have an offtrack question - If I am using Entity Framework, is there a way have equivalent to package manager console to update migrations, update database and others?
Wednesday, January 23, 2013 6:58:34 PM UTC
@Lex My main point was that the .NET Framework already has a facility for keeping connection strings secret. Scott's initial question was: how do we keep our database connection strings a secret?. In many cases, the developer is not in control of how the connection string is provided to data access code. For example, when using SQL Server as the ASP.NET session state provider, the connection string name is provided in the configuration. Another example would be SQL Role and SQL Profile providers.

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.
Phil Bolduc
Monday, March 11, 2013 4:50:56 AM UTC
Somewhat related. I notice you're using a SQL Server connection rather than Sql Azure. 2 questions:

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
Wednesday, April 24, 2013 12:14:38 AM UTC
Scott, I watched the show with Rob. Good stuff!

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?
David
Wednesday, June 19, 2013 12:45:52 AM UTC
This is amazing. I have been trying to figure out how to keep my PROD connection strings away from my DEV's eyes for a while. Thanks!
Jason
Friday, November 08, 2013 9:55:45 AM UTC
Having a bit of trouble getting this to work with an ADO connection.

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!
Anth
Comments are closed.

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.