Can a Certificate’s Private Key be Imported / Restored From a Binary Literal / Hex Bytes ?

(last updated: 2019-05-23 @ 19:15 EST / 2019-05-23 @ 23:15 UTC )

SQL Server 2012 introduced a significant improvement to CREATE CERTIFICATE: the ability to import a certificate — the public key and meta data — and optionally also the private key, from a VARBINARY literal (sometimes referred to as “hex bytes”). Prior versions only allowed importing from an assembly (public key only; no private key), or from an external file (public key, and optionally also the private key).

There are some problems with only allowing the import of certificates from assemblies and external files:

1. It complicates (even if only slightly) the process of copying a certificate between databases, in which case one needed to use BACKUP CERTIFICATE to save it to a file (or two files if also copying the private key) to then import in the other database using CREATE CERTIFICATE.
    
2. It’s much harder to create self-contained deployment scripts when you can’t specify the certificate data inline, and instead need to rely upon an external file. That could complicate a rollout process if the external resource isn’t available (whether missing, or renamed, or in a different folder, or a permissions issue, etc).
    
   Of course, an assembly (marked as PERMISSION_SET = SAFE) could be created from a VARBINARY literal, and then a certificate could be created from that assembly. This is the preferred approach for SQL Server 2005, 2008, and 2008 R2. HOWEVER, …
    
3. You don’t always have an assembly to use as a vehicle for loading a certificate (or asymmetric key, which an assembly can also contain) because you generally only have an assembly when working with SQLCLR.
    
4. And, starting in SQL Server 2017, loading an assembly first, as the container of the certificate and/or asymmetric key (because only assemblies could be created from a VARBINARY literal), was no longer an option due to the new “clr strict security” server-level configuration setting. The issue with this new setting is that it requires all assemblies, regardless of actual PERMISSION_SET being used, to meet the requirements of loading, or executing code from, UNSAFE assemblies.
    
   Remember, the goal here is to keep TRUSTWORTHY set to OFF. Loading an assembly with no prior security setup (i.e. without loading the certificate or asymmetric key first) worked prior to SQL Server 2017, as long as the assembly was marked as being SAFE. But now we can’t load either the certificate or the assembly first? Well, that sucks.

More accurately, that would have been quite bad had it not been for SQL Server 2012’s improvement to CREATE CERTIFICATE (i.e. allowing you to supply the certificate from a VARBINARY literal). And, to support getting the certificate data of certificates (especially helpful for certificates created in SQL Server), functions were added to return the VARBINARY literal of the certificate’s public key and meta-data, CERTENCODED, and it’s private key, CERTPRIVATEKEY.

Great. Wonderful. Being able to create the certificate from a VARBINARY literal actually allows us to maintain our “No TRUSTWORTHY” goal in SQL Server 2017 and beyond when working with self-contained deployment scripts ¹ (please see SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1 and SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2). Self-contained deployment scripts are easier to version, are more reliable, and are more portable.

Unfortunately, everything’s not awesome:

The Problems

As great as this new capability is, there are a few issues:

1. The documentation for CREATE CERTIFICATE is a little confusing:
   1. some info appears to be missing
   2. some info is likely misleading
2. The documentation for ALTER CERTIFICATE indicates that it was not updated with this new capability (i.e. to specify the private key inline) 😿. While restoring a certificate’s private key is certainly not a frequently used feature, it still would’ve been nice to not be forced to use an external file for those times that you do need to restore a private key. Not horrible, but also not awesome.

Let’s look at those two issues in the CREATE CERTIFICATE documentation, and doing so might just lead us somewhere unexpected.

 

CREATE CERTIFICATE

The documentation, as of 2019-04-17, states:

WITH PRIVATE KEY
 … This clause is only valid when the certificate is being created from a file. To load the private key of an assembly, use ALTER CERTIFICATE.

and:

Remarks
 
 …CREATE CERTIFICATE can load a certificate from a file or assembly…

There are three sentences here (two in the first quote, and one in the second quote) and we will look at them in the order that they appear above:

1. The WITH PRIVATE KEY syntax is only available when creating certificates from a file?

   The “Syntax” description in the documentation clearly shows it being optional when creating a certificate from a VARBINARY literal:

        BINARY = asn_encoded_certificate  
        [ WITH PRIVATE KEY ( <private_key_options> ) ]
       

   And, it makes sense that if you can create the certificate (public key and meta-data) from a VARBINARY literal, then you would also be able to add the private key that same way (though it does not look like this logic applies to assemblies, but we will get to that in a moment). At the same time, it wouldn’t make sense to allow importing the private key from a VARBINARY literal only when importing the public key from a file (the documentation does clearly indicate that you can import the private key by specifying the BINARY = 0x... option).

   This is clearly an oversight from when the documentation was updated for the release of SQL Server 2012.

2. Loading the private key of an assembly requires using ALTER CERTIFICATE?

   This both does, and does not, make sense. And, the reason that it does make sense that the WITH PRIVATE KEY clause is not valid when creating a certificate from an assembly, just happens to be the same reason that it does not make sense to suggest using ALTER CERTIFICATE to load the assembly’s private key: assemblies do not contain any private key that was used to sign them! Signed assemblies contain only the public key(s) used to sign them. If signed assemblies also contained the private keys, then those private keys would no longer be, um, “private”. Yes, a private key does exist for anything that has been signed, but that’s not something that’s distributed along with whatever was signed, so you typically won’t have the private key anyway.

   Even if you do have the private key used to sign the assembly, why would you load that into SQL Server in the first place? You only need the private key in SQL Server if you are going to sign other modules with it. Which, I suppose, is a thing that you could do, but I can’t think of a reason why anyone would. If you need to sign T-SQL modules, then you can easily create a certificate inside SQL Server and use that. There does not seem to be any benefit to using the same certificate that was used to sign an assembly.

3. A certificate can only be created from either a file or an assembly?

   We know, beyond any doubt, that the ability to create a certificate from a VARBINARY literal was added in SQL Server 2012. This is clearly an oversight from when the documentation was updated for that release.

“Create” Tests

Fortunately, we can confirm what is and is not allowed by attempting all combinations of sources for the certificate and private key. We will simply parse each statement to have SQL Server tell us which combinations are valid. We don’t need to have valid source values or files since we aren’t actually executing the statements, and thus aren’t creating anything.

PRINT '------------------------------
Assembly and Binary';
GO
SET PARSEONLY ON;
CREATE CERTIFICATE [TempCert] FROM ASSEMBLY [Microsoft.SqlServer.Types]
WITH PRIVATE KEY (BINARY = 0x34);
GO
SET PARSEONLY OFF;
GO


PRINT '------------------------------
Assembly and PVK File';
GO
SET PARSEONLY ON;
CREATE CERTIFICATE [TempCert] FROM ASSEMBLY [Microsoft.SqlServer.Types]
WITH PRIVATE KEY (FILE = N'C:\TEMP\SomethingSomething.DarkSide');
GO
SET PARSEONLY OFF;
GO


PRINT '------------------------------
Binary and Binary';
GO
SET PARSEONLY ON;
CREATE CERTIFICATE [TempCert] FROM BINARY = 0x12
WITH PRIVATE KEY (BINARY = 0x34);
GO
SET PARSEONLY OFF;
GO


PRINT '------------------------------
Binary and PVK File';
GO
SET PARSEONLY ON;
CREATE CERTIFICATE [TempCert] FROM BINARY = 0x12
WITH PRIVATE KEY (FILE = N'C:\TEMP\SomethingSomething.DarkSide');
GO
SET PARSEONLY OFF;
GO


PRINT '------------------------------
CER File and Binary';
GO
SET PARSEONLY ON;
CREATE CERTIFICATE [TempCert] FROM FILE = N'C:\TEMP\My.cer'
WITH PRIVATE KEY (BINARY = 0x34);
GO
SET PARSEONLY OFF;
GO


PRINT '------------------------------
CER File and PVK File';
GO
SET PARSEONLY ON;
CREATE CERTIFICATE [TempCert] FROM FILE = N'C:\TEMP\My.cer'
WITH PRIVATE KEY (FILE = N'C:\TEMP\SomethingSomething.DarkSide');
GO
SET PARSEONLY OFF;
GO


PRINT '------------------------------
DLL File and Binary';
GO
SET PARSEONLY ON;
CREATE CERTIFICATE [TempCert] FROM EXECUTABLE FILE = N'C:\TEMP\My.dll'
WITH PRIVATE KEY (BINARY = 0x34);
GO
SET PARSEONLY OFF;
GO


PRINT '------------------------------
DLL File and PVK File';
GO
SET PARSEONLY ON;
CREATE CERTIFICATE [TempCert] FROM EXECUTABLE FILE = N'C:\TEMP\My.dll'
WITH PRIVATE KEY (FILE = N'C:\TEMP\SomethingSomething.DarkSide');
GO
SET PARSEONLY OFF;
GO

The first two statements — creating from an assembly — are the only two that get an error. That error (which is the same for both statements) is:

Msg 319, Level 15, State 1, Line XXXXX
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

These results confirm that:

1. You can import a certificate from a VARBINARY literal
2. You can import a private key when creating a certificate from a VARBINARY literal
3. You cannot import a private key when creating a certificate from an assembly
4. Except when creating a certificate from an assembly, any combination of sources for the certificate (i.e. public key and meta-data) and the private key should be valid

Extra Credit

One interesting technicality implied by the test results (point #4 in particular) is that you should be able to create a certificate from a DLL file (using FROM EXECUTABLE FILE) and its private key at the same time (using either FILE = N'path_to_pvk_file' or BINARY = 0xhex_bytes). This is interesting because a DLL file and an assembly that has been loaded into SQL Server are essentially the same thing. The bytes of a DLL are not changed when loaded into SQL Server as an assembly. So, while you can’t load an assembly’s private key, you should be able to load the private if creating the certificate from the DLL file that will eventually (probably) get loaded and become that assembly.

But, we don’t need to rely on “should” be able to do something; we can test it out ourselves and then we will know for certain. In order to test this, we will need:

1. a DLL that can be imported, that will be signed by a certificate
2. the private key of the certificate used to sign the DLL

Fortunately, the test files for “SQLCLR vs. SQL Server 2017, Part 6: "Trusted Assemblies" – Whitelisted Assemblies can’t do Module Signing” include a DLL signed by a certificate that I created, so it also has the .pvk file (i.e. the private key). Perfect! Let’s try it out:

USE [tempdb];

CREATE CERTIFICATE [CertFromDLL]
  FROM EXECUTABLE FILE = N'C:\TEMP\SQL2017_NeedsModuleSigning_Signed.dll'
  WITH PRIVATE KEY (
    FILE = N'C:\TEMP\SQL2017-NeedsModuleSigning-Cert.pvk',
    DECRYPTION BY PASSWORD = 'blah',
    ENCRYPTION BY PASSWORD = 'New Password, yo!'
                   );
-- Success!

SELECT * FROM sys.certificates;

That returns the following:

 name:                    CertFromDLL
 pvt_key_encryption_type: ENCRYPTED_BY_PASSWORD
 issuer_name / subject:   US, SqlQuantumLeap.com, SQLCLR Cert
 cert_serial_number:      d8 7a e7 4f dc 0d 1f 92 4c 0b 5d af 6a 2a bf 9f
 thumbprint:              0x78EBD344A93788FA7E335726796C5FA9F4C5BD0C
 expiry_date:             2099-12-31 04:00:00.000

That worked! Of course, this scenario is mostly academic because:

1. If you are given a signed DLL, it is highly unlikely to also be given the .pvk file. There’s really no reason for someone to deliver the .pvk file along with the signed DLL, and in fact it’s less secure because it could allow you to change the DLL and re-sign it with the same private key, if you knew (or could guess) the private key’s password.
2. If you have both the signed DLL and the .pvk file, then you should be converting them into VARBINARY literals so that you will have self-contained deployment scripts with no external file references (assuming SQL Server 2012 or newer is being used). Converting binary files into their string representations (i.e. “hex bytes”) is very easy to do with “BinaryFormatter“, an open-source command-line utility I wrote. It’s easy to use for both automation (specify both input and output file paths), and integrated into File Explorer so that you can right-click on a file and have the output go directly to the clipboard in order to be pasted into a script (add shortcut to “C:\Users\account_name\AppData\Roaming\Microsoft\Windows\SendTo” folder).
3. As stated earlier, I’m not aware of any benefit to signing T-SQL modules with the same certificate that was used to sign an assembly. For signing T-SQL modules, just generate a new certificate in SQL Server (i.e. CREATE CERTIFICATE with no FROM clause).

 

ALTER CERTIFICATE

The documentation for ALTER CERTIFICATE has similar issues of missing and misleading information. As of 2019-04-17, the description at the top states:

Changes the private key used to encrypt a certificate, or adds one if none is present.

That statement does not accurately reflect either how ALTER CERTIFICATE works, or even how certificates in general work:

1. What does “Changes the private key” actually mean? The private key is mathematically related to the public key, so you can’t replace it with a different private key as there is only one private key for a particular public key / certificate. Meaning, the value of the private key cannot change.
    
   This could be referring to aspects of the private key that can be changed with this statement, such as the password used to encrypt the private key, or removing the private key entirely. It’s hard to tell what is implied by “changes” since changing the password isn’t even mentioned in either the Arguments or Remarks sections; it’s only mentioned in the Examples section, in two nearly identical examples (“A” and “B”) that were given different descriptions yet are the same thing.
    
2. “encrypt a certificate” is just wrong. Certificates (the public key and meta-data) are not encrypted. Only the private key, if it has been loaded for a certificate, is encrypted. This is why the DECRYPTION BY PASSWORD and ENCRYPTION BY PASSWORD options are part of the WITH PRIVATE KEY (...) clause (and this is handled the same way by CREATE CERTIFICATE).
    
3. What does it mean to “add one”? This makes it sound like you might be able to generate a new private key if it doesn’t currently exist for a certificate. But that isn’t possible. The whole purpose of having separate public and private keys is that one (generally) cannot be used to derive the other. While in practice you can probably derive the public key if you have the private key ² , it doesn’t work the other way around. At least in the Remarks section it’s clarified as:
   

   The private key must correspond to the public key specified by certificate_name.

   It would be clearer to say that the certificate’s private key can be imported.

 

“Alter” Tests

We can fairly easily confirm the ability to change the password used to encrypt the private key. If you specify the wrong password for any built-in function that requires the private key’s password, it will return NULL. So, let’s try changing the password of the certificate we loaded in the previous example:

-- Continuing from the previous example, we are already in [tempdb].
-- USE [tempdb];

SELECT CERTPRIVATEKEY(CERT_ID(N'CertFromDLL'), 'g', 'New Password, yo!'),
       CERTPRIVATEKEY(CERT_ID(N'CertFromDLL'), 'g', 'wrong password');
-- 0x1EF1B5B0000000...     NULL


ALTER CERTIFICATE [CertFromDLL]
  WITH PRIVATE KEY (
    DECRYPTION BY PASSWORD = 'New Password, yo!', -- current password
    ENCRYPTION BY PASSWORD = 'password, O.M.G.' -- new password
                   );


SELECT CERTPRIVATEKEY(CERT_ID(N'CertFromDLL'), 'g', 'New Password, yo!'),
       CERTPRIVATEKEY(CERT_ID(N'CertFromDLL'), 'g', 'password, O.M.G.');
-- NULL     0x1EF1B5B0000000...

As you can see, at first we were able to access the private key using the “New Password, yo!” password that we set when executing CREATE CERTIFICATE, and the “wrong password” password did not work. After changing the password, the original password no longer worked, but the new “password, O.M.G.” password does.

Extra Credit

As mentioned before, it’s disappointing that ALTER CERTIFICATE wasn’t updated to allow importing a private key from a VARBINARY literal.

But wait…

Haven’t we just seen how the documentation for CREATE CERTIFICATE wasn’t fully updated for the changes introduced in SQL Server 2012. And, we just saw that the documentation for ALTER CERTIFICATE only mentioned the password-changing capability in the examples, right? So, isn’t it possible that the ability to import a private key from a VARBINARY literal actually was added in SQL Server 2012, but the documentation, for whatever reason, simply wasn’t updated?

No harm in trying it out, right?

-- Make sure private key is loaded:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc]
FROM   sys.certificates
WHERE  [name] = N'CertFromDLL';
-- thumbprint:              0x78EBD344A93788FA7E335726796C5FA9F4C5BD0C
-- cert_serial_number:      d8 7a e7 4f dc 0d 1f 92 4c 0b 5d af 6a 2a bf 9f
-- pvt_key_encryption_type: ENCRYPTED_BY_PASSWORD


------------------------------------------------------------------------
--- !!!  HIGHLIGHT AND EXECUTE NUMBERED STEPS (1 - 5) TOGETHER  !!! ----
-------------                                              -------------

-- 1) Store private key in variable:
DECLARE @PrivateKey VARCHAR(4000) = CONVERT(VARCHAR(4000),
  CERTPRIVATEKEY(CERT_ID(N'CertFromDLL'), 'abacab', 'password, O.M.G.'),
  2);


SELECT DATALENGTH(@PrivateKey) / 2 AS [TotalPrivateKeyBytes],
       @PrivateKey AS [PrivateKey];
-- 1212    0x1EF1B5B000000000010000000100000010000000940400002...


-- 2) Remove private key:
ALTER CERTIFICATE [CertFromDLL]
  REMOVE PRIVATE KEY;


-- 3) Verify that private key has been removed:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc]
FROM   sys.certificates
WHERE  [name] = N'CertFromDLL';
-- thumbprint:              0x78EBD344A93788FA7E335726796C5FA9F4C5BD0C
-- cert_serial_number:      d8 7a e7 4f dc 0d 1f 92 4c 0b 5d af 6a 2a bf 9f
-- pvt_key_encryption_type: NO_PRIVATE_KEY


-- 4) Restore private key:
EXEC(N'
ALTER CERTIFICATE [CertFromDLL]
  WITH PRIVATE KEY (
    BINARY = 0x' + @PrivateKey + N',
    DECRYPTION BY PASSWORD = ''abacab'', -- current password
    ENCRYPTION BY PASSWORD = ''password, O.M.G.'' -- new password
                   );
');


-- 5) Verify that private key has been restored:
SELECT [thumbprint], [cert_serial_number], [pvt_key_encryption_type_desc]
FROM   sys.certificates
WHERE  [name] = N'CertFromDLL';
-- thumbprint:              0x78EBD344A93788FA7E335726796C5FA9F4C5BD0C
-- cert_serial_number:      d8 7a e7 4f dc 0d 1f 92 4c 0b 5d af 6a 2a bf 9f
-- pvt_key_encryption_type: ENCRYPTED_BY_PASSWORD

Woo hoo! There you have it: proof that the you definitely can import an existing certificate’s private key from a VARBINARY literal.

Conclusion

In going through this exercise, we’ve learned a few interesting things:

1. Yes, ALTER CERTIFICATE actually does support importing a private key from a VARBINARY literal. I tested this on SQL Server 2012 SP4, 2017 CU14, and 2019 CTP 2.4.
    
   This is interesting and good to know about because removing a private key makes the system more secure (because nothing else can be signed by that certificate), but if you need to re-sign objects or sign new objects, you will need to create a new certificate if you can’t restore the private key. This can be cumbersome if you have many signed objects and you only need to sign one or two more. While it has been (and still is) possible to save the private key to a .pvk file using BACKUP CERTIFICATE, there’s much more flexibility, and portability, in being able to restore the private key inline.
    
2. Importing certificates can use any combination of FILE ( .cer ), EXECUTABLE FILE ( .dll ), or BINARY ( 0x… ) for the public key + meta-data, and FILE ( .pvk ), or BINARY ( 0x… ) for the private key. Importing a certificate from an assembly (which only contains the public key + meta-data) does not allow for importing the private key from any source (which is fine because there’s no reason to have an assembly’s private key in SQL Server).
    
3. When in doubt, test!! Even when you are certain, test! The only drawback to testing is the time it takes to do it. And yes, time is valuable and we can’t test every little thing, but it helps to get in the habit of questioning what we think we know, or have been told, and testing more things, more often. It doesn’t matter what the documentation states. It doesn’t matter what anyone — no matter how skilled / experienced / intelligent / well-respected they are — says, nor how confidently they say it. The only true authority for how SQL Server works is SQL Server itself.
    
4. You can’t always trust the documentation. This is not to say that the folks writing the documentation are not doing their job, it’s just that:
   1. There’s a lot to document, and sometimes things get overlooked.
   2. The tech writers are not the developers, or even the product managers/owners. Meaning, the tech writers can only write what they are told that the software does. If development and/or management doesn’t inform the tech writers of new or updated functionality, then how would they know?

   Sometimes we just need to experiment, using the documentation as a starting point. This is how I discovered other undocumented options for documented functionality. For numerous examples, please see:

   • Documentation-related posts
   • Documentation corrections

   I will post others when I have time.

And, it was a wonderful decision to move Microsoft’s documentation to GitHub because now we can make improvements / corrections as we come across them.

I will submit an update for both of these pages. Then, everything will be awesome 😸

---

UPDATE 2019-04-23

I submitted PR 1972 for the updates to the two pages mentioned above. Here are some other things I noticed while making those changes:

1. Example C in CREATE CERTIFICATE had not been updated to account for the new “CLR strict security” setting that was introduced in SQL Server 2017
2. There were several formatting issues on both pages, more so on ALTER CERTIFICATE
3. A related command, BACKUP CERTIFICATE, had quite a few technical and formatting issues
4. Default file location for backing up and restoring certificates and private keys was mentioned on the page for BACKUP CERTIFICATE but not on the ALTER CERTIFICATE page.
5. Certificate-related built-in functions were not listed in the “See Also” section (or only 2 of them were listed on one of these pages)

So, I added / changed quite a bit on those three pages. I created PR 1976 for BACKUP CERTIFICATE.

I also created a simple test to prove that BACKUP CERTIFICATE and ALTER CERTIFICATE used the same file format for the private key (PVK) and the same default location. That script is on PasteBin: BACKUP CERTIFICATE documentation test.

 

UPDATE 2019-04-24

The changes for PR 1972, covering CREATE CERTIFICATE and ALTER CERTIFICATE, have been merged and are live.

 

UPDATE 2019-05-23

The changes for PR 1976, pertaining to BACKUP CERTIFICATE, have been merged and were live as of 2019-04-25.