(SQL Server) Send a Signed Email using RSASSA-PSS with SHA256

Demonstrates how to sign and send an email using the RSASSA-PSS signing algorithm with the SHA256 hash algorithm.

Note: This example requires Chilkat v9.5.0.67 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

// 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
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Get a digital certificate with private key from a .pfx
    -- (Chilkat has many different ways to provide a cert + private key for siging.
    -- Using a PFX is just one possible option.)
    DECLARE @pfx int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Pfx', @pfx OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/rsassa-pss/privatekey.pfx', 'PFX_PASSWORD'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Email', @email OUT

    -- Create a simple email.
    EXEC sp_OASetProperty @email, 'Subject', 'Sample RSASSA-PSS Signed Email'
    EXEC sp_OASetProperty @email, 'Body', 'Sample RSASSA-PSS Signed Email'
    EXEC sp_OASetProperty @email, 'From', 'from_name@mydomain.com'
    -- Add one ore more recipients..
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat Support', 'support@chilkatsoft.com'
    EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'Chilkat GMail', 'chilkat.support@gmail.com'

    -- Indicate that the email is to be signed, and that we want to use RSASSA-PSS.
    EXEC sp_OASetProperty @email, 'SendSigned', 1
    EXEC sp_OASetProperty @email, 'SigningAlg', 'pss'
    EXEC sp_OASetProperty @email, 'SigningHashAlg', 'sha256'

    -- Get the certificate to be used for signing.
    -- (The typical case for a PFX is that it contains a cert with an associated private key,
    -- as well as other certificates in the chain of authentication.  The cert with the private
    -- key should be in the first position at index 0.)
    DECLARE @cert int
    EXEC sp_OAMethod @pfx, 'GetCert', @cert OUT, 0
    EXEC sp_OAGetProperty @pfx, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    -- Tell the email object to use this cert (and private key) for signing.
    EXEC sp_OAMethod @email, 'SetSigningCert', @success OUT, @cert
    EXEC @hr = sp_OADestroy @cert

    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.MailMan', @mailman OUT

    -- Set the SMTP settings for your email account on your mail server.
    EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'SMTP_LOGIN'
    EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'SMTP_PASSWORD'
    EXEC sp_OASetProperty @mailman, 'SmtpHost', 'MY_SMTP_DOMAIN_OR_IP'
    EXEC sp_OASetProperty @mailman, 'SmtpPort', 587
    EXEC sp_OASetProperty @mailman, 'StartTLS', 1

    -- Signed email can be sent in two different ways.  
    -- In a multipart/signed email, the signature is attached as a separate MIME part.
    -- In an opaque email (signedData) the content of the email is encapsulated within the signature
    -- and the email is sent as "application/pkcs7-mime". 
    -- Either should be fine, but some receiving systems might require one or the other..
    EXEC sp_OASetProperty @mailman, 'OpaqueSigning', 0

    -- Send the email.  The mailman will sign the email as directed by the 
    -- property settings of the email object.
    EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @email
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pfx
        EXEC @hr = sp_OADestroy @email
        EXEC @hr = sp_OADestroy @mailman
        RETURN
      END

    EXEC sp_OAMethod @mailman, 'CloseSmtpConnection', @success OUT
    IF @success <> 1
      BEGIN

        PRINT 'Connection to SMTP server not closed cleanly.'
      END


    PRINT 'Mail Sent!'

    -- -----------------------------------------------------------
    -- This is the MIME of the RSASSA-PSS signed email that was sent

    -- 	MIME-Version: 1.0
    -- 	Date: Thu, 20 Apr 2017 10:14:58 -0500
    -- 	Message-ID: <3682A87344CB3A4FB5EB5BC9908C0C4DA01DA461@CHILKAT13>
    -- 	Content-Type: multipart/signed; protocol="application/pkcs7-signature"; micalg=sha256;
    -- 	 boundary="------------050103020705000601010800"
    -- 	X-Priority: 3 (Normal)
    -- 	Subject: Sample RSASSA-PSS Signed Email
    -- 	From: support@chilkatsoft.com
    -- 	To: "Chilkat Support" <support@chilkatsoft.com>,
    -- 	 "Chilkat GMail" <chilkat.support@gmail.com>
    -- 
    -- 	--------------050103020705000601010800
    -- 	Content-Type: text/plain; format=flowed
    -- 	Content-Transfer-Encoding: 7bit
    -- 
    -- 	Sample RSASSA-PSS Signed Email
    -- 	--------------050103020705000601010800
    -- 	Content-Transfer-Encoding: base64
    -- 	Content-Type: application/pkcs7-signature; name="smime.p7s"
    -- 	Content-Disposition: attachment; filename="smime.p7s"
    -- 
    -- 	MIIG5wYJKoZIhvcNAQcCoIIG2DCCBtQCAQExDzANBglghkgBZQMEAgEFADALBgkqhkiG9w0BBwGg
    -- 	ggL4MIIC9DCCAl2gAwIBAgIJAMPsJCT11cniMA0GCSqGSIb3DQEBCwUAMIGSMQswCQYDVQQGEwJB
    -- 	VTERMA8GA1UECAwIVmljdG9yaWExEjAQBgNVBAcMCU1lbGJvdXJuZTEhMB8GA1UECgwYSW50ZXJu
    -- 	ZXQgV2lkZ2l0cyBQdHkgTHRkMQ8wDQYDVQQDDAZXaWRnZXQxKDAmBgkqhkiG9w0BCQEWGWFkbWlu
    -- 	QGludGVybmV0d2lkZ2V0cy5jb20wHhcNMTYxMTAxMTY1MjMyWhcNMjExMDMxMTY1MjMyWjCBkjEL
    -- 	MAkGA1UEBhMCQVUxETAPBgNVBAgMCFZpY3RvcmlhMRIwEAYDVQQHDAlNZWxib3VybmUxITAfBgNV
    -- 	BAoMGEludGVybmV0IFdpZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAwwGV2lkZ2V0MSgwJgYJKoZIhvcN
    -- 	AQkBFhlhZG1pbkBpbnRlcm5ldHdpZGdldHMuY29tMIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKB
    -- 	gQDGIdoCjyavs+F/Rm0VIB4m6O7VL1j+1IqieoR9NEX2GQvu2VCdceyxf9qaw1bxipEvjLwUkw7M
    -- 	e+BTlLpWQbBMH87s6KpsC8MVyXhMLpP0oM8NFix/vLz2wdLhUh7CZvJA0plqkJk9bj57QIu+EO1k
    -- 	tUHM2DFb6sckvCL2yybD1wIDAQABo1AwTjAdBgNVHQ4EFgQUONKKu2zsXIrinWxIGT654vrcQwsw
    -- 	HwYDVR0jBBgwFoAUONKKu2zsXIrinWxIGT654vrcQwswDAYDVR0TBAUwAwEB/zANBgkqhkiG9w0B
    -- 	AQsFAAOBgQArFvdi5u9i2QF1Qw+cdC1l7w2Y3+q6RIkln2W8rWJFje00644o8hXy7v46giJCedmF
    -- 	ULlhm1n7XIsZGy2W3lJ77v5agn9gFwXu1h3cqkGXkoteE6SQJQXWgsW3GWPveObvTL8LF4y57fgM
    -- 	9ZWS+V9MJajeu44Rf/tU17TLYKjvEjGCA7MwggOvAgEBMIGgMIGSMQswCQYDVQQGEwJBVTERMA8G
    -- 	A1UECAwIVmljdG9yaWExEjAQBgNVBAcMCU1lbGJvdXJuZTEhMB8GA1UECgwYSW50ZXJuZXQgV2lk
    -- 	Z2l0cyBQdHkgTHRkMQ8wDQYDVQQDDAZXaWRnZXQxKDAmBgkqhkiG9w0BCQEWGWFkbWluQGludGVy
    -- 	bmV0d2lkZ2V0cy5jb20CCQDD7CQk9dXJ4jANBglghkgBZQMEAgEFAKCCAjQwGAYJKoZIhvcNAQkD
    -- 	MQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMTcwNDIwMTUxNDU4WjAvBgkqhkiG9w0BCQQx
    -- 	IgQgoHOtpFiAgti0cwDw46C8wyYSZbhx2yJqJZ/qRE8WPSQwXwYJKoZIhvcNAQkPMVIwUDALBglg
    -- 	hkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsO
    -- 	AwIHMA0GCCqGSIb3DQMCAgEoMIGxBgkrBgEEAYI3EAQxgaMwgaAwgZIxCzAJBgNVBAYTAkFVMREw
    -- 	DwYDVQQIDAhWaWN0b3JpYTESMBAGA1UEBwwJTWVsYm91cm5lMSEwHwYDVQQKDBhJbnRlcm5ldCBX
    -- 	aWRnaXRzIFB0eSBMdGQxDzANBgNVBAMMBldpZGdldDEoMCYGCSqGSIb3DQEJARYZYWRtaW5AaW50
    -- 	ZXJuZXR3aWRnZXRzLmNvbQIJAMPsJCT11cniMIGzBgsqhkiG9w0BCRACCzGBo6CBoDCBkjELMAkG
    -- 	A1UEBhMCQVUxETAPBgNVBAgMCFZpY3RvcmlhMRIwEAYDVQQHDAlNZWxib3VybmUxITAfBgNVBAoM
    -- 	GEludGVybmV0IFdpZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAwwGV2lkZ2V0MSgwJgYJKoZIhvcNAQkB
    -- 	FhlhZG1pbkBpbnRlcm5ldHdpZGdldHMuY29tAgkAw+wkJPXVyeIwPQYJKoZIhvcNAQEKMDCgDTAL
    -- 	BglghkgBZQMEAgGhGjAYBgkqhkiG9w0BAQgwCwYJYIZIAWUDBAIBogMCASAEgYCDQpT6vJZ9zERJ
    -- 	JVX69PGUfCN5Eq9pKzoOTQSrIBe1YgNDSPd5pyOPP2MN1RpI0T/GTQZw4iWy5LZq0T5sjmk3fFVB
    -- 	+VZjKtkTuhGaCdzMQXomVLBbI2fN4UaA4v1/Ayrqr8SY9Rsa20TpKv/bufK7oYs083UOtlcA1EHY
    -- 	41wQ5A==
    -- 
    -- 	--------------050103020705000601010800--

    EXEC @hr = sp_OADestroy @pfx
    EXEC @hr = sp_OADestroy @email
    EXEC @hr = sp_OADestroy @mailman


END
GO