Lets talk SQL Server 2005. So, one of the new features is the inclusion of Certificate management. This, in turn, allows you to selectively encrypt/decrypt within SQL - consolidating CPU use and centralizing your performance into one point.
You'll need to create a Master Key to use certs/keys. Something to note is that if you detatch or restore a DB with a master key, you'll need to open the master key *t least once* to register the key with SQL Server.
Once you've created a master key, you can create any number of certificates and from them, a number of symmetric or asymmetric keys. When you've created certs/keys you'll notice them under the Security branch in SQL Managment Studio. You'll most likely want to grant control to a user or role.
to actually use a key for encryption, you'll want to store the encrypted data in a varbinary column. Sizing is the key.. make sure that you have enough space to store whatever you encrypt.
You can easily store your encryption/decryption logic in a stored procedure - which can be called by multiple procedures within your data model. Be careful when using this functionality as it is CPU intensive.
More on this soon..
Posted at 02:02 am by ausrob2003