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 :)

Why the downvote? If there's anything specifically wrong with my question I'd welcome constructive criticism. — aknuds1
– aknuds1, Commented Aug 12, 2012 at 19:14

Co7e · Accepted Answer · 2016-10-28 17:20:21Z

8

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.

edited Oct 28, 2016 at 17:20

Co7e

1,00810 silver badges17 bronze badges

answered Aug 12, 2012 at 18:06

aknuds1

68.5k69 gold badges208 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kay Over a year ago

updated links: Part 1 , Part 2, Part 3, Part 4, Pkcs12 Provider.

kay Over a year ago

The provider solution contains an InstallShield Limited Edition project that would not open because I didn't have InstallShield installed. I ended up deleting the Installer project and creating my own with the following link [ InstallShield ](msdn.microsoft.com/en-us/library/dkkx7f79(v=vs.110).aspx )

aknuds1 · Accepted Answer · 2012-08-12 13:17:52Z

1

You would encrypt the section within the web.config file.

See MSDN about how to encrypt sections of your web.config file.

http://msdn.microsoft.com/en-us/library/zhhddkxy.aspx

What I would setup in your case is a POST build event that runs that command line option, conditionally only for that specific build configuration.

For example:

if $(ConfigurationName) == Release_Production {path-to-.net-framework}\aspnet_regiis\Aspnet_regiis.exe {your options here}

Remember, post build events are just simple DOS commands. You can even use () to scope several commands. The if only works on that 1 line unless you scope it. Standard command-line restrictions. Comment back here if you have problems setting it up, but post ur command line.

edited Aug 12, 2012 at 13:17

aknuds1

68.5k69 gold badges208 silver badges321 bronze badges

answered Aug 12, 2012 at 12:10

eduncan911

17.7k14 gold badges73 silver badges106 bronze badges

9 Comments

aknuds1 Over a year ago

Yeah, I know how to encrypt the connectionStrings section in Web.config, the problem would be that I need to transform it. How do I set up such a post build event in Visual Studio (2012)?

eduncan911 Over a year ago

Right-click on your project, and go to Properties. Look for Build events. There will be Pre and Post options.

eduncan911 Over a year ago

Something else, if you are attempting to use the built-in deployment options to deploy straight to Azure, you may run into issues. It may take building a custom package and deployment script that will build ur solution for the build event (msbuild), which will handle ur transformations, then encrypt, then use power shell to package up ur azure solution. The built in stuff is just simple, and doesn't have a lot of wiggle room. It is configurable, but lots of gotchas. Much easier to do it command line. May also look into any updates to msdeploy to see if now support Azure

aknuds1 Over a year ago

I get this error upon build: 'Aspnet_regiis.exe' is not recognized as an internal or external command, operable program or batch file.

eduncan911 Over a year ago

You'll have to setup paths to where they are installed. The msdn article should say where it is under c:\windows. Tip: use %windows% and %system32% command line parameters to help avoid hard coding exact paths

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related