CERTENCODED (Transact-SQL)

發行項
12/22/2023

適用於：SQL Server Azure SQL Database Azure SQL 受控執行個體

此函式會以二進位格式傳回憑證的公開部分。此函式會採用憑證識別碼作為引數，並傳回編碼憑證。若要建立新的憑證，請將二進位結果傳遞給 CREATE CERTIFICATE ...WITH BINARY。

Syntax

CERTENCODED ( cert_id )

注意

若要檢視 SQL Server 2014 （12.x）和舊版的 Transact-SQL 語法，請參閱舊版檔。

引數

cert_id
憑證的 certificate_id。在 sys.certificates 中找到此值；CERT_ID (Transact-SQL) 函式也會傳回。 cert_id 的資料類型為 int。

傳回類型

varbinary

備註

搭配使用 CERTENCODED 和 CERTPRIVATEKEY，以透過二進位格式傳回憑證的不同部分。

權限

CERTENCODED 可以公開使用。

範例

簡單範例

此範例會建立名為 Shipping04 的憑證，然後使用 CERTENCODED 函式傳回憑證的二進位編碼。此範例將憑證到期日設定為 2040 年 10 月 31 日。

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. 將憑證複製到另一個資料庫

更複雜的範例會建立兩個資料庫 SOURCE_DB 和 TARGET_DB。接著，在 SOURCE_DB 中建立憑證，然後將憑證複製至 TARGET_DB。最後，示範可以在 TARGET_DB 中使用憑證的複本來解密 SOURCE_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 資料庫中建立重複憑證。修改下列程式碼使其運作，並插入上一個步驟中所傳回的兩個二進位值 (@CERTENC 和 @CERTPVK)。請不要使用引號括住這些值。

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

這個當成單一批次執行的程式碼示範 TARGET_DB 可以解密 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

請參閱

安全性函數 (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
CERTPRIVATEKEY (Transact-SQL)
sys.certificates (Transact-SQL)