SQL Server

Set the Signing Certificate for an Email

Demonstrates the Chilkat Email.SetSigningCert method, which sets the certificate (with access to its private key) used to create a digital signature when sending signed email. This example loads the signing certificate from a PFX, sets it, and enables signed sending.

Background: Signing uses your own private key (the opposite of encrypting, which uses the recipient's public key), so the signing certificate must include private-key access. A PFX (PKCS#12) file bundles the certificate and its private key together, which is why it is loaded here with LoadPfxFile. If the certificate and key are in separate files, use SetSigningCert2 instead.

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 SetSigningCert method, which sets the certificate (with access to its
    --  private key) used for creating a digital signature when sending 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 a signing certificate together with its private key from a PFX file.
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/certs/signer.pfx', 'pfx_password'
    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

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

    --  Request that the email be sent with a digital signature.
    EXEC sp_OASetProperty @email, 'SendSigned', 1


    PRINT 'Signing certificate set.'

    --  Note: The path "qa_data/certs/signer.pfx" is a relative local filesystem path,
    --  relative to the current working directory of the running application.

    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @cert


END
GO