взято с http://dbasimple.blogspot.ru/2013/08/ms-sql-server.html
Продолжая тему защиты данных в базе данных в MS SQL Server, рассмотрим прозрачное шифрование (Transparent Data Encryption — TDE ) в MS SQL Server. Демонстрация шифрования будет проводится на MS SQLServer 2012 SP1 Enterprise Edition для базы данных клиентов [DbClients], но данный сценарий будет рабочий и для MS SQL Server 2005/2008. Прозрачное шифрование доступно только в редакциях Enterprise или Datacenter.
Итак, для начало немного теории:
Как всегда все хорошо описано в BOL —http://msdn.microsoft.com/ru-ru/library/bb934049.aspx .
Функция прозрачного шифрования данных (TDE) выполняет в реальном времени шифрование и дешифрование файлов данных и журналов в операциях ввода-вывода. При шифровании используется ключ шифрования базы данных (DEK), который хранится в загрузочной записи базы данных для доступности при восстановлении. Ключ шифрования базы данных является симметричным ключом, защищенным сертификатом, который хранится в базе данных master на сервере, или асимметричным ключом, защищенным модулем расширенного управления ключами. Функция прозрачного шифрования данных защищает «неактивные» данные, то есть файлы данных и журналов
Основное прозрачного шифрования, это то , что:
Шифрование файла базы данных проводится на уровне страниц. Страницы в зашифрованной базе данных шифруются до записи на диск и дешифруются при чтении в память. Прозрачное шифрование данных не увеличивает размер зашифрованной базы данных.
При этом не стоит забывать, что прозрачное шифрование не шифрует данные при передачи через каналы связи, для этого к примеру можно включить шифрование соединения.
Так же надо учитывать, что шифрование\дешифрование данных это нагрузка на CPU, следовательно при проектирование необходимо учесть запас мощностей процессоров.
Ниже представлена схема работы прозрачного шифрования (так же взята из BOL):
Порядок включения шифрования:
- Создать главный ключ
- Создать или получить сертификат, защищенный главным ключом
- Создать ключ шифрования базы данных и защитить его с помощью сертициката
- Задать ведение шифрование базы данных
Перейдем к самой реализации:
1) Создание главного ключа шифрования
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘StrongPassword#1’;
Созданный наш ключ можно увидеть в view:
select * from sys.key_encryptions
Удалить ключ можно инструкцией:
drop master key
После того как создали главный ключ, необходимо сделать его резервную копию и поместить резервную копию в надежное место:
BACKUP MASTER KEY TO FILE = ‘c:\sqltest2012_masterkey_backup.bak’
ENCRYPTION BY PASSWORD = ‘Password1′
2) Создание сертификата
CREATE CERTIFICATE TDECertificate WITH SUBJECT =’TDE Certificate for DBClients’
Проверка наличия созданного сертификата:
select * from sys.certificates where name=’TDECertificate’
Создание резервной копии сертификата с закрытым ключом:
BACKUP CERTIFICATE TDECertificate
TO FILE = ‘c:\sqltest2012_cert_TDECertificate’
WITH PRIVATE KEY
(
FILE = ‘c:\sqltest2012SQLPrivateKeyFile’,
ENCRYPTION BY PASSWORD = ‘Password#3’
);
3) Создание ключа шифрования в нашей базе данных с использование нашего сертификата
USE [DBClients]
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECertificate;
4) И наконец включаем шифрование для нашей базы данных
ALTER DATABASE [DBClients]
SET ENCRYPTION ON ;
В итоге имеем базу данных с прозрачным шифрованием.
Несколько моментов в работе с базой данных с включенным шифрованием:
Если создать резервную копию и попытаться восстановить на другом сервере, то получим ошибку:
Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ‘0x5B139FF1F2C5ED9EB3D503E78A63DEF3DD1FD96F’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Такую же ошибку получим и при попытки присоединения файлов базы данных.
Порядок восстановления базы данных с прозрачным шифрованием на другом экземпляре MS SQL Server:
1) Создать мастер главный ключ шифрования на сервере MS SQL Server
2) Восстановить из резервной копии сертификат с закрытым ключом.
CREATE CERTIFICATE TDECertificate
FROM FILE = ‘c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\sqltest2012_cert_TDECertificate’
WITH PRIVATE KEY (FILE = ‘c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\sqltest2012SQLPrivateKeyFile’,
DECRYPTION BY PASSWORD = ‘Password#3’);
3) Восстановить шифрованную базу данных или присоединить файлы базы данных с включенным шифрованием. БД готова для работы.
В заключение, прозрачное шифрование это один из шагов безопасного хранения базы данных, но при этом надо не забывать про резервное копирование сертификатов с закрытым ключами, иначе резервная копия будет бесполезна.
