Melville Thomson's Blog

Data, SQL and Windows Technologies

SQL Server 2005 Encryption

SQL Server 2005 Encryption

  • Comments 1
  • Likes

Steve asked me about encryption in SQL Server 2005.  I have found a few areas of information that are useful.  In BOL there is an article about the Encryption Hierarchy  this explains quite a lot about how it is organised.  I quite like it because it has a diagram which is easy to understand.

SQL Server encrypts data with a hierarchical encryption key management infrastructure.  Each layer encrypts the layer below it. 

The first is the service master key
 This secures:

  • linked server passwords
  • Connection strings
  •  Account credentials
  • All database master keys

There are optional database master key's
 This is used for:

  • Certificates
  • Asymmetric Keys
  • Symmetric Keys

There are a number of Cryptographic Functions   in Transact-SQL these support encryption, decryption, digital signing and the validation of digital signatures.

To see what is going on there are a number of Security Catalog Views  which show you cryptographic information these are useful. 

One tip I would recommend is to backup the service master key and secure it offsite for safety you can use ‘BACKUP MASTER KEY’ and ‘RESTORE MASTER KEY’ Transact-SQL statements.

BACKUP SERVICE MASTER KEY TO FILE = 'c:\temp_backups\keys\service_master_key' ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';

RESTORE SERVICE MASTER KEY FROM FILE = 'c:\temp_backups\keys\service_master_key' DECRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';

But very important
When the service master key is restored, SQL Server decrypts all the keys and secrets that have been encrypted with the current service master key, and then encrypts them with the service master key loaded from the backup file.
If any one of the decryptions fails, the restore will fail. You can use the FORCE option to ignore errors, but this option will cause the loss of any data that cannot be decrypted.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment