CERTENCODED (Transact-SQL)

Статья
03/07/2014

Возвращает открытую часть сертификата в двоичном формате. Эта функция принимает идентификатор сертификата и возвращает закодированный сертификат. Двоичный результат можно передавать в инструкции CREATE CERTIFICATE … WITH BINARY, чтобы создать новый сертификат.

Применимо для следующих объектов: SQL Server (начиная с SQL Server 2012 до текущей версии).

Синтаксис

CERTENCODED ( cert_id )

Аргументы

cert_id
Это идентификатор certificate_id сертификата. Его можно получить из sys.certificates или через вызов функции CERT_ID (Transact-SQL). Значение cert_id имеет тип int

Типы возвращаемых данных

varbinary

Замечания

Функции CERTENCODED и CERTPRIVATEKEY используются совместно для возврата различных составляющих сертификата в двоичной форме.

Разрешения

Функция CERTENCODED доступна для роли public.

Примеры

Простой пример

В следующем примере создается сертификат под именем Shipping04, а затем функция CERTENCODED используется для возврата сертификата в двоичной кодировке.

CREATE DATABASE TEST1;
GO
USE TEST1
CREATE CERTIFICATE Shipping04 
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Sammamish Shipping Records', 
EXPIRY_DATE = '20161031';
GO
SELECT CERTENCODED(CERT_ID('Shipping04'));

Б.Копирует сертификат в другую базу данных.

В следующем, более сложном примере создается две базы данных: SOURCE_DB и TARGET_DB. Необходимо создать сертификат в SOURCE_DB, скопировать его в TARGET_DB и показать, что данные, зашифрованные в SOURCE_DB, можно расшифровать в TARGET_DB с помощью копии сертификата.

Чтобы создать среду для примера, создайте базы данных SOURCE_DB и TARGET_DB, а также главный ключ в каждой из них. Затем создайте сертификат в SOURCE_DB.

USE master;
GO
CREATE DATABASE SOURCE_DB;
GO
USE SOURCE_DB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0URCE_DB KEY Pa$$W0rd';
GO
CREATE DATABASE TARGET_DB;
GO
USE TARGET_DB
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$W0rd in TARGET_DB';
GO

-- Create a certificate in SOURCE_DB
USE SOURCE_DB;
GO
CREATE CERTIFICATE SOURCE_CERT WITH SUBJECT = 'SOURCE_CERTIFICATE';
GO

Теперь извлеките двоичное описание сертификата.

DECLARE @CERTENC VARBINARY(MAX);
DECLARE @CERTPVK VARBINARY(MAX);
SELECT @CERTENC = CERTENCODED(CERT_ID('SOURCE_CERT'));
SELECT @CERTPVK = CERTPRIVATEKEY(CERT_ID('SOURCE_CERT'),
       'CertEncryptionPa$$word');
SELECT @CERTENC AS BinaryCertificate;
SELECT @CERTPVK AS EncryptedBinaryCertificate;
GO

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

-- Create the duplicate certificate in the TARGET_DB database
USE TARGET_DB
GO
CREATE CERTIFICATE TARGET_CERT
FROM BINARY = <insert the binary value of the @CERTENC variable>
WITH PRIVATE KEY (
BINARY = <insert the binary value of the @CERTPVK variable>
, DECRYPTION BY PASSWORD = 'CertEncryptionPa$$word');
-- Compare the certificates in the two databases
-- The two certificates should be the same 
-- except for name and (possibly) the certificate_id
SELECT * FROM SOURCE_DB.sys.certificates
UNION
SELECT * FROM TARGET_DB.sys.certificates;

Следующий код, выполняемый как единый пакет, показывает, что зашифрованные в SOURCE_DB данные можно расшифровать в TARGET_DB.

USE SOURCE_DB;

DECLARE @CLEARTEXT nvarchar(100);
DECLARE @CIPHERTEXT varbinary(8000);
DECLARE @UNCIPHEREDTEXT_Source nvarchar(100);
SET @CLEARTEXT = N'Hello World';
SET @CIPHERTEXT = ENCRYPTBYCERT(CERT_ID('SOURCE_CERT'), @CLEARTEXT);
SET @UNCIPHEREDTEXT_Source = 
    DECRYPTBYCERT(CERT_ID('SOURCE_CERT'), @CIPHERTEXT)
-- Encryption and decryption result in SOURCE_DB
SELECT @CLEARTEXT AS SourceClearText, @CIPHERTEXT AS SourceCipherText, 
       @UNCIPHEREDTEXT_Source AS SourceDecryptedText;

-- SWITCH DATABASE
USE TARGET_DB;

DECLARE @UNCIPHEREDTEXT_Target nvarchar(100);
SET @UNCIPHEREDTEXT_Target = DECRYPTBYCERT(CERT_ID('TARGET_CERT'), @CIPHERTEXT);
-- Encryption and decryption result in TARGET_DB
SELECT @CLEARTEXT AS ClearTextInTarget, @CIPHERTEXT AS CipherTextInTarget, @UNCIPHEREDTEXT_Target AS DecriptedTextInTarget; 
GO