Master Key Encryption in SQL Server

I have been using Service Broker recently and found it exciting and useful. It seems like a cool feather in SQL Server. I use the SQL Dependency class in my .Net Windows Service to listen for the Service Broker Notification. When there is an Insert to the tables in my database the Service Broker sends me a Notification which alerts my Windows Service Application to Query data from the table and do the processing. It works very well.

As you use Service Broker you might get a Warning message in your Event Viewer similar to this one.
Service Broker needs to access the master key in the database ‘xxx’. Error code:25. The master key has to exist and the service master key encryption is required.
 
Even though it’s not an error, it’s still a warning and it’s better to take care of the warning. To prevent the warning, I had to create Master Key Encryption in my Master Database.
The Information on Keys can be found in the sys.symmetric_keys table. The following query should come up with the Master Keys Created.
SELECT * FROM sys.symmetric_keys
For more info on sys.symmetric_keys table sys.symmetric_keys (Transact-SQL)
Here is an Article for further reading Managing SQL Server 2005 Master Keys for Encryption
You create the Master Key Encryption using the following statement.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourPassword'
Or if you want to check if the Master Key already exist and then create the Key, you can do using the following Script.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys
      WHERE [name] LIKE '%DatabaseMasterKey%')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourPassword'
 
To Drop the Master Key using the following:
Use DatabaseName
Drop Master Key
For further Reading on :
SQL Server uses different types of Encryption to protect your data from Hackers and Scary people out there.
SQL Server provides the following mechanisms for encryption:
  • Transact-SQL functions
  • Asymmetric keys
  • Symmetric keys
  • Certificates
  • Transparent Data Encryption
You can read about SQL Server Encryption Hierarchy here. SQL Server Encryption Hierarchy
There are two types of Keys used by SQL Server to Encrypt and Decrypt your Database
Asymmetric Keys
An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other. Asymmetric encryption and decryption are relatively resource-intensive, but they provide a higher level of security than symmetric encryption. An asymmetric key can be used to encrypt a symmetric key for storage in a database.
Symmetric Keys
A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database.
Here is an Article on using Symmetric Encryption in SQL Server 2005 Using Symmetric Encryption in SQL Server 2005
For further reading here is a Tutorial on  SQL Server Encryption and Symmetric Key Encryption
For more Resources on SQL Server Security:   SQL 2005 Security     SQL 2008 Security
Reading Resources:
6.       Backing up Master Key
9.       SQL 2005 Security    
Tags: