Windows Azure SQL Database Marketplace
[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:
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.
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:
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.
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.
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.
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: " +
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.
Do you have questions, concerns, comments? Post them below and we will try to address them.