Securing Your Connection String in Windows Azure: Part 4 - SQL Azure Team Blog - Site Home - MSDN Blogs

Securing Your Connection String in Windows Azure: Part 4

Securing Your Connection String in Windows Azure: Part 4

Rate This
  • Comments 10

This is the fourth part in a multi-part blog series about securing your connection string in Windows Azure. In the first blog post (found here) a technique was discussed for creating a public/private key pair, using the Windows Azure Certificate Store to store and decrypt the secure connection string. In the second blog post (found here) I showed how the Windows Azure administrator imported the private key to Windows Azure. In the third blog post I will show how the SQL Server Administrator uses the public key to encrypt the connection string. In this blog post I will discuss the role of the developer and the code they need to add to the web role project to get the encrypted connection string.

In this technique, there is a role of web developer; he has access to the public key (however he doesn’t need to use it), and the encrypted web.config file given to him by the SQL Server Administrator. His job is to:

  • Reference the Thumbprint of the private key in the web.config.
  • Add the provider assembly (PKCS12ProtectedConfigurationProvider.dl) to the project.

The developer’s role is the most restricted role in this technique. He doesn’t have access to the private key, nor the connection string.

Download and Compiling the Provider

The provider needs to be compiled so it can be referenced by the web role project. You will need Visual Studio 2008 or Visual Studio 2010 on your box. We discussed this in Part 3, for the SQL Azure Administrator, however the developer needs a compiled instant of this also. In some case the developer will compile it for the SQL Azure Administrator, in some case the code will be checked in and compiled with every build – follow your companies guide lines. The step to compile it are:

  1. From the MSDN Code Gallery download the .zip with the source code.
  2. Save everything in the .zip file to your local machine.
  3. Find the PKCS12ProtectedConfigurationProvider.sln file and open it as a solution with Visual Studio.
  4. From the Tool Menu Choose Build | Build Solution.
  5. In the PKCS12ProtectedConfigurationProvider\bin\Release directory there should be a PKCS12ProtectedConfigurationProvider.dll
  6. Copy PKCS12ProtectedConfigurationProvider.dll where your other third party assemblies are located in the web role, if you are using source control, check it in. This way the other developers will not need to recompile it.

Updating the Project to Use the Provider

  1. Add an assembly reference for this custom protected configuration provider to the Web role project, this is the PKCS12ProtectedConfigurationProvider.dll you built above. To do so, right click on References in the Web Role project and click on Add Reference. Then browse to the redistributables directory for the custom provider and select “Pkcs12CertProtectedConfiguratoinProvider.dll”. Right click on the added reference and click on Properties. Set the Copy Local property of the reference to True; most likely it will already be set to True. This is required so that the assembly is deployed to Windows Azure.
  2. From the Windows Azure Administrator get the thumbprint of the private key from the Windows Azure Portal.

    clip_image002

    Replace the thumbprint in the web.config with the thumbprint of the from the Windows Azure portal, this is the thumbprint of the private key and is needed by the provider to decrypt the connection string.

  3. Check in the web.config file and the project file with the newly added reference.

Now you are ready to create your deployment package and deploy to Windows Azure. The web.config file with the encrypted connection string along with the PKCS12ProtectedConfigurationProvider.dll assembly will be deployed to Windows Azure, working with the private key in the Windows Certificate store the provider, referenced by the thumbprint, the provider will be able to decrypt the connection string for the code.

A Developer’s Life

Have you ever noticed that when things become more secure the developer’s job gets harder? One thing about this technique is that the production web.config file will not work on the developer’s box running the development fabric. The reason is that the private key is not on the developer box, and that private key is needed to decrypt the web.config. The solution is not to install the private key on the developer’s box, this would compromise the connection string. The solution is to have the developers running a different web.config, one that contains connection strings to development SQL Azure databases. This version of the connection string doesn’t need to be encrypted.

Code Review

Any code running on the production Windows Azure servers that has access to the web.config and the Windows Azure Certificate store has access to the SQL Azure connection string. For example this code:

Response.Write("Clear text connection string is: " +
                 System.Web.Configuration.WebConfigurationManager.ConnectionStrings
                            ["SQLAzureConn"].ConnectionString);

Running on the production server would print out the connection string. This means that all code running on the Windows Azure server needs to have a security code review to make sure a rogue developer doesn’t compromise the integrity of the security work that we have done by encrypting the connection string. It also means that anyone that can deploy to the production Windows Azure server has the ability to figure out the connection string.

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.

  • I have completed all the steps, but then receive this message when trying to use the connection string:

    Failed to decrypt using provider 'CustomProvider'. Error message from the provider: No certificate was found for thumbprint 350E6DF8050CEAA04729FC45BA904E6D204B1646 (E:\approot\web.config line 19)  

    I have verified the thumbprint in azure.  I have the certificate uploaded to the account and also within the service.  Any idea's? Thanks!

  • Jaleel,

    1) Are you sure that you uploaded to the right Windows Azure account?  How many do you have?

    2) Is the thumbprint in the error message missing any characters?  i.e. a copy and paste error.

    3) Did you upload the certificate with the private key?

  • Hi,

    I experienced the same issue as Jaeel, and the issue was that I hadn't made the certificate available to the Cloud Website via the service definitions... I did this by adding the follwoing to the servicedefinition.csdef inside the webrole section:

         <Certificates>

             <Certificate name="AzureConfig" storeLocation="LocalMachine" storeName="My" />

         </Certificates>

    and the corresponding entry to the Role section of the serviceConfiguration.cscfg

         <Certificates>

             <Certificate name="AzureConfig" thumbprint="834BD072379A27B1468526C75194CF7D5387110C" thumbprintAlgorithm="sha1"/>

         </Certificates>

    This then gave the application visibility of the certificate and everything worked.

    Wayne - many thanks for taking the time to write such a thorough article, it was very helpful!

    Regards,

    Nick

  • Thanks Nick, that fixed the problem.  Wayne, thanks for the great content!

  • Hi All,

    Is there a way to configure certificate using the code, inside Global.asax.cs (i.e. not, using the web.config)?

  • CT:  Because the web.config loads (and decrypts) before the Global.asax.cs is called there is not a way I can think of do do that.  However, you might be able to solve your issue by downloading the source code, modifying it and creating your own custom provider.

  • Thank you Wayne for the detailed steps.

    I followed all the steps as you describe. However, one thing I have noticed is that the thumbprint values returned by the Azure and by the local MMC snap-in are the same. Should not they be different?

  • We have done the steps and is working fine if it installed in GAC.  if refer the dll into the project and is not installed into GAC, then how do we encrypt the connection string?

  • I am getting the error "The protection provider 'Customprovider' was not found" when trying to encrypt.Any thoughts on how to resolve this? Thank you

  • Hello,

    i managed to use encryption of app.config for a web role project.

    is this also possible for a web site running on azure? i am digging the web for days now but it seems this is a feature for web roles only.

Page 1 of 1 (10 items)
Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post