Microsoft SQL Server 2005 Database Encryption Step-by-Step
This is a how-to guide which will aims to help ms sql server developers and ms sql server administrators to implement Microsoft SQL Server 2005 Encryption methodologies.
This tutorial is a step-by-step guide for encryption and decryption in MS SQL Server 2005 and later (MS SQL2008 aka Katmai)
This tutorial is a step-by-step guide for encryption and decryption in MS SQL Server 2005 and later (MS SQL2008 aka Katmai)
Creating Master Key
Before using encryption algoritms in SQL Server 2005 and SQL Server 2008, a master key should be created in the database where encryption is going to be used.
Note that master key is created seperately for each database on a SQL Server database instance
Before creating a master key, sql developers or sql server database administers that has the required permissions can run the below t-sql select query to see if a master key is created before.
Note that master key is created seperately for each database on a SQL Server database instance
Before creating a master key, sql developers or sql server database administers that has the required permissions can run the below t-sql select query to see if a master key is created before.
SELECT * FROM sys.symmetric_keys
If there has been created a master key, you will see a result that is similar to below if you are running the t-sql select from sys.symmetric_keys view in MS SQL Server 2005,
Note that in SQL2008 (CTP6 or February CTP) sys.symmetric_keys view has additional columns:
name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid
key_thumbprint
provider_type
cryptographic_provider_guid
cryptographic_provider_algid
name
principal_id
symmetric_key_id
key_length
key_algorithm
algorithm_desc
create_date
modify_date
key_guid
key_thumbprint
provider_type
cryptographic_provider_guid
cryptographic_provider_algid
It is important that for a database in MS SQL Server, there can be only one master key in other words a single master key can be created on a database. A second master key can not be created in a sql server database.
You can use the below t-sql script code in order to create a master key in the sql database.
You can use the below t-sql script code in order to create a master key in the sql database.
/*************** CREATE MASTER KEY *********************************/
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'
GO
IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$EncryptionPassword12'
GO
It is important that you keep the encryption password in a safe place or keep backups of your sql server database master key.
I'm going to deal with master key backup later in this tutorial.
You can drop or remove an existing master key using the DROP MASTER KEY t-sql command.
If you try to drop a master key which has been used for creating other database objects like certificates the DROP MASTER KEY sql command will fail.
I'm going to deal with master key backup later in this tutorial.
You can drop or remove an existing master key using the DROP MASTER KEY t-sql command.
If you try to drop a master key which has been used for creating other database objects like certificates the DROP MASTER KEY sql command will fail.
If you run the DROP MASTER KEY sql server statement after a certificate is defined which we will see in the next step, the following error message is going to be informing the sql server programmer about the dependent certificate.
Msg 15580, Level 16, State 1, Line 2
Cannot drop master key because certificate 'PasswordFieldCertificate' is encrypted by it.
Cannot drop master key because certificate 'PasswordFieldCertificate' is encrypted by it.
