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.
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?
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:
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.
makecert -r -pe -n "CN=azureconfig" -sky exchange "azureconfig.cer" -sv "azureconfig.pvk"
pvk2pfx -pvk "azureconfig.pvk" -spc "azureconfig.cer" -pfx "azureconfig.pfx" -pi password-entered-in-previous-step
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.
I was surprised to see you have to enter the password in cleartext on the command-line. Is there an alternate way to do this?
Can we use certificate to store symmetric key that I want to store in secure place?...(or how can we use ?) it is secure to store the encryption decryption keys in blob container which is private?
Swapnil: You can find out more information here: msdn.microsoft.com/.../ee758713.aspx
Ben: Not that I am aware of, however the certificate generated with pvk2pfx.exe isn't unqiue to this process -- so if you have another tool that you feel is more secure you can use it.
Following the process you've outlined above means that the developers cannot discover the SQL Azure password. However, the developers can deploy code that accesses SQL Azure - do you have any suggestions about how the code might be checked/verified to make sure that it isn't doing anything that it shouldn't in the SQL Azure database? For example, a developer could deploy some code that queried SQL Azure for all the stored customer details without knowing the SQL Azure password...
Hi Wayne, can we use this tutorial also for website or it's just for cloud services? thanks!
Selectively you can use it for your on-premise web service also, i.e. you don’t need to upload certificates and a few other things don't apply.
Any chance there could be 'next' and 'previous' links at the end of each article to link between them?