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:
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.
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).
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.
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:
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.
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:
connectionString="Initial Catalog=aspnetdb;data source=.;uid=user;pwd=secretpassword" providerName="System.Data.SqlClient"/>
<add name="CustomProvider" thumbprint="4badf1eea9666d95c1c046fde32008c5e3bf20d9"
type="Pkcs12ProtectedConfigurationProvider.Pkcs12ProtectedConfigurationProvider, PKCS12ProtectedConfigurationProvider, Version=126.96.36.199, Culture=neutral, PublicKeyToken=34da007ac91f901d"/>
aspnet_regiis -pef "connectionStrings" "." -prov "CustomProvider"
If the encryption is successful, you will see the following output:
Here is what is happening:
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#aes192-cbc" />
<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
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.
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
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?
the link to the second post is not working