ASP.NET MVC 4 - How should I encrypt the connection string to my production SQL server?

Question

I have an ASP.NET MVC 4 project which is to be deployed to Azure for production, in production I use a SQL Azure database. My problem is that I want to connect to the SQL Azure database only in the production deployment, and not when developing, and that the SQL Azure connection string should be encrypted.

Now, I can solve the first requirement through a Web.config transform, so that the database connection string gets substituted upon deployment to Azure. However, I don't see how to combine this with connection string encryption? How can one both encrypt the SQL Azure connection string and substitute it for the development connection string when deploying? Best practices for this scenario would be most welcome :)

Answer 1

I think a good solution here is to type the production <connectionStrings> section into Web.config and encrypt it, and then move the encrypted <connectionStrings> section into the transform file (e.g. Web.Release.config) and annotate it so that it replaces the whole <connectionStrings> section upon transformation. This accomplishes the goal of deploying Web.config with production connection strings that are also encrypted.

I've followed the guide in "Securing Your Connection String in Windows Azure", parts 1, 2, 3 and 4 to understand how to encrypt Web.config. I suggest that for a full reference, others do the same. I will outline the main steps I've performed to solve my scenario.

After updating the <connectionStrings> section in Web.config with production settings, I installed the Pkcs12 Protected Configuration Provider and ran aspnet_regiis.exe to encrypt the section (in a Visual Studio command prompt, situated in the project directory):

aspnet_regiis -pef "connectionStrings" "." -prov "CustomProvider"

I also added a definition of CustomProvider to Web.config:

<configProtectedData>
  <providers>
    <add name="CustomProvider" thumbprint="<your thumbprint here>"
       type="Pkcs12ProtectedConfigurationProvider.Pkcs12ProtectedConfigurationProvider, PKCS12ProtectedConfigurationProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=34da007ac91f901d"/>
  </providers>
</configProtectedData>

Afterwards I moved the encrypted <connectionStrings> section into Web.Release.config (which is used to transform Web.config upon deployment to Azure), and annotated the section so that it replaces the corresponding section in Web.config:

connectionStrings configProtectionProvider="CustomProvider" xdt:Transform="Replace">
...
</connectionStrings>

Finally I restored the development <connectionStrings> section in Web.config. I have tested this solution and found that the deployed Web.config contains the encrypted <connectionStrings> section, just as I was after.