SQL Server

Add Private Key and Certificate to a PEM

See more PEM Examples

Demonstrates how to add certificates and private keys to a PEM. 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
    -- 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.

    -- The Chilkat PEM class was introduced in v9.5.0.49.  
    -- It requires the bundle to be unlocked, as shown above.
    DECLARE @pem int
    EXEC @hr = sp_OACreate 'Chilkat.Pem', @pem OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Add the private key found in alice.key to this PEM.
    -- 
    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadAnyFormatFile', @success OUT, 'qa_data/alice.key', ''
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pem
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    -- Add it to the PEM:
    EXEC sp_OAMethod @pem, 'AddPrivateKey', @success OUT, @privKey

    -- Add the certificate found in alice.crt to this PEM.
    -- 
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/alice.crt'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pem
        EXEC @hr = sp_OADestroy @privKey
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Add it to the PEM:
    DECLARE @includeCertChain int
    SELECT @includeCertChain = 0
    EXEC sp_OAMethod @pem, 'AddCert', @success OUT, @cert, @includeCertChain

    -- Write the PEM containing the private key and certificate.
    -- The private key will be output in PKCS8 encrypted form.
    -- Certificates are never encrypted.

    -- This is the password that will be required to open and access the private key
    -- from the PEM we're about to write..
    DECLARE @password nvarchar(4000)
    SELECT @password = 'secret'
    DECLARE @extendedAttrs int
    SELECT @extendedAttrs = 0
    DECLARE @noKeys int
    SELECT @noKeys = 0
    DECLARE @noCerts int
    SELECT @noCerts = 0
    DECLARE @noCaCerts int
    SELECT @noCaCerts = 0
    DECLARE @encryptAlg nvarchar(4000)
    SELECT @encryptAlg = 'aes128'

    DECLARE @pemStr nvarchar(4000)
    EXEC sp_OAMethod @pem, 'ToPemEx', @pemStr OUT, @extendedAttrs, @noKeys, @noCerts, @noCaCerts, @encryptAlg, @password

    PRINT @pemStr

    EXEC @hr = sp_OADestroy @pem
    EXEC @hr = sp_OADestroy @privKey
    EXEC @hr = sp_OADestroy @cert


END
GO