(SQL Server) Working with PEM Encrypted Private Keys

Demonstrates how to load and save PEM encrypted private keys.

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)
    -- Starting in v9.5.0.49, all Chilkat classes can be unlocked at once at the beginning of a program
    -- by calling UnlockBundle.  It requires a Bundle unlock code.
    DECLARE @chilkatGlob int
    -- Use "Chilkat_9_5_0.Global" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Global', @chilkatGlob OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @chilkatGlob, 'UnlockBundle', @success OUT, 'Anything for 30-day trial.'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @chilkatGlob, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @chilkatGlob
        RETURN
      END

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

    DECLARE @pemPassword nvarchar(4000)
    SELECT @pemPassword = 'secret'

    -- To load a PEM file containing encrypted private keys, simply
    -- provide the password.
    EXEC sp_OAMethod @pem, 'LoadPemFile', @success OUT, '/Users/chilkat/testData/pem/pemContainingEncryptedPrivateKeys.pem', @pemPassword
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @pem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @chilkatGlob
        EXEC @hr = sp_OADestroy @pem
        RETURN
      END

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

    DECLARE @pemText nvarchar(4000)
    EXEC sp_OAMethod @fac, 'ReadEntireTextFile', @pemText OUT, '/Users/chilkat/testData/pem/pemContainingEncryptedPrivateKeys.pem', @pemPassword

    -- To load a PEM from a string, call LoadPem instead of LoadPemFile:
    EXEC sp_OAMethod @pem, 'LoadPem', @success OUT, @pemText
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @pem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @chilkatGlob
        EXEC @hr = sp_OADestroy @pem
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END

    -- A few notes:
    -- The PEM may contain both private keys and certificates (or anything else).
    -- The password is utilized for whatever content in the PEM is encrypted.  
    -- It is OK to have both encrypted and non-encrypted content within a given PEM.

    -- PEM private keys can be encrypted in different formats.  The LoadPem and LoadPemFile
    -- methods automatically handle the different formats.
    -- One format is PKCS8 and is indicated by this delimiter within the PEM:

    -- -----BEGIN ENCRYPTED PRIVATE KEY-----
    -- MIICoTAbBgkqhkiG9w0BBQMwDgQIfdD0zv24lgkCAggABIICgE0PdHJmRbNs6cBX
    -- ...

    -- Another format, we'll call "passphrase" looks like this in the PEM:
    -- -----BEGIN RSA PRIVATE KEY-----
    -- Proc-Type: 4,ENCRYPTED
    -- DEK-Info: DES-EDE3-CBC,A4215544D11C5D0C
    -- 
    -- paqy9XRexcSjurHfG0xhCaUD0HrvIdhuC0CbRxxxeMlkLaV6+uT80rBxt2AaibWG
    -- ...

    -- Show the bit length of each private key:
    DECLARE @i int

    DECLARE @numPrivateKeys int
    EXEC sp_OAGetProperty @pem, 'NumPrivateKeys', @numPrivateKeys OUT
    IF @numPrivateKeys = 0
      BEGIN

        PRINT ('Error: Expected the PEM to contain private keys.')
        EXEC @hr = sp_OADestroy @chilkatGlob
        EXEC @hr = sp_OADestroy @pem
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END
    SELECT @i = 1
    WHILE @i <= @numPrivateKeys
      BEGIN
        DECLARE @privKey int
        EXEC sp_OAMethod @pem, 'GetPrivateKey', @privKey OUT, @i - 1

        EXEC sp_OAGetProperty @privKey, 'BitLength', @iTmp0 OUT

        PRINT @i + ': ' + @iTmp0 + ' bits'
        EXEC @hr = sp_OADestroy @privKey

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @chilkatGlob
    EXEC @hr = sp_OADestroy @pem
    EXEC @hr = sp_OADestroy @fac


END
GO