Encryption hierarchy
SQL Server uses an encryption hierarchy to secure data. This structure allows all levels of the
hierarchy to be kept secure and for SQL Server to implement automatic key
management to decrypt keys and certificates as they are required.
I've not tested it myself, but I think that the following will apply to SQL Server 2008 too.
Setting up an encryption hierarchy
DPAPI
DPAPI is a standard library that ship with the operating
system that does not require any addition libraries to function properly. The DPAPI master key is setup and secured
using the credentials of the operating system “system” account when the
operating is setup so does not require any work to configure.
Service Master Key
Every SQL Server has a single Service Master Key (SMK) that
is created automatically during install and does not require any work to set it
up. The SMK has administration functions
that allow the SMK to be backed up and restored to a file that allows the SMK
to be retrieved in a disaster recovery scenario. If the SMK is backed up, it should ideally be
written to a removable device and stored in a secure location as the device
contains “the keys to the empire” and can be used to access any encrypted data
on the server.
Backup
The SMK should ALWAYS be backed up immediately after SQL
Server has been installed on a server.
This should be run using SSMS on the server.
BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD
= 'password'
Restore
Restoring an SMK will decrypt and re-encrypt all it’s
dependant keys. If this process fails at
any point, the entire process will fail in which case “[FORCE]” can be
used. FORCE will almost certainly result
in the loss of some data.
RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD
= 'password' [FORCE]
Alter
In the event of key maintenance
or a suspected key compromise, there is a command :
ALTER SERVICE MASTER KEY
Database Master Keys
Each database can have a single database master key (DMK)
that requires explicitly setting up be the DBA.
Create
The following command creates a DMK and stores it in the
database encrypted by both the SMK and the password:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
Backup
Like the SMK, DMKs can be backed up for disaster recovery at
a later stage. Any DMK backup files MUST
be stored securely.
BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD
= 'password'
Restore
Restoring a DMK requires the original password to decrypt
the data and a new password with which to re-encrypt it.
RESTORE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD
= 'password'
ENCRYPTION BY PASSWORD
= 'password'
[FORCE]
Drop
A key can be dropped when it is no longer needed. Ensure that you have decrypted any data that
the DMK has been used to encrypt before doing so.
DROP MASTER KEY
Certificates
SQL Server can create X509 certificates which can be used to
directly encrypt data, or can be used with keys to provide encryption:
CREATE CERTIFICATE TestCertificate
WITH SUBJECT = 'This is
a test certificate',
EXPIRY_DATE = '01/01/2020';
Keys
Keys are created using the following function :
CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM =
TRIPLE_DES
ENCRYPTION BY
CERTIFICATE TestCertificate;
CREATE SYMMETRIC KEY TestSymmetricKey
WITH ALGORITHM =
AES_256
ENCRYPTION BY
CERTIFICATE TestCertificate;
Encrypting and decrypting data
Once a full encryption hierarchy is setup, encrypting and
decrypting data is a straightforward process.
All procedures that require an encryption function to be called must
start by opening the appropriate key:
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION
BY CERTIFICATE MyCertificate;
Once the key is open, the Key_GUID function can be used to
get the appropriate key and then the EncryptByKey function can be used to
encrypt and decrypt the data
SELECT userid, EncryptByKey(Key_GUID('MySymmetricKey'), Password)
FROM users
SELECT userid, convert(varchar,
DecryptByKey(Key_GUID('MySymmetricKey'), Password))
FROM users
Setting up tables for encrypted data
Encrypted data carries an overhead in terms of data storage
which means that the size of fields will often need to be increased. For example, a plain-text varchar(20) needs
to be extended to varchar(60) to store 20 characters of TripleDES encrypted
text. To determine the size of the
fields required, the following UDF can be used:
USE [myDatabase]
GO
/****** Object:
UserDefinedFunction [dbo].[CalculateCipherLen] Script Date: 03/02/2009 10:04:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[CalculateCipherLen]( @KeyName sysname,
@PTLen int, @UsesHash int = 0 )
RETURNS int
as
BEGIN
declare @KeyType nvarchar(2)
declare @RetVal int
declare @BLOCK int
declare @IS_BLOCK int
declare @HASHLEN int
-- Hash length that
SET @HASHLEN = 20
SET @RetVal = NULL
-- Look for the
symmetric key in the catalog
SELECT @KeyType = key_algorithm FROM sys.symmetric_keys
WHERE name = @KeyName
-- If parameters are
valid
if( @KeyType is not
null AND @PTLen > 0)
BEGIN
-- If hash is
being used. NOTE: as we use this value to calculate the length, we only use 0
or 1
if( @UsesHash
<> 0 )
SET
@UsesHash = 1
-- 64 bit
block ciphers
if( @KeyType =
N'R2' OR @KeyType = N'D' OR @KeyType = N'D3' OR @KeyType = N'DX' )
BEGIN
SET
@BLOCK = 8
SET
@IS_BLOCK = 1
END
-- 128 bit
block ciphers
else if(
@KeyType = N'A1' OR @KeyType = N'A2' OR @KeyType = N'A3' )
BEGIN
SET
@BLOCK = 16
SET
@IS_BLOCK = 1
END
-- Stream
ciphers, today only RC4 is supported as a stream cipher
else
BEGIN
SET
@IS_BLOCK = 0
END
-- Calclulate
the expected length. Notice that the formula is different for block ciphres
& stream ciphers
if( @IS_BLOCK
= 1 )
BEGIN
SET
@RetVal = ( FLOOR( (8 + @PTLen + (@UsesHash * @HASHLEN) )/@BLOCK)+1 ) * @BLOCK
+ 16 + @BLOCK + 4
END
else
BEGIN
SET
@RetVal = @PTLen + (@UsesHash * @HASHLEN) + 36 + 4
END
END
return @RetVal
END
No comments:
Post a Comment