Given an ASP.NET MVC 4 app, how should I configure and encrypt the SQL Azure connection string?

Question

Hiya

I am developing an ASP.NET MVC 4 application in Visual Studio 2012, which is to be deployed as an Azure Cloud Service. I want to use a local database during development, and switch to a SQL Azure server when deploying to Azure. The SQL Azure connection string should be encrypted. How is this typically accomplished? I can't see how to combine the usual Web.config transform technique with encrypted data (via aspnet_regiis).

See also my stackoverflow question on the matter.

Answer 1

Hi aknuds1,

There is a nice blog post (4 part) in the SQL Azure Team Blog on Securing Your Connection String in Windows Azure: Part 1, Part 2, Part 3 (encrypting your Web.Conifg) and Part 4. 

This is typically accomplished by using the aspnet_regiis -pef "connectionStrings" "." -prov "CustomProvider" command, just like described in the stackoverflow link you shared.

Hope this helps!

---

Cheers, Carlos Sardo

Answer 2

Hi Carlos

The problem is that encrypting Web.config only addresses the requirement of encrypting connection strings. It doesn't deal with the requirement to replace connection strings with production equivalents (when deploying to Azure). See what I mean?

Answer 3

Hi aknuds1,

I think I see what you mean:

In your local/dev Web.config you probably have it unencrypted. And in your Prod/Live Web.config you have it encrypted... which generates a different XML (on you <connectionStrings> section) with more elements, etc.. and this makes it more difficult for you to write the proper transformation syntax? Is this the correct assumption for you problem? 

...If so, It is possible to replace entire elements.

I know for sure that when you package your Azure Hosted Service, in Visual Studio, it respects all web.config transformations you may have. So, this is not really a problem that you face only when deploying to Azure. Don't you agree?

---

Best Regards,
Carlos Sardo

Answer 4

I think you've got the essence, Carlos. I'm new to Web development, so I'm pretty open to suggestions as to how it should be done. The point is that the production connection string should be encrypted, and it needs to make its way into the Web.config somehow when deploying to production.

Most of all I find it pretty strange that I can't find any examples of people doing this, a sentiment mirrored by "Security Geek"'s comment on the very blog you linked to!

But yeah, I've been thinking the same thing, that maybe I should encrypt <connectionStrings> in Web.config and then paste it into Web.Release.config, so that I can replace the section in the transformation.

I'll give it a shot, thanks.

Answer 5

Hi aknuds,

Just remembered something... You could also try to place you connection string info in individual files (on for each build config):

On your Web.Config (debug, release, test, dev, prod, etc) like this:

<?xml version='1.0' encoding='utf-8'?>
<configuration>
    <connectionStrings configSource="configs/connections.<BUILD_CONFIG_HERE>.config"/>
</configuration>

And have a separate connections.config for each build configuration you have (ie: connections.dev.config, connections.prod.config (<- this one has your encrypted connStr), etc).

In short words, the only XML transformation that you need to apply on your Web.config is in the configSource attribute, which you can easily do.

Finally, when you build your WebApp in the PROD/RELEASE configuration, the Web.Config that it will produce will be pointing to the encrypted config/connections.prod.config file.

I think this solves your problem.

Hope this helps!

---

Best Regards,
Carlos Sardo

Answer 6

But it's only slightly more convenient than your previous solution, i.e. to replace the <connectionStrings> section, right? I've got that solution working already, but good to know this other option, thanks!