Security in SQL Server 2005 – Encryption

In July 2004, encryption built-ins and key management had just been introduced in SQL Server 2005. Now its old news. They'll be a good-sized section about it in the book revision. You're probably heard of:

EncryptBy (Key/Certificate/PassPhrase) and
DecryptBy (Key/Certificate/PassPhrase)

But there's also the less well-known:

SignByAsymKey/SignByCert
VerifySignedByAsymKey/VerifySignedByCert
HashBytes

and also the aptly-named: DecryptByKeyAutoCert and DecryptByKeyAutoAsymKey

The "auto" versions are for convenience. When you're doing encryption or decryption, you have to remember to open the keys that have been used to encrypt the data you want to read. And to close them when you're done. The "Auto" versions do this for you, when you're decrypting, so you can provide a decrypting VIEW, for example. I'll have more to say about this soon.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.