Прозрачное шифрование баз данных в Microsoft SQL Server

Введение

В Microsoft SQL Server 2005 реализована достаточно приличная поддержка криптографии. С помощью встроенных средств можно шифровать данные, используя как симметричные, так и ассиметричные алгоритмы. Есть поддержка операции хеширования и электронной подписи. Реализована неплохая система управления ключами (насколько это вообще возможно без использования специальных устройств). Этих возможностей более чем достаточно, чтобы построить криптографическую систему защиты данных, конечно, если поставленную задачу вообще можно решить применением криптографии.

Но чтобы воспользоваться всеми возможностями, предоставляемыми SQL Server, в приложение должена быть встроена соответствующая функциональность. Причем это касается не только кода, но и структуры базы данных, в которую, вероятно, также потребуется внести изменения, диктуемые особенностями работы с зашифрованными данными. Применение криптографии может привести к падению производительности приложения за счет дополнительных затрат на шифрование и расшифровку. Кроме того, бесполезность использования индексов на зашифрованных данных также может сказаться на производительности приложения не лучшим образом. Все это должно быть учтено в приложении, а защищаться должны только те данные, для которых это действительно нужно.

А что делать, если приложение ничего о криптографических возможностях SQL-сервера не знает, и никаких своих средств защиты данных на сервере тоже не предлагает. В этом случае можно попытаться задействовать ряд технических средств, имеющихся в арсенале администратора. В первую очередь на ум приходит файловая система с шифрованием (Encrypting File System). Ее использование позволяет защититься от некоторых угроз, но, например, проблемы защиты данных в резервной копии, файловая система с шифрованием (EFS) не решает.

В Microsoft SQL Server 2008 появилось новое решение для обозначенных выше проблем - это прозрачное шифрование БД (Transparent Data Encryption или TDE). TDE позволяет шифровать базы данных целиком. Когда страница данных сбрасывается из оперативной памяти на диск, она шифруется. Когда страница загружается обратно в оперативную память, она расшифровывается. Таким образом, база данных на диске оказывается полностью зашифрованной, а в оперативной памяти - нет. Основным преимуществом TDE является то, что шифрование и расшифровка выполняются абсолютно прозрачно для приложений. Следовательно, получить преимущества от использования TDE может любое приложение, использующее для хранения своих данных Microsoft SQL Server 2008. При этом модификации или доработки приложения не потребуется.

К сожалению, планируется, что Transparent Data Encryption (TDE) будет доступно только в Enterprise- и Developer-редакциях SQL Server 2008.

Иерархия ключей

Зашифровать данные, как правило, не составляет никакого труда. Алгоритмы шифрования хорошо известны, и многие из них реализованы в операционной системе (например, AES). Гораздо сложнее придумать, как защитить ключ, которым эти данные зашифрованы. Ведь если мы "положим" его рядом с зашифрованными данными, то мы получим в лучшем случае обфускацию, но никак не надежную защиту. Для решения этой задачи в SQL Server применяется специальная иерархия ключей. В контексте Transparent Data Encryption (TDE) она строится следующим образом:

• Для каждой базы данных, которая шифруется с помощью Transparent Data Encryption (TDE) создается специальный ключ - Database Encryption Key (DEK). Этот ключ используется для шифрования данных.
• Database Encryption Key (DEK) шифруется сертификатом, который должен быть создан в БД master.

Далее стандартно:

• Этот сертификат шифруется главным ключом БД master.
• Главный ключ БД master шифруется главным ключом службы (Service Master Key или SMK).
• Главный ключ службы (SMK) шифруется с помощью DPAPI.

Вся иерархия приведена на следующем рисунке:

Такая схема позволяет SQL Server в любой момент времени получить доступ к ключу, которым зашифрована БД, а, следовательно, и к зашифрованным данным. И в тоже время, никто другой получить доступ к этим данным не может. Но к сожалению, это теория, а на практике есть очень ограниченный список угроз, которым способно противостоять Transparent Data Encryption (TDE). 

Решение каких задач по плечу TDE?

Если злоумышленник смог получить доступ к защищаемым данным через SQL Server, то Transparent Data Encryption (TDE) оказывается абсолютно бесполезным. Данные зашифрованы только на диске, а в памяти - нет. Зашифрованная база данных выглядит для пользователей абсолютно так же, как и незашифрованная.

Для защиты от администраторов Transparent Data Encryption (TDE) так же бессильно. Администратор SQL Server может шифрование просто отключить. Системный администратор при желании также сможет найти тысячу и один способ получить доступ к зашифрованным данным (даже если он не является администратором SQL Server).

Что реально может сделать Transparent Data Encryption (TDE), так это защитить файлы баз данных и резервные копии на случай их похищения. И это уже неплохо. Если снять копию с файлов активной БД не так просто (хотя и возможно), то похищение резервной копии при наличии к ним доступа не представляет никаких проблем (какие могут быть проблемы сунуть носитель с резервной копией в карман).

Но и тут есть свои ограничения. Файлы БД и резервные копии будут надежно защищены, только если злоумышленнику не удастся вместе с данными заполучить и ключ. Если ему это удастся, то он без проблем расшифрует секретные данные. Самым слабым звеном тут является главный ключ службы (SMK), который находится на вершине иерархии ключей и который защищается с помощью DPAPI. Подробнее об этом можно почитать в моем блоге (http://blogs.gotdotnet.ru/yliberman) в сообщении "Вся правда о Service Master Key в SQL Server 2005".

Также следует отметить, что Transparent Data Encryption (TDE) - это не замена тем криптографическим возможностям, которые есть в SQL Server 2005. Если шифрование в SQL Server 2005 работает на уровне значений и столбцов (за что часто называется "cell-level"-шифрованием), то Transparent Data Encryption (TDE) работает на гораздо более высоком уровне - на уровне базы данных. Задачи, которые решаются с помощью обоих подходов во многом пересекаются, но у каждого из них есть свои преимущества и недостатки. Оба подхода используют одни и те же криптографические алгоритмы (с теми же длинами ключей), так что криптографически оба подхода обеспечивают одинаковый уровень защиты данных.

Настройка TDE

Чтобы зашифровать базу данных с помощью Transparent Data Encryption (TDE), нужно выполнить следующие шаги:

1. Создать главный ключ БД master (если он не был создан ранее):

USE master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$Strong$Password$123'

1. Создать или импортировать сертификат, закрытый ключ которого должен быть зашифрован главным ключом БД master:

CREATE CERTIFICATE DEK_EncCert WITH SUBJECT = 'DEK Encryption Certificate'

1. Далее в базе данных, которую мы собираемся шифровать, нужно создать Database Encryption Key (DEK). DEK шифруется сертификатом, который мы создали на предыдущем шаге.

USE MySecretDB
go
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DEK_EncCert

Проверить, что Database Encryption Key (DEK) действительно создан, можно с помощью системного представления sys.dm_database_encryption_keys.

SELECT DB_NAME(database_id), * FROM sys.dm_database_encryption_keys

1. В этот момент все готово для того, чтобы включить шифрование базы данных. Включаем.

ALTER DATABASE MySecretDB SET ENCRYPTION ON

С этого момента начинается процесс первоначального шифрования базы данных. Он выполняется "в фоне" в отдельном потоке. Отследить прогресс выполнения этой операции можно по столбцу percent_complete уже упомянутого нами ранее системного представления sys.dm_database_encryption_keys. Так, если выполнить приведенный ниже запрос в процессе выполнения первоначального шифрования базы данных, то мы можем получить, например, следующий результат:

SELECT DB_NAME(database_id), encryption_state, percent_complete FROM sys.dm_database_encryption_keys

А когда процесс первоначального шифрования базы данных будет завершен, запрос вернет следующий результат:

В столбце encryption_state содержится информация о текущем состоянии базы данных. Согласно SQL Server Books Online (BOL), в контексте Transparent Data Encryption (TDE) БД может находиться в одном из следующих состояний:

• 0 - Database Encryption Key (DEK) не создан.
• 1 - Database Encryption Key (DEK) создан, но база данных не зашифрована.
• 2 - Выполняется первоначальное шифрование.
• 3 - База данных зашифрована.
• 4 - Идет смена ключа.
• 5 - Идет расшифровка.

Я думаю, что вы уже обратили внимание на то, что в результате включения шифрования для нашей БД база данных tempdb также стала шифроваться. Тому, что именно шифруется для обеспечения безопасности данных, посвящен следующий раздел.

Что именно шифруется?

Когда для БД включено Transparent Data Encryption (TDE), шифруются как ее файлы данных, так и ее журнал транзакций.

Кроме того, как только на экземпляре SQL Server включается шифрование хотя бы одной БД, база данных tempdb также начинает шифроваться. За что "пострадала" база данных tempdb, понятно, - она может содержать куски секретной информации из шифруемых баз. А вот за что должны "страдать" приложения, работающие с другими, не зашифрованными базами данных? Их запросы, выполнение которых требует участия базы данных tempdb (большие сортировки, например), очевидно, станут выполняться медленнее. Дело, видимо, в том, что не всегда возможно определить источник данных, которые попадают в tempdb, и поэтому для гарантии она шифруется целиком.

Разберемся по порядку:

Файлы данных

Когда для базы данных включается шифрование, SQL Server, как уже упоминалось выше, в отдельном потоке выполняет шифрование всех файлов данных этой БД. Но есть области, которые остаются незашифрованными:

• File Header Page (Page *:0). Это первая страница, которая присутствует во всех файлах данных.
• Boot Page (Page 1:9) . Эта страница присутствует только в первом файле данных БД и расположена по смещению 0x12000 байт от начала файла. Именно здесь хранится зашифрованный сертификатом Database Encryption Key (DEK), а так же почти вся информация, которая доступна через системное представление sys.dm_database_encryption_keys. Для отображения содержимого этой страницы, помимо универсальной команды DBCC PAGE, предназначена команда DBCC DBINFO (информацию, относящуюся к TDE она, к сожалению, не возвращает).
• Заголовки страниц (первые 0x60 байт каждой страницы) также остаются незашифрованными.

Когда SQL Server зашифровывает страницу, он устанавливает для нее соответствующий флаг (в поле m_flagBits, которое физически расположено по смещению 4 от начала страницы и занимает 2 байта, устанавливается бит 0x800). Интересно, что мы никогда не увидим этот бит установленным через DBCC PAGE, так как в памяти все страницы расшифрованы (хотя в файле флаг для этой страницы может быть установлен).

Журнал транзакций

В отличие от файлов данных, операция первоначальной шифровки для журналов транзакций не выполняется. То есть информация о транзакциях, которая уже есть в журналах транзакций на момент включения шифрования, остается незащищенной. Шифруется только информация о новых транзакциях. По крайней мере, такое поведение мы можем наблюдать в CTP6.

Как следствие, если есть полная резервная копия БД до того как она была зашифрована, то даже после включения шифрования, можно сделать резервную копию журнала транзакций уже зашифрованной БД, а потом восстановить ее на момент, предшествующий шифрованию, и получить доступ к секретным данным. При этом восстановить такой архив можно без доступа к ключу (например, на другом сервере).

Следующий сценарий демонстрирует эту возможность:

USE master
go
-- Создаем главный ключ базы данных master
IF(not EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')) 
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$Strong$Password$123'
go
-- Создаем сертификат, которым будем шифровать DEK
CREATE CERTIFICATE DEK_EncCert WITH SUBJECT = 'DEK Encryption Certificate'
go 
-- Создаем базу данных, которую будем шифровать
CREATE DATABASE MySecretDB
go
-- И сразу делаем ее полную резервную копию (секретных данных здесь нет)
BACKUP DATABASE MySecretDB TO DISK = 'c:\temp\MySecretDB.bak' WITH INIT
go
USE MySecretDB
go 
-- Создаем таблицу и заполняем ее секретными данными
-- Делаем это в транзакции с меткой T1
BEGIN TRAN T1 WITH MARK

CREATE TABLE dbo.MySecretTable (Data varchar(200) not null)

INSERT dbo.MySecretTable (Data) VALUES ('It is my secret')

COMMIT
go 
-- Шифруем базу данных
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DEK_EncCert
go
ALTER DATABASE MySecretDB SET ENCRYPTION ON
go 

Проверяем, что база данных зашифрована:

SELECT DB_NAME(database_id), encryption_state FROM sys.dm_database_encryption_keys

Делаем резервную копию журнала транзакций (база данных уже зашифрована):

BACKUP LOG MySecretDB TO DISK = 'd:\temp\MySecretDB.trn'

Стираем нашу базу данных, а затем и сертификат, которым мы шифровали ее Database Encryption Key (DEK). Нам это нужно для того, чтобы эмулировать восстановление БД на другом сервере.

USE master
go
DROP DATABASE MySecretDB
go
DROP CERTIFICATE DEK_EncCert 
go

Теперь попытаемся восстановить базу данных. Сначала полностью:

RESTORE DATABASE MySecretDB FROM DISK = 'd:\temp\MySecretDB.bak' WITH NORECOVERY
RESTORE LOG MySecretDB FROM DISK = 'd:\temp\MySecretDB.trn'

Как и следовало ожидать, попытка восстановления базы данных закончилась ошибкой. Сертификат, которым зашифрован Database Encryption Key (DEK), более недоступен.

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint '0x347D263A185EF41D8EB06AE425F7599AD2D0FCC3'.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally. 

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

RESTORE DATABASE MySecretDB FROM DISK = 'd:\temp\MySecretDB.bak' WITH NORECOVERY
RESTORE LOG MySecretDB FROM DISK = 'd:\temp\MySecretDB.trn' WITH STOPATMARK = 'T1' 
go
USE MySecretDB
go
-- Запрос к секретным данным
SELECT * FROM dbo.MySecretTable
go

Все, доступ к секретным данным мы получили:

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

Что из этого следует? Если мы шифруем БД, которая уже содержит секретную информацию, то мы должны либо пересоздать журнал транзакций, либо позаботиться о том, чтобы незашифрованные транзакции в журнале транзакций были перезаписаны новыми зашифрованными транзакциями.

База данных tempdb

Как уже упоминалось ранее, если на сервере включается шифрование хотя бы для одной пользовательской БД, то база данных tempdb тоже автоматически начинает шифроваться. Причем ситуация с tempdb напоминает ситуацию с журналами транзакций. Все новые данные, записываемые в tempdb шифруются, в то время как первоначального шифрования данных, которые там уже есть, не выполняется. В результате данные, которые уже были в tempdb на момент включения шифрования, остаются незащищенными. Но в данном случае решить проблему легко, достаточно перезапустить сервер. В этом случае tempdb будет создана заново, а все записываемые в нее данные будут шифроваться с самого начала.

Внутреннее устройство TDE

Идея исследования

SQL Server сам не реализует никаких криптографических алгоритмов. Вместо этого он целиком полагается на те возможности, которые реализованы в операционной системе. Это справедливо как для SQL Server 2005, так и для SQL Server 2008. То есть когда серверу требуется, например, зашифровать некоторую информацию, он просто вызывает соответствующие CryptoAPI-функции. Этим мы и воспользуемся. Мы постараемся разобраться с тем, какие CryptoAPI-функции вызывает SQL Server для реализации логики TDE. Это позволит нам гораздо лучше понять, как на самом деле работает прозрачное шифрование.

Для того чтобы это сделать, мне пришлось написать специальный инструмент, который я назвал SQLInternalsProfiler. Его основная задача - это профилирование вызовов функций (в т.ч. API функций) выполняемых сервером, и отслеживание общей статистики по этим вызовам или получение данных, относящихся к каждому вызову в отдельности. SQLInternalsProfiler представляет собой динамическую библиотеку, в которой реализовано несколько расширенных хранимых процедур. Далее приведено краткое описание тех процедур, которыми мы будем пользоваться в этой статье:

Скачать SQLInternalsProfiler можно в моем блоге, в сообщении "SQL Internals Profiler" (http://blogs.gotdotnet.ru/personal/yliberman/PermaLink.aspx?guid=963f9740-420d-4a1e-a4b6-fca535b2f636). Там же приведено подробное описание этого инструмента.

Куда тратится процессорное время в TDE?

Итак, приступим к исследованиям:

Шаг 1. Давайте создадим базу данных, в которой будем проводить эксперименты. Также создадим для этой базы данных DEK (Database Encryption Key), но шифрование пока включать не будем.

USE master
go
-- Создаем главный ключ базы данных master (если он не был создан ранее)
IF(not EXISTS(
  SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##'))
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$Strong$Pass$123'
go
-- Создаем сертификат которым будет шифроваться DEK (Database Encryption Key)
CREATE CERTIFICATE DEK_EncCert WITH SUBJECT = 'DEK encryption certificate'
go
-- Создаем базу данных для экспериментов
CREATE DATABASE TDE_TEST_DB
go
USE TDE_TEST_DB
go
-- Создаем DEK (Database Encryption Key)
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DEK_EncCert 
go
-- Выводим информацию о состоянии базы данных
SELECT DB_NAME(database_id), * FROM sys.dm_database_encryption_keys

Шаг 2. Теперь создадим в этой базе данных хранимую процедуру dbo.spu_TDE_Test1, которую будем использовать для создания тестовой нагрузки на SQL Server. Нагрузка будет представлять собой вставку большого числа строк в таблицу. Это один из самых "неприятных" для TDE сценариев, так как он связан с большим объемом ввода/вывода. Кроме того, процедура измеряет общее время, которое заняло выполнение тестовой нагрузки и отдельно время потребления CPU.

USE TDE_TEST_DB
go
-- Процедура выполняющая тестирование
CREATE PROCEDURE dbo.spu_TDE_Test1 AS
BEGIN
    SET NOCOUNT ON
    -- Создаем таблицу, в которую будем вставлять данные
    IF(OBJECT_ID('dbo.TestTable') is not NULL) DROP TABLE dbo.TestTable
    CREATE TABLE dbo.TestTable
    (
        Id int primary key, 
        Data varchar(8000) not null
    )
    -- Сбрасываем все несохраненные изменения на диск и очищаем кэш
    -- (нужно для обеспечения повторяемости результатов теста)
    CHECKPOINT
    DBCC DROPCLEANBUFFERS 
    -- Запоминаем данные перед тестом
    DECLARE @time_start datetime = GETDATE(), @cpu_start float = @@CPU_BUSY    
    -- Основной цикл теста - вставляем много строк в таблицу
    DECLARE @i int = 0 
    WHILE(@i < 100000) BEGIN
        INSERT dbo.TestTable(Id, Data) VALUES (@i, REPLICATE('a', 8000))
        SET @i += 1
    END
    CHECKPOINT
    -- Считаем результаты (общее затраченное время и время работы CPU)
    DECLARE @duration float, @cpu_used float, @cpu_count int
    SET @duration = DATEDIFF(ms, @time_start, GETDATE()) / 1000.0
    SET @cpu_count = (SELECT  COUNT(*) FROM sys.dm_os_schedulers 
                                     WHERE scheduler_id < 255 And is_online = 1)
    SET @cpu_used = 
      (@@CPU_BUSY - @cpu_start) * @@TIMETICKS / 1000000.0 / @cpu_count     
    -- Выводим результаты
    SELECT 
        @duration as duration, 
        @cpu_used as cpu_used,
        CONVERT(decimal(6, 1), 100.0 * @cpu_used / @duration / @cpu_count) 
          as [cpu_used%]
END
go

Шаг 3. Запускаем процедуру и смотрим на результаты, полученные для незашифрованной базы данных (при первом запуске процедуры будет выполняться расширение базы данных, так что результаты нужно "снимать" как минимум со второго запуска).

EXECUTE dbo.spu_TDE_Test1

Получившийся у меня результат:

Шаг 4. Теперь включаем шифрование базы данных.

-- Включаем шифрование
ALTER DATABASE TDE_TEST_DB SET ENCRYPTION ON
-- Ждем пока данные в базе данных будут полностью зашифрованы
WHILE(1=1) BEGIN
  WAITFOR DELAY '00:00:01' 
  IF(EXISTS(SELECT * FROM sys.dm_database_encryption_keys 
    WHERE database_id = DB_ID(' TDE_TEST_DB') And encryption_state = 3)) BREAK
END 
-- Выводим состояние базы данных
SELECT DB_NAME(database_id), * FROM sys.dm_database_encryption_keys

Шаг 5. Снова запускаем ту же тестовую процедуру, но уже для зашифрованной базы данных. Процедура возвращает нам общее время выполнения теста и потребление времени CPU. C помощью SQLInternalsProfiler'а мы так же получаем список API-функций из динамической библиотеки ADVAPI32.DLL (все основные криптографические функции реализованы именно в этой библиотеке), выполнение которых заняло значительное время (> 0.5%). По каждой функции выводится общее время выполнения и количество сделанных вызовов.

-- Включаем перехват всех функций импортируемых SQL сервером из ADVAPI32.dll
EXECUTE master..xp_SQLInternalsProfiler_AddSource_IAT 'ADVAPI32.dll', '*', '.', 1
-- Запускаем тест
EXECUTE dbo.spu_TDE_Test1
-- Сохраняем статистику по вызовам функций во временную таблицу
CREATE TABLE #Results
(
    source_name nvarchar(128) not null,
    [count] bigint not null, 
    second_time decimal(12, 6) not null,
    percent_time decimal(8, 2) not null
) 
INSERT #Results 
EXECUTE master..xp_SQLInternalsProfiler_Results 897
-- Отключаем перехваты 
EXECUTE master..xp_SQLInternalsProfiler_StopAll 1
-- Выводим информацию по наиболее затратным функциям и общий итог
SELECT * FROM #Results WHERE percent_time > 0.5 
UNION ALL 
SELECT 'Total', SUM([count]), SUM(second_time), SUM(percent_time) FROM #Results
ORDER BY second_time DESC
-- Удаляем временную таблицу
DROP TABLE #Results

Результат:

Если свести все результаты вместе, получаем:

Надо сказать, что полученные результаты ставят больше вопросов, чем дают ответов. Эти результаты не совсем укладываются в декларируемую логику работы TDE - "при сохранении на диск данные шифруются, а при чтении с диска расшифровываются". В частности мы видим, что очень много процессорного времени ушло на расшифровку данных, хотя, если следовать логике TDE, то вообще ничего не должно было расшифровываться (мы же только пишем данные). Также из результата видно, что значительную нагрузку на процессор создала операция восстановления ключа (CryptImportKey). Почему происходит именно так, мы разберемся в следующем разделе.

Какую функцию выполняют все эти вызовы?

Чтобы понять, что же на самом деле происходит, давайте проведем еще один эксперимент. Выполним по сути тот же тестовый запрос, что и выше, но затребуем от SQLInternalsProfiler более детальную информацию. Нам нужно, чтобы собранная информация была сгруппирована не только по криптографической операции, но и по размеру обрабатываемых ей данных. Это позволит нам лучше понять, для чего выполняется та или иная операция.

SET NOCOUNT ON
-- Очищаем кэш
CHECKPOINT
DBCC DROPCLEANBUFFERS
-- Настраиваем перехват криптографических функций
EXECUTE master..xp_SQLInternalsProfiler_AddSource_IAT 'ADVAPI32.dll',
  'CryptEncrypt', 'n7dgp6opb6', 1
EXECUTE master..xp_SQLInternalsProfiler_AddSource_IAT 'ADVAPI32.dll',
  'CryptDecrypt', 'dgp6opb6', 1
EXECUTE master..xp_SQLInternalsProfiler_AddSource_IAT 'ADVAPI32.dll',
  'CryptImportKey', 'dg3ob3', 1
EXECUTE master..xp_SQLInternalsProfiler_AddSource_IAT 'ADVAPI32.dll',
  'CryptAcquireContextW', '.', 1
-- Создаем таблицу, куда будем вставлять данные
IF(OBJECT_ID('dbo.TestTable1') is not NULL) DROP TABLE dbo.TestTable1
CREATE TABLE dbo.TestTable1
(
  Id int primary key,
  Data varchar(8000) not null
)
-- Вставляем в таблицу много строк
DECLARE @i int
SET @i = 0
BEGIN TRAN
WHILE(@i < 50000) BEGIN
  INSERT dbo.TestTable1(Id, Data) VALUES(@i, REPLICATE('a', 8000))
  SET @i = @i + 1
END
COMMIT
CHECKPOINT
-- Создаем таблицу #Result и записываем в нее результат
CREATE TABLE #Results
(
  source_name varchar(128) not null,
  block_size int null,
  [count] bigint not null, 
  second_time decimal(12, 6) not null,
  percent_time decimal(8, 2) not null,
  bytes_count bigint not null
)
INSERT #Results
EXECUTE master..xp_SQLInternalsProfiler_Results 1925
EXECUTE master..xp_SQLInternalsProfiler_StopAll 1

Результатом выполнения приведенного выше запроса является таблица #Results. Информация, собранная в ней, используется ниже для дальнейшего анализа.

Файлы данных

Чтобы отобрать криптографические операции, которые относятся к файлам данных, запросим из таблицы #Results все операции с длиной блока данных, равной 8096 байт (длина страницы 8192 байта минус 96 байт, длина заголовка страницы, который не шифруется) и мало-мальски значимым временем выполнения (не менее одного процента от всего времени выполнения запроса).

-- MDF
SELECT * FROM #Results WHERE block_size = 8096 And percent_time >= 1

С первой строкой все понятно. Она говорит о том, что немногим более 50000 страниц было зашифровано. Именно такое количество строк мы и вставляли. Каждая строка у нас занимала отдельную страницу.

Со второй строкой не все ясно. Откуда вообще взялась расшифровка, да еще в таких количествах?! Как видно из таблицы, расшифровано было даже больше данных, чем зашифровано. И это притом, что мы вообще ничего не читали (только писали). Неявные чтения различного рода служебной информации такого объема расшифровок, конечно, не объясняют. Дело в том, что страница перед записью на диск шифруется непосредственно в кэше страниц (BPool). Очевидно, что в памяти в таком виде она абсолютно бесполезна. Поэтому, после того как операция записи страницы на диск завершается, в памяти страница расшифровывается обратно. Таким образом, получается, что запись страницы в файл данных порождает выполнение сразу двух криптографических операций - шифрования и расшифровки.

Журнал транзакций

Для отображения криптографических операции, относящихся к журналу транзакций, запросим из таблицы #Results все операции с длиной блока данных, большей 8096 байт (мы вставляли строки в таблицу в рамках одной большой транзакции, так что можно ожидать, что SQL Server будет писать в журнал транзакций относительно большими кусками).

-- LDF
SELECT * FROM #Results WHERE block_size > 8096 And percent_time >= 1

Как видим, тут обошлось без сюрпризов. Наши ожидания подтвердились. SQL Server действительно сгруппировал информацию о нескольких INSERT'ах в блоки данных размером немногим менее 64 КБ. Затем каждый блок данных зашифровал и записал в журнал транзакций.

Другие операции (восстановление DEK)

Выше мы разобрались с операциями с длиной блока данных, равной и превышающей 8096 байт. Что же осталось?

SELECT * FROM #Results WHERE block_size < 8096 And percent_time >= 1

А осталось довольно много. Какую задачу выполняют эти операции?

Первые две - это восстановление ключа шифрования базы данных (DEK). Каждый раз, когда SQL Server открывает (создает) файл данных или виртуальный журнал транзакций для зашифрованной базы данных, он восстанавливает (расшифровывает) для него новый экземпляр DEK. Подчеркну, что в случае журналов транзакций DEK восстанавливается (расшифровывается) для каждого виртуального журнала транзакций в отдельности, а не для всего файла журнала в целом! Объем записи в журнал транзакций может быть довольно большим (как в нашем примере), и тогда SQL Server вынужден создавать много новых виртуальных журналов транзакций. Например, за время выполнения нашего тестового запроса SQL Server создал 359 новых виртуальных журналов транзакций. Я получил эту информацию с помощью команды DBCC LOGINFO. Для каждого нового виртуального журнала SQL Server восстановил (расшифровал) DEK, и это породило в нашем случае 359 вызовов функций CryptDecrypt и CryptImportKey.

После того, как SQL Server восстанавливает DEK, он снимает с него 50 независимых копий для того, чтобы каждый шедулер (планировщик времени) мог использовать свою копию ключа. Даже если в системе нет стольких шедулеров, все равно создается 50 экземпляров DEK. Каждая копия создается в отдельном контексте. Итого, на каждый DEK создается 51 контекст. Так в нашем примере образовалось 18309 (= 359 * 51) вызовов функции CryptAcquireContext.

В SQL Server 2008 для защиты DEK используется ассиметричное шифрование (DEK шифруется сертификатом из БД master). Как следствие, восстановление DEK - достаточно долгая и ресурсоемкая операция. Именно поэтому даже всего 359 таких операций (как в нашем примере) создали ощутимую дополнительную нагрузку. В примере я использовал сертификат с длиной ключа 1024 бит (значение по умолчанию). Я пробовал использовать большие длины ключа, например, 2048 бит, и затраты на восстановление DEK иногда перекрывали затраты на все остальное.

Прогнозирование "тормозов" от включения TDE

Эта часть посвящена ответу на вопрос "Как включение TDE отразится на скорости работы приложения?". Вопрос производительности очень актуален в контексте TDE, так как значительные (а иногда очень значительные) дополнительные затраты на шифрование могут стать серьезным барьером на пути применения TDE в реальных проектах.

Давайте вспомним основной принцип работы TDE. Данные шифруются при их записи на диск, а расшифровываются при чтении с диска. Основываясь только на этом принципе, можно сделать один важный вывод - объем дополнительных затрат на шифрование в TDE напрямую зависит от объема ввода/вывода, порождаемого приложением. То есть для приложений, порождающих большой объем операций ввода/вывода, можно ожидать значительных дополнительных затрат на шифрование. Обратное тоже верно. Включение TDE для нетребовательных к дисковой подсистеме приложений скорее всего не приведет к заметной деградации производительности (например, если приложение преимущественно читает данные, и объема оперативной памяти на сервере достаточно, чтобы вместить большую часть этих данных).

Отталкиваясь от вышесказанного, я выбрал два сценария для примеров, которые приведены далее. Первый пример - массированная вставка данных. Второй пример - запрос, генерирующий большой объем физических чтений. Из-за большого объема ввода/вывода оба примера - это очень "неприятные" для TDE сценарии. Приводить пример, где напротив, демонстрируется влияние TDE на приложения, которые не порождают ввода/вывода, я не стал, так как результат очевиден - никакого влияния не будет.

Я также решил не приводить пример влияния TDE на некоторое "типичное" приложение, так как для другого "типичного" приложения результаты могут оказаться совсем другими. Все очень сильно зависит от конкретного приложения. Однако на этот счет есть данные от Microsoft, опубликованные в статье "Database Encryption in SQL Server 2008 Enterprise Edition" на MSDN. Вот что там по этому поводу написано (вольный перевод):

Пример 1. Много пишем

В первом примере мы разберемся с тем, как включение TDE отразится на выполнении массированной вставки. Сначала выполним тестовый запрос без TDE и посмотрим, какую нагрузку он создаст. Потом выполним запрос при включенном TDE и сравним результаты.

Прежде чем переходить непосредственно к тестам, давайте создадим базу данных, в которой будем экспериментировать:

-- Создаем тестовую базу данных
CREATE DATABASE TDE_TEST_DB
go
USE TDE_TEST_DB
go

Теперь можно пускать тестовую нагрузку:

SET NOCOUNT ON
-- Создаем таблицу (если нет), куда будем вставлять данные
IF(OBJECT_ID('dbo.TestTable1') is not NULL) 
    DROP TABLE dbo.TestTable1
CREATE TABLE dbo.TestTable1
(
    id int primary key,
    data varchar(200) not null
)
-- Вставляем в таблицу много строк
DECLARE @i int
SET @i = 0
BEGIN TRAN
WHILE(@i < 5000000) BEGIN
    INSERT dbo.TestTable1(id, data) VALUES(@i, REPLICATE('a', 200))
    SET @i = @i + 1
    IF(@i % 100 = 0) BEGIN
        COMMIT
        BEGIN TRAN
    END
END
IF(@@TRANCOUNT > 0) COMMIT

Вот как выглядит наш тестовый запрос в Мониторе производительности (красная кривая характеризует загрузку процессора в процентах, а синяя кривая - запись на диск в МБ/сек):

Теперь посмотрим, как обстоят дела при включенном TDE. Для этого включим шифрование для тестовой базы данных:

USE master
-- Создаем главный ключ базы данных master (если он не был создан ранее)
IF(not EXISTS(SELECT * 
                FROM sys.symmetric_keys 
                WHERE name = '##MS_DatabaseMasterKey##')) 
  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My$Strong$Pass$123' 
go 
-- Создаем сертификат которым будет шифроваться DEK (Database Encryption Key)
CREATE CERTIFICATE DEK_EncCert WITH SUBJECT = 'DEK encryption certificate'
go 
USE TDE_TEST_DB
go 
-- Создаем DEK (Database Encryption Key)
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE DEK_EncCert 
go 
-- Включаем шифрование
ALTER DATABASE TDE_TEST_DB SET ENCRYPTION ON 
-- Ждем пока данные в базе данных будут полностью зашифрованы
WHILE(1=1) BEGIN 
  WAITFOR DELAY '00:00:01' 
  IF(EXISTS(SELECT * FROM sys.dm_database_encryption_keys 
    WHERE database_id = DB_ID('TDE_TEST_DB') And encryption_state = 3)) BREAK
END 
-- Выводим состояние базы данных
SELECT DB_NAME(database_id), encryption_state 
  FROM sys.dm_database_encryption_keys

Убеждаемся, что база данных теперь действительно зашифрована:

И опять запускаем тот же тест. В целях экономии места я не буду повторять листинг тестового запроса. Далее приведен график из Монитора производительности. Для наглядности я совместил результаты с выключенным и включенным TDE на одном графике:

Так как тестируемый запрос, очевидно, не может быть распараллелен, вся нагрузка, включая шифрование, ложится на один процессор (ядро). В силу этого, как видно из графика, загрузка процессора в секунду возросла не сильно (больше 100% никак). А вот длительность запроса возросла очень прилично, более чем в два раза. Это говорит о том, что на шифрование было потрачено много времени процессора. Сколько именно, определяет площадь под красной кривой. Площадь под синей кривой определяет общий объем записи на диск, сгенерированный запросом. Это значение не должно было измениться и не изменилось, так как очевидно, что на этот параметр TDE никак не влияет. Но так как длительность запроса возросла, объем записи в единицу времени заметно уменьшился (пропорционально).

Пример 2. Много читаем

Давайте рассмотрим еще один пример. В отличие от предыдущего примера, где было много записи, здесь будет большой объем чтения. Кроме того, тестовый запрос в этом примере, в отличие от того, что мы использовали ранее, может быть распараллелен.

Итак, первое, что нужно сделать, - это расшифровать нашу базу данных, которую мы зашифровали в процессе выполнения первого примера. Для этого выполняем:

-- Выключаем шифрование
ALTER DATABASE TDE_TEST_DB SET ENCRYPTION OFF
-- Ждем пока данные в базе данных будут полностью расшифрованы
WHILE(1=1) BEGIN
  WAITFOR DELAY '00:00:01'
  IF(EXISTS(SELECT * FROM sys.dm_database_encryption_keys 
    WHERE database_id = DB_ID('TDE_TEST_DB') And encryption_state = 1)) BREAK
END
-- Выводим состояние базы данных
SELECT DB_NAME(database_id), encryption_state 
  FROM sys.dm_database_encryption_keys

Убеждаемся, что наша база данных действительно расшифрована:

Несмотря на то, что зашифрованных пользовательских баз данных на сервере больше нет, tempdb осталась зашифрованной. Это не должно как-либо сказаться на результатах этого примера, так как ни явно, ни скрыто (план выполнения запроса этого не предусматривает) в tempdb мы ничего не пишем и не читаем. Но для "чистоты эксперимента" можно перезапустить SQL Server. После перезапуска tempdb уже не будет шифроваться.

Итак, выполняем тестовый запрос.

-- Сбрасываем кэш (нужно для улучшения повторяемости результатов)
CHECKPOINT
DBCC DROPCLEANBUFFERS
-- Начало тестового запроса
SELECT data FROM dbo.TestTable1 UNION
SELECT data FROM dbo.TestTable1 UNION
SELECT data FROM dbo.TestTable1

Для выполнения этого запроса используется таблица dbo.TestTable1, которая была нами создана и заполнена данными в предыдущем примере. Приведенный выше тестовый запрос даже без шифрования достаточно прилично нагружает процессор из-за неявной опции DISTINCT (вспомним, что операция UNION без ALL возвращает только уникальные строки). План выполнения запроса предусматривает распараллеливание, то есть запрос сможет задействовать оба ядра процессора, установленных в тестовом сервере.

Смотрим, какую нагрузку на сервер создал этот запрос:

Как и в первом примере, давайте сравним исходную загрузку с загрузкой после включения TDE. Включаем шифрование тестовой базы данных:

-- Включаем шифрование
ALTER DATABASE TDE_TEST_DB SET ENCRYPTION ON 
-- Ждем, пока данные в базе данных будут полностью зашифрованы
WHILE(1=1) BEGIN 
  WAITFOR DELAY '00:00:01' 
  IF(EXISTS(SELECT * 
              FROM sys.dm_database_encryption_keys 
                WHERE database_id = DB_ID('TDE_TEST_DB') 
                And encryption_state = 3)) 
              BREAK
END 
-- Выводим состояние базы данных
SELECT DB_NAME(database_id), encryption_state 
  FROM sys.dm_database_encryption_keys

Убеждаемся, что база данных теперь опять зашифрована, и повторяем тот же тест. Вот что у меня получилось:

Можно констатировать, что здесь, как и в первом примере, включение TDE привело к появленью существенной дополнительной нагрузки на процессор (площадь под красной кривой). Однако, в отличие от первого примера, за счет того, что план выполнения этого запроса допускает распараллеливание, загрузка процессора в секунду выросла очень прилично (ей было куда расти, так как в тестовой системе было установлено 2 ядра, и следовательно, потолок - 200%). За счет этого длительность запроса увеличилась не очень сильно. Как следствие объем чтения в единицу времени тоже снизился не очень сильно.

Анализ результатов

Как видно из приведенных выше примеров, TDE может создать очень значительную дополнительную нагрузку на процессор. В некоторых случаях речь вполне может идти об увеличении загрузки процессора в разы. Но нужно хорошо понимать, что увеличение загрузки процессора при выполнении запроса, например, на 10 секунд, вовсе не означает, что запрос будет выполняться на эти 10 секунд дольше. При наличии достаточного количества свободного процессорного времени чтобы "проглотить" дополнительную нагрузку, время выполнения запроса может увеличиться незначительно или даже вообще не измениться! Примерно так обстояли дела в нашем втором запросе. Отсюда берутся вполне реальные оценки в 3-28% от Microsoft, приведенные выше. Это достигается за счет того, что большинство операций в SQL Server выполняется асинхронно, и как следствие, запрос не стоит и не ждет, пока выполнится шифрование/расшифровка данных. А вот если ресурсов процессора не хватает, то время выполнения запроса может сильно увеличится. Такое положение дел мы видели в нашем первом примере.

Выводы

Основной вывод, который нужно из всего этого сделать, заключается в том, что нужно заранее планировать использование прозрачного шифрования баз данных в приложениях. Необходимо убедиться, что свободных ресурсов (в первую очередь речь идет о CPU) сервера хватит, чтобы справится с возросшими потребностями после включения TDE. В этом случае производительность приложения не пострадает (или пострадает, но совсем не сильно :) ).

Кроме того, нужно понимать, что включение шифрования не делает данные автоматически защищенными от всех напастей. Прозрачное шифрование баз данных (TDE) способно защитить данные от вполне определенного перечня угроз, основными из которых являются "утечка" файлов базы данных и "утечка" резервных копий. Стоит отметить, что такое положение вещей нельзя ставить в вину Microsoft, это связано с общими для всех проблемами применения шифрования.

• Купить программу Microsoft SQL Server 2012
• Задать вопрос ONLine по покупке программ в интернет магазине ITShop
• Подписаться на рассылку "Вопросы и ответы по MS SQL Server"