This is a brief summary of SQL Server 2005 symmetric encryption, encryption keys, their hierarchy and usage. At the end of this blog you may find an example script on how to use symmetric key encryption.
Symmetric encryption
- Maximum number of characters which can be encrypted in one function is 7943.
- Symmetric encryption decrypts 1000 times faster than asymmetric encryption.
- Symmetric key is stored in the database and could be encrypted by database master key, by certificate and by password.
- If symmetric key was created using the same KEY_SOURCE, ALGORITHM and IDENTITY_VALUE it would be the same, even if created in different databases.
- When database backed up or detached symmetric key is kept in the database.
- When symmetric key is encrypted by password, Triple DES algorithm is used to encrypt symmetric key. Triple DES is weaker than AES. So a key for a stronger encryption is encrypted with a weaker encryption algorithm. It is advised to use certificate to encrypt symmetric key and to use AES 256 algorithm for symmetric encryption.
- AES encryption works only on Windows Server 2003 and later. (I have not tried it on Win XP SP 3, which came out in May 2008, may it has this encryption already.)
- Certificate can be encrypted/decrypted by database master key. If database master key is OPEN, then certificate decryption/encryption is applied automatically when you OPEN symmetric key.
- To OPEN database master key a user must have CONTROL permissions in the database.
Database Master Keys.
(This is where different articles say different things, so I was trying to give priority to the article with the later date and my experience.)
Here is an MSDN article about SQL Server 2005 encryption hierarchy.
And below is an overview of the encryption hierarchy.
Here is a similar MSDN article only for SQL Server 2008.
And an accompanying MSDN diagram for permissions hierarchy.
Here is original MSDN article.
When I was writing encryption script for our database I put these two diagrams on the wall in front of me, it helps :).
- Database Master Key (DMK) can be created with encryption by PASSWORD only.
- DMK is a symmetric key (according to may 2008 article and public/private key according to 2007 article, so I would go with 2008 J).
- DMK can be backed up.
- Other encryption can be added to DMK like so
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
- it is recommended to drop encryption by password and back up the key immediately before using it. Like so :
ALTER MASTER KEY DROP ENCRYPTION BY PASSWORD = 'whatever the original password is';
- the only problem with that is, IT DOES NOT WORK. MSDN says that a key can not be without any encryption, that is why the error message is thrown, but even when I added encryption by Service Master Key it did not help. Because SMK encryption is in master database only and thus when dropping password encryption it tries to drop it from the original database leaving key unencrypted, which is not allowed. So I have to add some other encryption to DMK before dropping password encryption.
- What it means is that the key can be accessed in the following 3 ways (assuming the database is restored from FULL database back up or original MDF file is attached.)
- When database is restored on the computer and SQL instance where the original key was created.
- When database is restored under the same SQL Server service account which was used when creating original key.
- When database is restored on a completely different computer and under very different service account, but a correct password for DMK is provided.
- When you ADD ENCRYPTION BY SERVICE MASTER KEY to DMK it creates a copy of DMK in master database and SQL Server Service account and SQL Server computer instance identity is used to encrypt the key. Thus there are 2 copies of DMK, one in the database itself and encrypted by password and another in master database and encrypted by SMK. When SMK encryption is added for DMK, there is no need to open DMK by password, in fact the whole command can be omitted, DMK gets unencrypted automatically. Which is what we want, because we don’t want to provide DMK password in our scripts and if it changes we would not want to update password in all T-SQL code (in a wrapper function in our case, but still we don’t want to make it visible even there).
- No matter how easy it is to decrypt the data I would still create a back up for database master key and put in a safe somewhere. Or Print and store securely all the parameters required to recreate symmetric key.
- The is_master_key_encrypted_by_server column of the sys.databases catalog view in master database indicates whether the database master key is encrypted by the service master key.
- Information about the database master key is visible in the sys.symmetric_keys catalog view.
- Please check that before dropping any other encryptions for DMK. If DMK is not encrypted by server (SMK) then it is better to know how to open DMK manually or add SMK encryption to it.
I looked up the syntax for ALTER MASTER KEY and the only encryptions which can be added are SMK and password:
ALTER MASTER KEY <alter_option>
<alter_option> ::=
<regenerate_option> <encryption_option>
<regenerate_option> ::=
[ FORCE ] REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'
<encryption_option> ::=
ADD ENCRYPTION BY [ SERVICE MASTER KEY PASSWORD = 'password' ]
DROP ENCRYPTION BY [ SERVICE MASTER KEY PASSWORD = 'password' ]
But I still don’t get why they have DROP password if it does not work? Some articles say that SMK always has password encryption in case database gets detached and needs to be restored. May be this syntax is for the future release. Who knows, it was in SQL Server 2005 books online. The REGENERATE option re-creates the database master key and all the keys it protects. The keys are first decrypted with the old master key, and then encrypted with the new master key. This resource-intensive operation should be scheduled during a period of low demand, unless the master key has been compromised. So for now we are going to have a password for DMK, unless something new comes up.
Below are the encryption script and examples on how to use it.
Encryption script.
Please test variables' sizes before going into production to make sure that data is not truncated.
-- =============================================
-- Author: Ivan A
-- Copyright 2009 by ISolvable - http://isolved.spaces.live.com/
-- Create date: 07/21/2008
-- Description: PLEASE DO NOT STORE THIS SCRIPT IN PRODUCTION DATABASE.
-- This is a script which creates database keys.
-- Passwords need to be changed in production.
-- =============================================
ALTER PROCEDURE [dbo].[CREATE_KEYS]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- this to prevent accidental executions of this stored procedure and to inform a user.
RAISERROR (N'This procedure cannot be executed. Please examine the procedure code first!',16,1)
/*<DO_NOT_EXECUTE,,*/--THIS STORPROC>
-- BACKUP KEYS.
-- DO NOT STORE THIS CODE IN PRODUCTION.
-- CODE WITH PASSWORDS TO BE STORED IN A SAFE(TBD).
-- BAKUP KEYS TO BE STORED IN A SAFE(TBD).
-- THIS CODE HAS ONLY MOCK UP PASSWORD.
-- CHANGE PASSWORDS BEFORE USE IN PRODUCTION.
-- DO NOT RECREATE KEYS IF SOME DATA IS ALREADY ENCRYPTED.
-- DECRYPT DATA FIRST. RECREATE KEYS. THEN ENCRYPT DATA WITH NEW KEYS.
-- BACKUP NEW KEYS.
-- THE CODE FOR THIS PROCEDURE WOULD BE COMMENTED OUT, TO PREVENT ACCIDENTAL EXECUTION.
-- PRESS CTRL-SHIFT-M and fill in the appropriate values.
-- THEN EXECUTE CODE SEPARATELY FROM STORED PROCEDURE STATEMENTS.
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- =============================================
/*
----------------------------------------------------------------------------------------------
-- this creates a Database Master Key (DMK) in the original database
-- and encrypts MASTER KEY by password.
-- MUST MEET WINDOWS PASSWORD POLICY.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<master_key_password,,password>'
-- this copies DMK into master database
-- and encrypts DMK using SQL Server Service Master Key.
-- After this statement is executed DMK would be opened/decrypted automatically.
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
-- this exports DMK into a file (ON THE SERVER NOT ON LOCAL COMPUTER)
-- and encrypts it using a password.
-- store this file in a safe and secure location.
BACKUP MASTER KEY TO FILE = '<Back_up_file_path_on_server,,c:\temp>'
ENCRYPTION BY PASSWORD = '<master_key_password,,password>'
-- this creates certificates which protects database encryption keys (symmetric or asymmetric).
CREATE CERTIFICATE [<Cert_Name,,CTrack_Cert>] WITH SUBJECT = '<Certificate_Subject,,Key Protection>'
-- this creates symmetric key and ecnrypts it with a certificate.
CREATE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>] WITH
ALGORITHM = <Symmetric_Algorithm,,AES_256>
ENCRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>];
----------------------------------------------------------------------------------------------
*/
/*
----------------------------------------------------------------------------------------------
-- Stored Procedures to be created.
----------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This procedure opens current symmetric key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Sys_Open_Key]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- This is to be called subsequently with other stored procedures
-- within the same connection session.
-- As soon as connection session is closed the key is also closed.
-- This opens a key defined in usp_CREATE_KEYS script (not in production)
OPEN SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
DECRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This procedure closes current symmetric key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Sys_Close_Key]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- This is to be called subsequently with other stored procedures
-- within the same connection session.
-- As soon as connection session is closed the key is also closed.
-- This opens a key defined in usp_CREATE_KEYS script (not in production)
CLOSE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
END
GO
----------------------------------------------------------------------------------------------
*/
/*
----------------------------------------------------------------------------------------------
-- Functions to be created.
----------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This function encrypts a string using database symmetric key
-- =============================================
CREATE FUNCTION [dbo].[udf_Encrypt]
(
-- Add the parameters for the function here
@text varchar(7944)
)
RETURNS varchar(7988)
AS
BEGIN
-- if provided text length is greater than what can be encrypted than
-- an error message is thrown to prevent truncation.
if len(@text) > 7943
begin
DECLARE @temp as int
RETURN (1 + 'Argument text should be less than or equal to 7943')
end
-- Declare the return variable here
DECLARE @Result varchar(7988)
-- opening and closing key is resource intensive operation
-- if encryption needs to be applied multiple times please
-- use usp_Sys_Open_Key and usp_Sys_Close_Key stored procedures
-- and call encryptbykey function directly from your code.
-- usp_Sys_Open_Key procedure does the following.
--OPEN SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
--DECRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>];
-- use encrypt function
SELECT @Result = encryptbykey(key_guid('<Symmetric_Key_Name,,CTrack_Sym_Key>'),@text)
-- close symmetric key using usp_Sys_Open_Key procedure,
-- which does the following.
-- CLOSE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
-- Return the result of the function
RETURN @Result
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This function decrypts a string using database symmetric key
-- =============================================
CREATE FUNCTION udf_Decrypt
(
-- Add the parameters for the function here
@text varchar(7988)
)
RETURNS varchar(7943)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(7943)
-- opening and closing key is resource intensive operation
-- if encryption needs to be applied multiple times please
-- use usp_Sys_Open_Key and usp_Sys_Close_Key stored procedures
-- and call encryptbykey function directly from your code.
-- usp_Sys_Open_Key procedure does the following.
--OPEN SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
--DECRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>];
-- use encrypt function
SELECT @Result = decryptbykey(@text)
-- close symmetric key using usp_Sys_Open_Key procedure,
-- which does the following.
-- CLOSE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
-- Return the result of the function
RETURN @Result
END
GO
----------------------------------------------------------------------------------------------
*/
/*
----------------------------------------------------------------------------------------------
-- Other ways to use
----------------------------------------------------------------------------------------------
-- you may use the following command to open the key,
-- however the wrapper procedure would be provided, to avoid
-- changing key names in multiple code files.
OPEN SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
DECRYPTION BY CERTIFICATE [<Cert_Name,,CTrack_Cert>];
-- you may use the following command to close the key.
CLOSE SYMMETRIC KEY [<Symmetric_Key_Name,,CTrack_Sym_Key>]
----------------------------------------------------------------------------------------------
*/
/*
----------------------------------------------------------------------------------------------
-- Procedures to be called from .NET
----------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This procedure is for use from .NET,
-- it encrypts plain text using database symmetric key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Encrypt_BySymKey]
-- Add the parameters for the stored procedure here
@plaintext as varchar(1000)
AS
BEGIN
-- the size of the encrypted string
-- should be at least 60 bytes larger than the original plain text.
DECLARE @encrypted as varchar(1060)
-- Open symmetric key
Exec dbo.usp_Sys_Open_Key
-- encrypt plaint text
SET @encrypted = dbo.udf_Encrypt(@plaintext)
-- close symmetric key
Exec dbo.usp_Sys_Close_Key
select @encrypted
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: This procedure is for use from .NET,
-- it decrypts into plain text using database symmetric key.
-- =============================================
CREATE PROCEDURE [dbo].[usp_Decrypt_BySymKey]
-- Add the parameters for the stored procedure here
@encrypted as varchar(1060)
AS
BEGIN
-- the size of the encrypted string
-- should be at least 60 bytes larger than the original plain text.
DECLARE @decrypted as varchar(1060)
-- Open symmetric key
Exec dbo.usp_Sys_Open_Key
-- encrypt plaint text
SET @decrypted = dbo.udf_Decrypt(@encrypted)
-- close symmetric key
Exec dbo.usp_Sys_Close_Key
select @decrypted
END
GO
----------------------------------------------------------------------------------------------
*/
END
Summary of instructions on how to use procedures and functions created by the above script. Detailed instructions are in the script comments above.
The script creates 2 stored procedures and 2 functions.
- usp_Sys_Open_Key
- usp_Sys_Close_Key
- udf_Encrypt
- udf_Decrypt
When setting up a database, you would need:
- to open this script.
- Press CTRL-SHIFT-M
- Fill in the required parameters.
- Make sure data is NOT encrypted before recreating the keys.
- Run code to create stored procedures and functions.
Here is an example on how to use these things:
DECLARE @original as varchar(100)
-- make sure variable for encrypted text is at least 64 bytes larger, or better double it.
, @encrypted as varchar(200)
, @decrypted as varchar(100)
-- creating original text
SET @original = 'some text to encrypt'
SELECT @original, len(@original)
-- open symmetric key
Exec usp_Sys_Open_Key
-- Encrypt text, make sure you do not trim.
-- Do not cast without specifying a size.
SET @encrypted = dbo.usp_Encrypt(@original)
SELECT len(cast(@encrypted as varbinary(68))), len(@encrypted)
-- Do casting like so. This would be correctly decrypted.
SET @decrypted = dbo.usp_Decrypt(cast(cast(@encrypted as varbinary(68)) as varchar(68)))
SELECT @decrypted
-- always close key or close connection session as soon as key is not needed.
Exec usp_Sys_Close_Key
-- the return value of the decrypt function is 7943, you may do casting, make sure to specify the size
-- like in the following example.
drop table temp2
Exec usp_Sys_Open_Key
select *
, cast(dbo.usp_Decrypt(accnum_enc) as varchar(20)) as accnum_dec
into temp2
from temp1
Exec usp_Sys_Close_Key
Again this problem turned out to be compatible with ISolvable interface.
Ok, I think this is enough for today. It is 11:00 PM on January 15, 2009, and tomorrow I need to wake up at 4:40 am to go to work at 6:am. Need to get some sleep so I can actually work tomorrow :).
P.S. The post could have some errors, since I wrote most of it in July of 2008, please write me if you find some problems or have answers to my questions.
