(SQL Server) Export a Private Key from an MS Storage Provider

Demonstrates how to export a private key from a Microsoft Storage Provider.

Note: This example requires Chilkat v9.5.0.83 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires Chilkat v9.5.0.83 or greater.

    -- We'll export a certificate's private key from the MS storage provider.
    -- It is assumed the certificate + private key is already installed on the Windows system.
    -- The export does not remove the key from the Windows storage provider.

    -- First, let's get a certificate in one of the many ways we can do it.
    -- (I ran certmgr.msc, opened a certificate, and noted it's thumbprint.)
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadByThumbprint', @success OUT, 'ea5a129c1919a52d238ee28d9f3a8f345b768388', 'hex'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
    PRINT 'Found: ' + @sTmp0

    -- First export the private key the easy way.
    DECLARE @privKey int
    EXEC sp_OAMethod @cert, 'ExportPrivateKey', @privKey OUT
    EXEC sp_OAGetProperty @cert, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- OK.. we have the private key.  Do whatever we want with it..

    EXEC @hr = sp_OADestroy @privKey

    -- -------------------------------------------------------------
    -- Now let's export in a more roundabout way by getting information about the 
    -- storage provider and key name and then we'll export completely independent
    -- of the certificate.
    -- 
    -- Remember: The private key is not contained within the certificate.  An X.509 certificate
    -- embeds the public key, but the counterpart private key is stored elsewhere, such
    -- as in a .pfx/.p12, or as in this case, in the Windows "protected store", or perhaps on
    -- a smartcard or hardware token.  (But a private key stored on a smartcard or token cannot
    -- be exported.  It must remain on the hardware.)
    -- 

    DECLARE @storageProvider nvarchar(4000)
    EXEC sp_OAGetProperty @cert, 'CspName', @storageProvider OUT
    DECLARE @keyName nvarchar(4000)
    EXEC sp_OAGetProperty @cert, 'KeyContainerName', @keyName OUT


    PRINT 'Information about the certificate''s private key:'

    PRINT 'Storage Provider: ' + @storageProvider

    PRINT 'Key Name: ' + @keyName

    -- Export using just the name of the storage provider and key.
    DECLARE @keyCon int
    -- Use "Chilkat_9_5_0.KeyContainer" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.KeyContainer', @keyCon OUT

    DECLARE @privKey2 int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey2 OUT

    DECLARE @silentFlag int
    SELECT @silentFlag = 0
    EXEC sp_OAMethod @keyCon, 'ExportKey', @success OUT, @keyName, @storageProvider, @silentFlag, @privKey2
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @keyCon, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @keyCon
        EXEC @hr = sp_OADestroy @privKey2
        RETURN
      END

    -- OK.. we have the private key in privKey2.  Do whatever we want with it..
    -- Perhaps we save as encrypted PKCS8 PEM.
    EXEC sp_OAMethod @privKey2, 'SavePkcs8EncryptedPemFile', @success OUT, 'myPassword', 'qa_output/privKey2.pem'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @keyCon
        EXEC @hr = sp_OADestroy @privKey2
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @keyCon
    EXEC @hr = sp_OADestroy @privKey2


END
GO