Основы MS SQL Server шифрования на примере симметричного шифрования

Рассмотрим один из способов шифрования данных MS SQL Server 2008R2, а именно использование симметричного ключа, далее с помощью данного ключа зашифруем столбец в таблице.

Для начало немного теории:

Симметричный ключ –это ключ шифрования который используется как для шифрования данных, так и для их расшифровки. При его создании он должен быть зашифрован с помощью одним из средств: сертификат, пароль, симметричным\ассиметричным ключом. В примере будем шифровать с помощью сертификата.

Для того чтобы создать сертификаты и симметричный ключ в базе данных, нужно создать главный ключ базы данных (master key). Главный ключ базы данных  шифруется с помощью главного ключа службы, и хранится как в самой базе данных так и её копия в базе данных master.

Главный ключ службы является вершиной иерархии шифрования ms sql server. Он создается автоматически, когда он понадобится при шифровании.

Для шифрования отдельный строк данных  необходимы функции шифрования – мы будем использовать EncryptByKey \ DescryptByKey.

В итоге имеем следующую цепочку:

Главный ключ службы -> Главный ключ базы данных -> Ключи и сертификаты в базе данных.

Соответственно, не имея одного из компонентов, мы не сможем расшифровать наши данные. Поэтому не забываем делать резервные копии ключей.
Перейдем от теории к практики:
Задача: зашифровать поле  в таблице.

1) Для начало создадим ключ базы данных:

CREATE MASTER KEY ENCRYPTION BY

    PASSWORD= 'superpassword#12'

2)Затем создадим сертификат для шифрования:

CREATE CERTIFICATE cert1

   WITH SUBJECT = 'Certificate for Admin bd';

3) Затем создадим симметричный ключ шифрования с помощью сертификата:

CREATE SYMMETRIC KEY SSN_Key_01

    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE cert1;
GO

Проверить наличие наших ключе и сертификатов можно выполнив запросы:

SELECT * FROM sys.symmetric_keys

SELECT * FROM sys.certificates

4) После того как все создали, необходимо сделать резервные копии наших ключе и сертификатов:

Создание резервной копии главного ключа базы данных

BACKUP MASTER KEY TO FILE = 'c:\adminbd'

  ENCRYPTION BY PASSWORD = 'password@1' ;
GO

Создание резервной копии сертификата:

BACKUP CERTIFICATE cert1  TO FILE = 'c:\cert1_backup';
GO

 И самое главное, создание резервной копии главного ключа службы:

BACKUP SERVICE MASTER KEY TO FILE = 'c:\service_master_key’

      ENCRYPTION BY PASSWORD = 'Password@1'

 5) После этого можно шифровать\дешифровать данные  в таблице.

Но перед тем как использовать шифрование\дешифрование, необходимо открыть ключ шифрования.

Пример вставки с шифрованием:

OPEN SYMMETRIC KEY SSN_Key_01

   DECRYPTION BY CERTIFICATE cert1;

INSERT INTO [dbo].[servicelogins]

           ([login]           ,[srv]                      ,[encrpsw]
           ,[bd]           ,[description]           ,[owner]           )
VALUES

           ('test_login'

           ,'test_srv'

           ,EncryptByKey(Key_GUID('SSN_Key_01'),'testpassword' )

           ,'test bd'
           ,'[description]'
           ,'[owner]'
           )

Чтение данных:

OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE cert1;

select  [login]

      ,[srv]
      ,[encrpsw]
      ,convert(char,DecryptByKey([encrpsw])) as p
      ,[bd] ,owner
from [dbo].[servicelogins]
go

Ключ открывается на время сеанса.

При чтение таблицы без ключа ошибки не будет, но данные соответсвено будут в зашифрованном виде.   

Самое, главное при создание резервной копии и восстановлении её на другом сервере, база данных будет восстановлена, данные будут,  сертификаты и ключи базы данных так же будут восстановлены, но читать с помощью их  данные не получится, т.к главный ключ службы другой. Для этого необходимо восстановить главный ключ из резервной копии:

RESTORE SERVICE MASTER KEY FROM FILE = 'c:\service_master_key’

    DECRYPTION BY PASSWORD = 'Password@1' force
  

Параметр ‘Force’ указывает, что можно заменить текущий ключ. Внимание, если на сервере есть зашифрованные данные текущего мастера ключа, то они будут недоступные при восстановлении нового ключа. Данный факт стоит учитывать при резервном копировании и восстановлении.

 Удачной работы!