SNIPPET: SQL Server 2005 Encryption

  • Article

SQL Server 2005 Beta 2 adds security to your data through selective encryption of sensitive information with functions that convert clear (character) text to ciphertext (varbinary). You can specify symmetric keys for better performance or asymmetric keys for greater security. For example to insert data encrypted using the certificate "'JanainaCert02'" (from Books Online)

 INSERT INTO [AdventureWorks].[ProtectedData04] values( N'data encrypted by certificate ''Shipping04''', EncryptByCert(Cert_ID('JanainaCert02'), @cleartext) );

To find out more check out https://www.ftponline.com/vsm/2004_en/magazine/features/rjennings/page4.aspx or download the abridged Books Online (link to the right) and search for the following:

  • CREATE MASTER KEY
  • CREATE SYMMETRIC KEY
  • CREATE CERTIFICATE
  • EncryptByCert, EncryptByKey, and EncryptByPassPhrase

Yipppeeeee - an end to clear text passwords being held in SQL Server!