Шифрование с помощью кластера AlwaysOn

У меня есть база данных, которая была перенесена из старого экземпляра SQL Server 2008R2 и в кластер AlwaysOn SQL Server 2012. В базе данных есть несколько полей, которые зашифровываются с использованием встроенных функций шифрования SQL Server (главный ключ, сертификат, симметричный ключ.)

Я выполнил следующие команды в экземпляре QA AO (те же шаги, которые выполнялись на старом сервере):

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

 CREATE CERTIFICATE myCert  
    WITH SUBJECT = 'password'

 CREATE SYMMETRIC KEY myKeyName    
    WITH ALGORITHM = TRIPLE_DES 
    ENCRYPTION BY CERTIFICATE myCert

Кроме того, мне пришлось запускать следующие команды для правильного дешифрования данных:

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Когда я запускаю эту команду, я вижу все дешифрованные данные:

OPEN SYMMETRIC KEY myKeyName
DECRYPTION BY CERTIFICATE myCert 
select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from  users
CLOSE SYMMETRIC KEY myKeyName

Пока все хорошо. Однако, если я запустил те же самые шаги на моем производственном кластере AO, этот запрос:

select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from  users

возвращает NULL для пароля. Чтобы сделать это немного более сумасшедшим, это утверждение (выполняемое в контексте среды QA) дешифрует все из обеих баз данных просто:

 OPEN SYMMETRIC KEY myKeyName
 DECRYPTION BY CERTIFICATE myCert 

 SELECT TOP 1000 
    userid, 
    CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) 
 FROM users

 SELECT TOP 1000 
    userid, 
    CONVERT(nVARCHAR(255),DECRYPTBYKEY(password))  
 FROM PRODUCTIONAO.prod_database.dbo.users

 CLOSE SYMMETRIC KEY myKeyName

Я не уверен, почему это будет работать на моем экземпляре QA, но не на моем экземпляре. Любая помощь будет принята с благодарностью!

Ответ 1

Причина, по которой работает ваш последний запрос, связана с тем, что вы используете ключ экземпляра QA/cert для дешифрования производственных данных. В QA вы можете автоматически расшифровать сертификат с помощью главного ключа базы данных (DMK), поскольку он зашифрован главным ключом службы QA (SMK) следующим образом:

Service Master Key (QA)
  Database Master Key (QA)
    Certificate (QA)
      Symmetric Key (QA)
        Data (Prod)

В prod у вас есть другой SMK, поэтому единственный способ открыть DMK - это использовать пароль. Кажется, что вы запускали следующее в среде QA, но не в prod:

/* Add service master key encryption to the database master key */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Попробуйте это в prod:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
OPEN SYMMETRIC KEY myKeyName
DECRYPTION BY CERTIFICATE myCert 
select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from users
CLOSE SYMMETRIC KEY myKeyName

Если это возвращает данные, вам нужно добавить шифрование SMK к вашему DMK в процессе производства (первый script). Другой вариант - резервное копирование SMK из исходного экземпляра и восстановление его на вторичном. Я бы рекомендовал это только в случаях использования HA, где экземпляры являются партнерами по отказу и оба находятся в одной среде. Совместное использование SMK между QA и prod - это плохая практика.

Ответ 2

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

шаги/код:

открыть дешифрование главного ключа паролем = 'WriteYouOriginalPasswordHere'

изменить главный ключ добавить шифрование с помощью служебного ключа

закрыть главный ключ