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

This is the third 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 this blog post I will show how the SQL Server Administrator uses the public key to encrypt the connection string.

In this technique, there is a role of the SQL Azure administrator; he has access to the public key, the SQL Azure portal, and the SQL Azure administrator login and password. His job it to:

• Encode the connection string with the public key.
• Secure the SQL Azure Portal administrator login/password
• Restrict the SQL Azure login (which is not the administrator login) in the connection string.

Because the SQL Azure administrator has access to the public key, he can encode the connection string and knows the password to the production database. He has more access to SQL Azure than any other user in the example scenario.

Restricting the User

Before the connection string is encoded, the SQL Azure administrator needs to restrict the SQL Azure account in the connection string to reduce the attack surface and make the production database more secure. Don’t use the SQL Azure database administrative user account in your connecting string. Instead, as part of security best practices, create another user that just has the permissions that the web role needs. If the web site doesn’t need to create tables, don’t allow the web user to create a table. If the web site is just reading data, make the user read-only. Restricting the web user will reduce the damage to your database if the connection string does become compromised. Find out how to create a user in SQL Azure by reading this blog post. Restricting the SQL Azure user also restricts the Windows Azure administrator (this role is discussed in this blog post).

Importing the Public Key

The first thing that the SQL Azure administrator has to do is take the public key gotten from the Windows Azure administrator and import it into their local certificate store on their local box. The aspnet_regiis.exe tool which performs the encoding on the web.config uses the local certificate store.

1. Click Start, type mmc in the Search programs and files box, and then press ENTER.
2. On the File menu, click Add/Remove Snap-in.
3. Under Available snap-ins, double-click Certificates.
4. Select Computer account, and then click Next.
5. Click Local computer, and then click Finish.
6. In the Personal store, right click, under “All Tasks”, click Import. Browse to the .cer file (public key gotten from the Windows Azure administrator) and import the certificate.
7. Once you have the certificate import, right click on it and choose Open, this will bring up the Certificate dialog, choose the details tab, scroll to the bottom and copy the thumbprint property. We will need this later

Download and Compiling the Provider

This could be done ahead of time by the developers and the two installer files (setup.exe and the installer.msi) could be emailed to the SQL Server Administrator, however if they haven’t done it, the SQL Administrator will need to download and compile the provider. You will need Visual Studio 2008 or Visual Studio 2010 on your box. Follow these steps:

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 Installer/bin/release directory there should be a setup.exe.
6. Execute this setup.exe and install the provider.

The installer will put Pkcs12CertProtectedConfiguratoinProvider.dll assembly file into the Global Assembly Cache so that the aspnet_regiis.exe can find it when you go to encrypt the web.config.

Encrypting Web.Config

Now that you have the provider assembly in the Global Assembly Cache and the public certificate installed on your box, you are ready to encrypt the connection string section of the web.config file, here is how:

1. Get the web.config file from the developers
2. If you are using source control, check out the web.config file (you will be modifying it).
3. Add a connectionString similar to the one below in the Web.config file. This connection string should be similar to the one that comes from the SQL Azure Portal. However, it should contain the restricted user name and password.

   <connectionStrings>    <add name="SQLAzureConn"          connectionString="Initial Catalog=aspnetdb;data source=.;uid=user;pwd=secretpassword" providerName="System.Data.SqlClient"/>  connectionStrings>

4. Add and configure the custom protected configuration provider. To do this, add the following <configProtectedData> section to the Web.config file in the web role. Note that the thumbprint should be set to the thumbprint value from the Certificate dialog in the Microsoft Management Console, with all the spaces removed.

   <configProtectedData>      <providers>        <add name="CustomProvider" thumbprint="4badf1eea9666d95c1c046fde32008c5e3bf20d9"             type="Pkcs12ProtectedConfigurationProvider.Pkcs12ProtectedConfigurationProvider, PKCS12ProtectedConfigurationProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=34da007ac91f901d"/>      providers>    configProtectedData>

5. Run the following command from a Visual Studio command prompt to encrypt the connectionStrings section using the custom provider. Set the current directory in the Visual Studio command prompt to the folder containing the Web.config file.

   aspnet_regiis -pef “connectionStrings” “.” -prov “CustomProvider”

   If the encryption is successful, you will see the following output:

   

   Here is what is happening:

   • aspnet_regiis.exe finds the web.config in the current directory and loads it.
   • Using the –prov switch it finds the provider section in configProtectedData and figures out the full name of the assembly containing the provider.
   • aspnet_regiis.exe loads the assembly from the Global Assembly Cache and calls the Initialize method in the assembly which checks to make sure that there is a thumbprint property in the web.config file.
   • aspnet_regiis.exe then calls the Encrypt method of the Pkcs12CertProtectedConfiguratoinProvider.dll assembly which loads the public certificate from the Certificate store using the thumbprint as a primary key to the store. Using the –pef switch from the command line it loads the connection string section in the web.config and encrypts it.
   • Once the connection string section is encrypted, it is written back to the web.config like so:

       <connectionStrings configProtectionProvider="CustomProvider">      <EncryptedData Type="https://www.w3.org/2001/04/xmlenc#Element"        xmlns="https://www.w3.org/2001/04/xmlenc#">        <EncryptionMethod Algorithm="https://www.w3.org/2001/04/xmlenc#aes192-cbc" />        <KeyInfo xmlns="https://www.w3.org/2000/09/xmldsig#">          <EncryptedKey xmlns="https://www.w3.org/2001/04/xmlenc#">            <EncryptionMethod Algorithm="https://www.w3.org/2001/04/xmlenc#rsa-1_5" />            <KeyInfo xmlns="https://www.w3.org/2000/09/xmldsig#">              <KeyName>rsaKeyKeyName>            KeyInfo>            <CipherData>              <CipherValue>aA4kyC0pNY8VFnPtLcC...=CipherValue>            CipherData>          EncryptedKey>        KeyInfo>        <CipherData>          <CipherValue>6Fg9VWR5/...CipherValue>        CipherData>      EncryptedData>    connectionStrings>

6. Check the web.config file back into source control, or give it back to the developers.

Summary

In the next 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. Do you have questions, concerns, comments? Post them below and we will try to address them.