SQL Server

Working with PEM Encrypted Private Keys

See more PEM Examples

Demonstrates how to load and save PEM encrypted private keys. SQL Server

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    SELECT @success = 0

    DECLARE @pem int
    EXEC @hr = sp_OACreate 'Chilkat.Pem', @pem OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    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 = 0
      BEGIN
        EXEC sp_OAGetProperty @pem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pem
        RETURN
      END

    DECLARE @fac int
    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 = 0
      BEGIN
        EXEC sp_OAGetProperty @pem, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        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 @pem
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END
    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    SELECT @i = 1
    WHILE @i <= @numPrivateKeys
      BEGIN
        EXEC sp_OAMethod @pem, 'PrivateKeyAt', @success OUT, @i - 1, @privKey

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

        PRINT @i + ': ' + @iTmp0 + ' bits'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @pem
    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @privKey


END
GO