CERTENCODED (Transact-SQL)

Article
11/19/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Base de données SQL dans Microsoft Fabric

Cette fonction retourne la partie publique d’un certificat au format binaire. Cette fonction accepte un ID de certificat comme argument et retourne le certificat encodé. Pour créer un certificat, passez le résultat binaire à CREATE CERTIFICATE ... WITH BINARY.

Syntaxe

CERTENCODED ( cert_id )

Arguments

cert_id
certificate_id du certificat. Recherchez cette valeur dans sys.certificates ; elle est également retournée par la fonction CERT_ID (Transact-SQL). cert_id a le type de données int.

Types de retour

varbinary

Notes

Les fonctions CERTENCODED et CERTPRIVATEKEY sont utilisées ensemble pour retourner les différentes parties d’un certificat sous forme binaire.

Autorisations

CERTENCODED est disponible publiquement.

Exemples

Exemple simple

Cet exemple crée un certificat nommé Shipping04 puis utilise la fonction CERTENCODED pour retourner l’encodage binaire du certificat. Cet exemple définit la date d’expiration du certificat pour le 31 octobre 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. Copie d'un certificat dans une autre base de données

L’exemple le plus complexe crée deux bases de données, SOURCE_DB et TARGET_DB. Créez ensuite un certificat dans SOURCE_DB, puis copiez-le dans TARGET_DB. Enfin, montrez que les données chiffrées dans SOURCE_DB peuvent être déchiffrées dans TARGET_DB à l’aide de la copie du certificat.

Pour créer l’exemple d’environnement, créez les bases de données SOURCE_DB et TARGET_DB, et une clé principale dans chacune d’elles. Créez ensuite un certificat dans 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

Extrayez la description binaire du certificat.

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

Créez ensuite le certificat dupliqué dans la base de données TARGET_DB. Pour que cela fonctionne, modifiez le code suivant, en insérant les deux valeurs binaires (@CERTENC et @CERTPVK) retournées à l’étape précédente. N’entourez pas ces valeurs de guillemets.

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

Ce code, exécuté en tant que lot unique, indique que TARGET_DB peut déchiffrer les données chiffrées à l’origine dans 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

Voir aussi

Fonctions de sécurité (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
CERTPRIVATEKEY (Transact-SQL)
sys.certificates (Transact-SQL)

Partager via