[This article was contributed by the SQL Azure team.]

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 PKCS12ProtectedConfigurationProviderbinRelease 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.

   

   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.