Notes on SQL

In a previous article I described how to use a certificate to sign a stored procedure. Part of that article showed how to copy the certificate between databases by using a backup/restore method. This article shows another method – scripting.

Background

While working out how to move a certificate around several servers (within a High-Availability Group) I decided that using the backup/restore method was not suitable. It requires a shared resource across all of the servers and scripting those actions was problematic, as different environments might have different shared folders (or none).
With SQL Server 2012 Microsoft introduced two functions for scripting certificates and their keys – CERTENCODED and CERTPRIVATEKEY.

Test Environment

To set up the initial test environment I have simply replicated some of the steps within my previous article on certificates, to create the same test environment.

Listing 1: Create Initial Test Environment

CREATE DATABASE CertificateTest;
GO
 
USE CertificateTest;
GO
 
CREATE PROCEDURE [dbo].[GetReplicaDetailsCount]
(@CountHAGroups INT OUTPUT)
AS
BEGIN
    SET @CountHAGroups = 
        (
            SELECT COUNT(*) FROM
            (   
                SELECT
                AG.name
                FROM
                master.sys.availability_groups AS AG
                LEFT JOIN master.sys.dm_hadr_availability_group_states as agstates
                ON AG.group_id = agstates.group_id
 
            ) AS AGCOUNT
        )
END;
GO
 
USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'TestUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
 
USE [CertificateTest]
GO
 
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
 
GRANT EXECUTE ON [dbo].[GetReplicaDetailsCount] TO [TestUser]
GO

--Create the certificate
CREATE CERTIFICATE [ServerViewCertificate]
ENCRYPTION BY PASSWORD = 'SK@(67Hn00T$%GG'
WITH SUBJECT = 'Certificate to view server state';
GO

Script the Certificate

At this point we differ from the previous article, as we won’t be using BACKUP CERTIFICATE.
There are two functions available for scripting certificate. CERTENCODED returns a binary value for a specified certificate, CERTPRIVATEKEY returns a binary value for the private key of a certificate.

Listing 2: Using CERTENCODED to get a binary certificate value

USE CertificateTest;
GO

SELECT  CERTENCODED(CERT_ID('ServerViewCertificate')); 

Figure 1: Result from Listing 2

The binary value returned from this query can be used to create the certificate. So in this example the certificate in the master database will be created using this, instead of using a file (as shown in the previous article).

Listing 3: Creating the certificate from a binary value

USE master;
GO

CREATE CERTIFICATE [ServerViewCertificate]
      FROM BINARY = 0x308201D930820142A003020102021070CFE49CF2AFC6A3414FC2169E050D5E300D06092A864886F70D0101050500302B3129302706035504031320436572746966696361746520746F207669657720736572766572207374617465301E170D3138303532313135343031345A170D3139303532313135343031345A302B3129302706035504031320436572746966696361746520746F20766965772073657276657220737461746530819F300D06092A864886F70D010101050003818D0030818902818100B679C8B6133CA945D63A22FDFE006AF8DCA39857ACE5AC0CA8D6B55B55A8DD7B36D14628CF5E31FE7E916168F965870E8286E561ACD9150B42C913B74267B245DFFFEA4B50B74CFABCCF3C13844924211580A6F08BD85AA29CD547DEB47EBCFCD2F03E1CEAE068341D9C27861BF051393A40B55511B2AED89C59DAEF4DB3BC650203010001300D06092A864886F70D0101050500038181009FEF48251FFC8FFBA36E164CC88EB162A291421645ACEF13276151983AE3D53FE4B12127FAB8C6DF8D880F2A3F07E55A8B02BE0E62E9E645F1512EE58BBD8572179E8A62F56D635C7DF472F76D31545DEBB635C586B324A046104E33F916F5C16872ADE795C7193C27B120451B223B1A051CE6C874E8521235FB564971A2DA68;
GO

Figure 2: Result from Listing 3

Checking the thumbprints from the two copies of the certificate on this server, shows that they are identical:

Listing 4: Check the thumbprints

SELECT
    name,
    thumbprint
FROM master.sys.certificates
WHERE
    name = N'ServerViewCertificate'
UNION ALL
SELECT
    name,
    thumbprint
FROM CertificateTest.sys.certificates
WHERE
    name = N'ServerViewCertificate';

Figure 3: Result from Listing 4

As an aside, if I now change the code in listing 2 to have another name for the certificate it will not create it:

Listing 5: Attempting to Duplicate a Certificate

USE master;
GO

CREATE CERTIFICATE [AnotherServerViewCertificate]
      FROM BINARY = 0x308201D930820142A003020102021070CFE49CF2AFC6A3414FC2169E050D5E300D06092A864886F70D0101050500302B3129302706035504031320436572746966696361746520746F207669657720736572766572207374617465301E170D3138303532313135343031345A170D3139303532313135343031345A302B3129302706035504031320436572746966696361746520746F20766965772073657276657220737461746530819F300D06092A864886F70D010101050003818D0030818902818100B679C8B6133CA945D63A22FDFE006AF8DCA39857ACE5AC0CA8D6B55B55A8DD7B36D14628CF5E31FE7E916168F965870E8286E561ACD9150B42C913B74267B245DFFFEA4B50B74CFABCCF3C13844924211580A6F08BD85AA29CD547DEB47EBCFCD2F03E1CEAE068341D9C27861BF051393A40B55511B2AED89C59DAEF4DB3BC650203010001300D06092A864886F70D0101050500038181009FEF48251FFC8FFBA36E164CC88EB162A291421645ACEF13276151983AE3D53FE4B12127FAB8C6DF8D880F2A3F07E55A8B02BE0E62E9E645F1512EE58BBD8572179E8A62F56D635C7DF472F76D31545DEBB635C586B324A046104E33F916F5C16872ADE795C7193C27B120451B223B1A051CE6C874E8521235FB564971A2DA68;
GO

The response from the server is “A certificate with name ‘AnotherServerViewCertificate’ already exists or this certificate already has been added to the database“. Although a certificate with this new name doesn’t already exist, a certificate matching the binary value does exist, meaning that you can’t circumvent the certificate security by attempting to create another based upon the original certificate.

Using CERTENCODED in isolation is not necessarily enough to save the entire certificate – it does not include the private key. If I attempted to sign a procedure, using a certificate created via CERTENCODED I would see get the error message “Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.”

To script the private key there is another function – CERTPRIVATEKEY. There are three parameters to this function, the identity of the certificate, an encryption key – used to encrypt the value returned from this function and a decryption key – the key that was used to initially encrypt this certificate. If the third parameter isn’t supplied then the database master key is used.

The certificate used in the examples in this article has a key of ‘SK@(67Hn00T$%GG’ and the following listing shows the results from CERTPRIVATEKEY if this value is not supplied.

Listing 6: CERTPRIVATEKEY examples

Use CertificateTest;
GO

--Run 1: Different Encryption value and correct Decryption value

select CERTPRIVATEKEY(CERT_ID('ServerViewCertificate'),
		      'A@%12345678',
		      'SK@(67Hn00T$%GG') as [Run 1]; 

--Run 2: Decryption value used for both parameters

select CERTPRIVATEKEY(CERT_ID('ServerViewCertificate'),
		      'SK@(67Hn00T$%GG',
		      'SK@(67Hn00T$%GG') as [Run 2]; 

--Run 3: Different Encryption Decryption value

select CERTPRIVATEKEY(CERT_ID('ServerViewCertificate'),
		      'A@%12345678',
		      'A@%12345678') as [Run 3]; 

Figure 4: Results from Listing 6

Notice that the first two results return different values, because the second parameter has specified a different encryption key. If the third parameter (the decryption key) is not correct then a NULL value is returned, instead of an error message.

Scripting the Certificate to Another Database

So, having looked t the two functions for scripting the certificate and the private key, we can create another test database as the target, with a stored procedure that we need to sign.

Listing 7: Additional Test Database

CREATE DATABASE CopyCertificate;
GO

USE CopyCertificate;
GO

CREATE PROCEDURE [dbo].[CopyGetReplicaDetailsCount]
(@CountHAGroups INT OUTPUT)
WITH EXECUTE AS OWNER
AS
BEGIN
 
    SET @CountHAGroups = 
        (
            SELECT COUNT(*) FROM
 
            (   
                SELECT
                AG.name
                FROM
                master.sys.availability_groups AS AG
                LEFT JOIN master.sys.dm_hadr_availability_group_states as agstates
                ON AG.group_id = agstates.group_id
 
            ) AS AGCOUNT
        )
 
END;

Now extract the binary values for the certificate and the private key from the database ‘CertificateTest’.

Listing 8: Extract Binary Values for the Certificate

USE CertificateTest;
GO

SELECT CERTENCODED(CERT_ID('ServerViewCertificate')) AS [Certificate Value];

SELECT CERTPRIVATEKEY(CERT_ID('ServerViewCertificate'),
		      'A@%12345678',
		      'SK@(67Hn00T$%GG') AS [Private Key];

Now switch to the new database and create the certificate from the binary values of Listing 8. Notice that the decrypt and encrypt values match the values used within the CERTPRIVATEKEY call, in Listing 8.

Listing 9: Create the Certificate from Binary Values

USE CopyCertificate;
GO

CREATE CERTIFICATE ServerStateCertificate  
FROM BINARY = 0x308201D930820142A00302010202101DE82FB5D15B8ABF43641AAE349F2247300D06092A864886F70D0101050500302B3129302706035504031320436572746966696361746520746F207669657720736572766572207374617465301E170D3138303532393134303231305A170D3139303532393134303231305A302B3129302706035504031320436572746966696361746520746F20766965772073657276657220737461746530819F300D06092A864886F70D010101050003818D0030818902818100CD7D8928A84750C066DA9D91D8A10EF39E7E5978D8600A8602FEDA76F0124C425FBCE1F55ABFD954A57A85B261DC9F5CC888C32E8F201DC0001B340C36DDDE7639DCE0A82D6C52DDBA61147F260E91B4A33C0CCE8372233A0F65B0F9496EC4AF1784B3058B9ABCD7D2A9D0810B9423C0103C5831A075CB50BFAF93DB88FA09A50203010001300D06092A864886F70D010105050003818100AF2394AF3DB36656B5CA9871F68782F057C32ED5B159A208C231160A2906DA4D8190383B8713187449946785A46350F72F221A8788B84106EA20D658952E1DDF7ED25159E426BD43F98294B73543615EAB35366B4A558C5DA482BD3FB077ED8F1444AAD24EFBD7F1B68D5B3DBEE16D8D93043D30B62860C759BF07500616D8D4
WITH PRIVATE KEY( 
BINARY= 0x1EF1B5B0000000000100000001000000100000005402000055F692EFAE12E05C83F3694B2B4D3B390702000000A400006B466B1284A96A45050AF5CFD27BE6A485A970287AF918E368293D534E5745205EA82B94948AD91E296AF47E94B3EC5976F31DFE09F6718B484E8E28BA25AFCA5B5742351E5636ADF2B1D623C5DCAA3B93FDBD5644AC487366E5A93700D070C2C20CD16F59C17B96BF55C2E16F23B638642A1B83BFD91623D7EA57C3AD3190EE70B877352D2952E1F14BA27F8051D2092462DBA4162E92D7AED72E9FFB02CC0E6C768D17ACBF2A3C2E1423F885923FE4B601FD86B10C022881B46C710A2C1DDF195F90B9442F5419A10D73E7E3058B485FF1BEAD1C4438593C729E8B90A14C36115A7108F978EA21E6654BBD2639CF1E765DDF4BD885A40B8C990F31B00BEAAC4EB80878A99F7950BD09C64E83461720B97C2DA81EDB815D38A0077EE90968E475C25D312475D6A14306627FEE38737925E2EF6297E83CD50190812E058C0EF68AF46FCD00FCBB03404F9C3304AFEE053754682B0EB37C55668B95CE5D85864EBD6AFC8BC947BF760ABA92A2CFC83B81F6D20DB12511A3E11C777FB74EAF249DEABE4B2BE78CD31135046105E1DDA29BD52C1574418EB645907324A2BD345C04AB8FD372FCB54E1F9863D10694CDEF8CA40FFD115DC2687A8CFA5EFB3CE633104EC247D99A455C9D10EA40EB1305D17C7B99A21EA1FE584150989C7E79E78C7ADD1F917C4F708DDFF622A883D45C7D43795CB43A2D04BF580D30897931399E3151737EE0FCA4B75479701887552841022E9253943BFB4F559AFFBDDAE5FF33962507634802DDE7D9B0A0F64610CB8F5BA46417C46DA2BC9D2A5D9E69DF682ABC3AFAA8F66AED8CB02F733825
, DECRYPTION BY PASSWORD = 'A@%12345678', 
ENCRYPTION BY PASSWORD = 'SK@(67Hn00T$%GG');

Checking the thumbprint of the certificate within the two databases (‘CertificateTest’ and ‘CopyCertificate’) shows that they have the same value. The certificate has been created from binary values in a script.

List 10: Checking the Thumbprints

SELECT
    name,
    thumbprint
FROM CopyCertificate.sys.certificates
WHERE
    name = N'ServerViewCertificate'
UNION ALL
SELECT
    name,
    thumbprint
FROM CertificateTest.sys.certificates
WHERE
    name = N'ServerViewCertificate';

Figure 5: Result from Listing 10

Conclusion

Although BACKUP CERTIFICATE does allow a certificate to be duplicated to another database, by using CERTENCODED and CERTPRIVATEKEY it is possible to write a script that can copy a certificate without having a physical file. This also means that the binary values could be stored in variables and dynamic SQL could be used to automate the process – quite useful when creating a certificate within a High Availability Group.

References
CERTENCODED – Microsoft Docs
CERTPRIVATEKEY – Microsoft Docs