SQL Server

Add a PFX Source from BinData

Demonstrates the Chilkat Email.AddPfxSourceBd method, which adds PFX data held in a BinData object to the list of certificate and private-key sources used during decryption or signing. The second argument is the PFX password. Call it before obtaining the encrypted email so Chilkat can decrypt it automatically. This example loads a PFX into a BinData, adds it, and then loads an encrypted email.

Background: This is the in-memory (BinData) counterpart to AddPfxSourceFile. It is the right choice when the PFX bytes come from somewhere other than a file — a database, a secrets manager, or an HTTP download — letting you supply the certificate and private key without first writing them to disk (which is preferable for sensitive key material).

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the AddPfxSourceBd method, which adds PFX data (held in a BinData) to the
    --  list of certificate and private-key sources used during decryption or signing.  The
    --  second argument is the PFX password.  Call it before obtaining the encrypted email.

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

    --  Load PFX data into a BinData object.
    DECLARE @bdPfx int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdPfx OUT

    EXEC sp_OAMethod @bdPfx, 'LoadFile', @success OUT, 'qa_data/certs/decryption.pfx'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @bdPfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdPfx
        RETURN
      END

    --  Add the PFX as a source of the certificate + private key.
    EXEC sp_OAMethod @email, 'AddPfxSourceBd', @success OUT, @bdPfx, 'pfx_password'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdPfx
        RETURN
      END

    --  Load an encrypted email; Chilkat decrypts it using the PFX source.
    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/encrypted.eml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @bdPfx
        RETURN
      END


    EXEC sp_OAGetProperty @email, 'Decrypted', @iTmp0 OUT
    PRINT 'Decrypted = ' + @iTmp0

    --  Note: Paths such as "qa_data/..." are relative local filesystem paths,
    --  relative to the current working directory of the running application.

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @bdPfx


END
GO