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

Securing Your Connection String in Windows Azure: Part 3

Securing Your Connection String in Windows Azure: Part 3

Rate This
  • Comments 13

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:

    image

    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="http://www.w3.org/2001/04/xmlenc#Element"
            xmlns="http://www.w3.org/2001/04/xmlenc#">
            <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#aes192-cbc" />
            <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
              <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
                <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
                <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
                  <KeyName>rsaKey</KeyName>
                </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.

  • I am adding the configProtectedData section in my web.config. thumbprint attribute is not coming up in the intellisense.  When I am building the solution. Build is successful.

    On running the command in visual studio command prompt - aspnet_regiis -pef "connectionStrings" "." -prov "CustomProvider"

    It is giving me following message :

    Encrypting configuration section...

    The protection provider 'CustomProvider' was not found.

    Failed!

    Unable to understand why it is unable to find the CustomProvider even though it is added ?

  • How to use the same in SSIS package?

  • I've downloaded the solution and built the project, but the bin\Release folder does not have an Instal.exe in it, but just the Provider DLL

  • Ok, followup on my earlier post.

    Went back into the solution, rebuilt just the Installer project

    The optout window renders the path of the Installer.msi file which looks something like this on Windows7

    C:\YourProjectPath\PKCS12ProtectedConfigurationProvider\Installer\Release

    There will be 2 files in there, an Installer.msi and a Setup.exe

  • Please fix the link to Part 2. And while you're at it, please put links to the other two parts in each of the three parts, to make navigation easier.

  • How about worker roles?  how do we secure connection string in app.config?

  • Hi man!

    the link to the second post is not working

  • Hello, I am feeling like such a loser having followed the instructions here and failed both times. VS2012, .Net 4.5 runtime. Same error both times when running locally for test purposes:

    Parser Error Message: Failed to decrypt using provider 'CustomProvider'. Error message from the provider: Value cannot be null.

    Parameter name: keyObject

    If it worked, the next step would be to put the config information into web.release.debug and have a local db for debug. Company policy requires our sites to have this. Any ideas?

  • gacutil /l shows PKCS12ProtectedConfigurationProvider, Version=1.0.0.0, Culture=neutral, Public KeyToken=34da007ac91f901d, processorArchitecture=MSIL

    Thumbprint matches expected value. Procmon shows a failure accessing the seeming unrelated registry key just before failure: HKLM\Software\Wow6432Node\Microsoft\ASP.NET\4.0.30319.0\CompilationMutexName.

  • Hmm,

    > aspnet_regiis-pef connectionStrings . -prov CustomProvider

    Microsoft (R) ASP.NET RegIIS version 4.0.30319.17929

    Administration utility to install and uninstall ASP.NET on the local machine.

    Copyright (C) Microsoft Corporation.  All rights reserved.

    Encrypting configuration section...

    Succeeded!

    > aspnet_regiis

    -pdf connectionStrings .

    Microsoft (R) ASP.NET RegIIS version 4.0.30319.17929

    Administration utility to install and uninstall ASP.NET on the local machine.

    Copyright (C) Microsoft Corporation.  All rights reserved.

    Decrypting configuration section...

    Failed to decrypt using provider 'CustomProvider'. Error message from the provider: Value cannot be null.

    Parameter name: keyObject (c:\Users\pohms\Src\Secret\tfs\Sources\Secret\web.config line 47)

  • why are all these examples for web.config. In an azure worker role that needs to connect to SqlAzure instance, I don't even have web.config. Isn't cscfg the right spot to store settings? what if I want to change the connection string without redeploying application? Do you have link to sample for how to store encrypted connection string to cscfg file?

  • I have a question? If connectionstrings =”Data Source=MyServer;Databas… was encrypted then connectionStringName in membership,rolemanager in Microsoft.Practices.EnterpriseLibrary how to read?

    I’m looking encryption connectionstrings in web.config. My company is not using tool aspnet_regiis.exe.

    Thanks

    Sorry I’m not good English.

  • I have a question? If connectionstrings =”Data Source=MyServer;Databas… was encrypted then connectionStringName in membership,rolemanager in Microsoft.Practices.EnterpriseLibrary how to read?

    I’m looking encryption connectionstrings in web.config. My comppany is not using tool aspnet_regiis.exe.

    Thanks

    Sorry I’m not good English.

Page 1 of 1 (13 items)
Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post