CERTPRIVATEKEY (Transact-SQL)

Article
11/19/2024

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

This function returns the private key of a certificate in binary format. This function takes three arguments.

A certificate ID.
An encryption password, used to encrypt the private key bits returned by the function. This approach does not expose the keys as clear text to users.
An optional decryption password. A specified decryption password is used to decrypt the private key of the certificate. Otherwise, the database master key is used.

Only users with access to the certificate private key can use this function. This function returns the private key in PVK format.

Syntax

CERTPRIVATEKEY   
    (  
          cert_ID   
        , ' encryption_password '   
      [ , ' decryption_password ' ]  
    )

Arguments

certificate_ID
The certificate_id of the certificate. Obtain this value from sys.certificates or from the CERT_ID (Transact-SQL) function. cert_id has data type int.

encryption_password
The password used to encrypt the returned binary value.

decryption_password
The password used to decrypt the returned binary value.

Return types

varbinary

Remarks

Use CERTENCODED and CERTPRIVATEKEY together to return different portions of a certificate, in binary form.

Permissions

CERTPRIVATEKEY is publicly available.

Examples

CREATE DATABASE TEST1;  
GO  
USE TEST1  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Use 5tr0ng P^55Words'  
GO  
CREATE CERTIFICATE Shipping04   
WITH SUBJECT = 'Sammamish Shipping Records',   
EXPIRY_DATE = '20401031';  
GO  
SELECT CERTPRIVATEKEY(CERT_ID('Shipping04'), 'jklalkaa/; uia3dd');

See CERTENCODED (Transact-SQL), Example B, for a more complex example that uses CERTPRIVATEKEY and CERTENCODED to copy a certificate to another database.

Share via