SQL Server Requires Chilkat v11.0.0+
SQL Server
Send a Signed Email using RSASSA-PSS with SHA256
See more SMTP Examples
Demonstrates how to sign and send an email using the RSASSA-PSS signing algorithm with the SHA256 hash algorithm. .Chilkat SQL Server Downloads
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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.Pfx', @pfx OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @pfx, 'LoadPfxFile', @success OUT, 'qa_data/rsassa-pss/privatekey.pfx', 'PFX_PASSWORD'
IF @success = 0
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.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 @hr = sp_OACreate 'Chilkat.Cert', @cert OUT
EXEC sp_OAMethod @pfx, 'CertAt', @success OUT, 0, @cert
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @pfx, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @pfx
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @cert
RETURN
END
-- Tell the email object to use this cert (and private key) for signing.
EXEC sp_OAMethod @email, 'SetSigningCert', @success OUT, @cert
DECLARE @mailman int
EXEC @hr = sp_OACreate 'Chilkat.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 = 0
BEGIN
EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @pfx
EXEC @hr = sp_OADestroy @email
EXEC @hr = sp_OADestroy @cert
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 @cert
EXEC @hr = sp_OADestroy @mailman
END
GO