Not to toot my own horn too much but since security is a huge interest of mine I do give presentations on using the built in encryption capabilities in SQL Server for doing things like encrypting personally identifiable information and correctly implementing password hashing. I have posted all of my demo code (as well as some simple applications that access the data) to CodePlex here: http://sqlcrypto.codeplex.com .

I'd be happy to answer any questions about the code. As for key management the two greatest things about encryption in SQL Server is how the keys (mostly) travel along with the database (including in backups) and how easy it is to change encryption keys as SQL will automatically decrypt/reencrypt the data with the appropriate keys when you update them.