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

One of the challenges you face in running a highly secure SQL Azure environment is too keep your connection string to SQL Azure secure. If you are running Windows Azure you need to provide this connection string to your Windows Azure code, however you might not want to provide the production database password to the development staff creating the Windows Azure package. In this series of blogs posts I will take about how to secure your SQL Azure connection string in the Windows Azure environment.

Scoping the Scenario

Without any security measures your connection string appears in plain text, and includes your login and password to your production database. The goal is to allow the code running on Windows Azure be able to read your connection string, however the developers making the package for Windows Azure deployment should not be able to read it. In order to do that, Windows Azure needs know the “secret” to unlock the connection string; however the developers that are making the package should not. How is this accomplished when they have access to all the files in the package?

Using the Windows Azure Certificate Store

The way that we have a secret that only Windows Azure “knows” is to pre-deploy a certificate to the Windows Azure Certificate Store. This certificate contains the private key of a public/private key pair. Windows Azure then uses this private key to decrypt an encrypted connection string that is deployed in the configuration files of the Windows Azure package. The public key is passed out to anyone needing to encrypt a connection string. Users with the public key can use it to encrypt the connection string, but it doesn’t allow them to read the encrypted connection string.

In this technique there are three different types of users:

1. The private key holder (the Windows Azure administrator) a very secure user that generates the private/public key pair, stores the private key backup, and puts the private key in place on Windows Azure. Only he and Windows Azure can decode the connection string.
2. The SQL Administrator, he knows the connection string and he uses the public key (gotten from the Windows Azure Administrator) to encoded the connection string. The encoded bytes are giving to the web developers for the web.config.
3. Web developers that make the Windows Azure package, they know the public key (everyone has access to it); however they don’t know the connection string nor the private key so they can’t get the SQL password.

Creating a Certificate

The first thing the Windows Azure administrator (private key holder) needs to do is use their local machine to create a certificate. In order to do this they will need Visual Studio 2008 or 2010 installed. The technique that I usually use to create a private/public key pair is with a program called makecert.exe.

Here are the steps to create a self-signed certificate in .pfx format.

1. Open a Visual Studio command prompt (Run as administrator), you will find the command prompt in the start menu under Visual Studio tools.
2. Execute this command:

   makecert -r -pe -n “CN=azureconfig” -sky exchange “azureconfig.cer” -sv “azureconfig.pvk”

   

3. You will be prompted for a password to secure the private key three times. Enter a password of your choice.

   

4. This will generate an azureconfig.cer (the public key certificate) and an azureconfig.pvk (the private key file) file.
5. Then enter the following command to create the .pfx file (this format is used to import the private key to Windows Azure). After the –pi switch, enter the password you chose.

   pvk2pfx -pvk “azureconfig.pvk” -spc “azureconfig.cer” -pfx “azureconfig.pfx” -pi password-entered-in-previous-step

6. You can verify that the certificate has been created by looking in the current directory in the Visual Studio command prompt.

   

Summary

In part two of the blog series I will show how the Windows Azure Administrator imports the private key (.pfx file) into Windows Azure. Do you have questions, concerns, comments? Post them below and we will try to address them.