SQL Server

Set the Signing Certificate and Private Key

Demonstrates the Chilkat Email.SetSigningCert2 method, which explicitly sets the certificate and its corresponding private key as separate objects for sending digitally signed email. This example loads a .cer certificate and a PEM private key, sets both, and enables signed sending.

Background: When your signing certificate and its private key are stored separately — a public .cer plus a PEM key file — rather than combined in a PFX, SetSigningCert2 accepts the two objects individually. It is the two-object counterpart to SetSigningCert, which takes a single certificate that already carries its private key.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

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

    --  Demonstrates the SetSigningCert2 method, which explicitly sets the certificate and its
    --  corresponding private key (as separate objects) for sending digitally signed email.

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

    EXEC sp_OASetProperty @email, 'Subject', 'Signed email'
    EXEC sp_OASetProperty @email, 'Body', 'This message will be sent with a digital signature.'
    EXEC sp_OASetProperty @email, 'From', 'alice@example.com'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Bob', 'bob@example.com'

    --  Load the certificate (public) and the matching private key from separate files.
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/signer.cer'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    DECLARE @privKey int
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privKey OUT

    EXEC sp_OAMethod @privKey, 'LoadPemFile', @success OUT, 'qa_data/certs/signer_privkey.pem'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @privKey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    --  Set the certificate and private key used to create the signature.
    EXEC sp_OAMethod @email, 'SetSigningCert2', @success OUT, @cert, @privKey
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @cert
        EXEC @hr = sp_OADestroy @privKey
        RETURN
      END

    EXEC sp_OASetProperty @email, 'SendSigned', 1


    PRINT 'Signing certificate and private key set.'

    --  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 @cert
    EXEC @hr = sp_OADestroy @privKey


END
GO