Best patterns for encrypting database columns/tables in SQL Azure

Short answer: as of April 20, 2012 column encryption is not supported in SQL Azure, see: http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx and search for ENCRYPT. You will see that all of the encryption functions are listed as unsupported.

If you need to encrypt your data at rest and have to support SQL Azure then you are going to have to do your encryption at the application level. This is more difficult to get right as lots of the good encryption practices (using nonces as initialization vectors for each cell, key management for tracking which keys encrypt which columns, integrity verification, layered protection of encryption keys with role based permissions) you will have to implement yourself. I strongly recommend getting a security professional to work with you on the encryption as it is challenging to get right and if you miss something then you have a false sense of security that could really burn you later.

You could look at http://securentity.codeplex.com, which claims to be a solution for managing encrypted data and supports SQL Azure. I have not personally used it, nor can I vouch for it's correctness, but it is a potential option.

Other information:

In SQL Server column level encryption is easy and very secure (even with a view) because you can use roles to manage who can access the encryption keys. Even if a user can select from a view, if they are not granted access to the key that encrypts the underlying data then they will get back NULLs for those encrypted columns. I have a set of sample code for SQL Server 2005 and higher that demonstrates the encryption capabilities of SQL Server here: http://sqlcrypto.codeplex.com

Connect to master database and do the following:

CREATE LOGIN YourNewUser WITH PASSWORD = '<strong password here>';

Connect directly to database that the new user should own:

CREATE USER YourNewUser FOR LOGIN YourNewUser;

EXEC sp_addrolemember 'db_owner', 'YourNewUser';