Windows Azure SQL Database Marketplace
[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:
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.