CERTENCODED (Transact-SQL)

Artículo
10/16/2024

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Esta función devuelve la parte pública de un certificado en formato binario. Esta función toma un identificador de certificado como argumento y devuelve el certificado codificado. Para crear un certificado nuevo, pase el resultado binario a CREATE CERTIFICATE ... WITH BINARY.

Sintaxis

CERTENCODED ( cert_id )

Argumentos

cert_id
certificate_id del certificado. Busque este valor en sys.certificates; la función CERT_ID (Transact-SQL) también lo devolverá. cert_id tiene el tipo de datos int.

Tipos de valores devueltos

varbinary

Observaciones

Use CERTENCODED y CERTPRIVATEKEY juntos para devolver diferentes partes de un certificado en formato binario.

Permisos

CERTENCODED está disponible públicamente.

Ejemplos

Ejemplo sencillo

En este ejemplo se crea un certificado denominado Shipping04 y se usa la función CERTENCODED para devolver la codificación binaria del certificado. Este ejemplo establece la fecha de expiración del certificado en 31 de octubre de 2040.

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

B. Copiar un certificado a otra base de datos

En el ejemplo más complejo se crean dos bases de datos, SOURCE_DB y TARGET_DB. Luego se crea un certificado en SOURCE_DB y se copia en TARGET_DB. Por último, se muestra que los datos cifrados en SOURCE_DB se pueden descifrar en TARGET_DB con la copia del certificado.

Para crear el entorno de ejemplo, cree las bases de datos SOURCE_DB y TARGET_DB y una clave maestra en cada base de datos. Luego, cree un certificado en 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

Después, extraiga la descripción binaria del certificado.

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

Luego, cree el certificado duplicado en la base de datos TARGET_DB. Modifique el código siguiente para que funcione. Para ello, inserte los dos valores binarios (@CERTENC y @CERTPVK) devueltos en el paso anterior. No especifique estos valores entre comillas.

-- 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;

En este código, que se ejecuta como un solo lote, se muestra que TARGET_DB puede descifrar datos que se cifraron en SOURCE_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

Vea también

Funciones de seguridad (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
CERTPRIVATEKEY (Transact-SQL)
sys.certificates (Transact-SQL)

Compartir a través de