(SQL Server) Sign MIME using RSASSA-PSS with SHA256

Demonstrates how to sign an MIME 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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @success int

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

    -- Create a MIME message to sign.
    EXEC sp_OAMethod @mime, 'NewMultipartMixed', @success OUT
    EXEC sp_OAMethod @mime, 'AddHeaderField', @success OUT, 'someHeader1', 'Some value 1'
    EXEC sp_OAMethod @mime, 'AddHeaderField', @success OUT, 'someHeader2', 'Some value 2'

    DECLARE @part0 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Mime', @part0 OUT

    EXEC sp_OAMethod @part0, 'AddHeaderField', @success OUT, 'hdrA', 'Some value A'
    EXEC sp_OAMethod @part0, 'AddHeaderField', @success OUT, 'hdrB', 'Some value B'
    EXEC sp_OASetProperty @part0, 'ContentType', 'text/plain'
    EXEC sp_OAMethod @part0, 'SetBody', NULL, 'This is the plain-text body.'

    DECLARE @part1 int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Mime', @part1 OUT

    EXEC sp_OAMethod @part1, 'AddHeaderField', @success OUT, 'hdrX', 'Some value X'
    EXEC sp_OAMethod @part1, 'AddHeaderField', @success OUT, 'hdrY', 'Some value Y'
    EXEC sp_OASetProperty @part1, 'ContentType', 'text/xml'
    EXEC sp_OAMethod @part1, 'SetBody', NULL, '<a>This is the XML body</a>'

    EXEC sp_OAMethod @mime, 'AppendPart', @success OUT, @part0
    EXEC sp_OAMethod @mime, 'AppendPart', @success OUT, @part1

    -- The MIME to be signed:
    DECLARE @sbUnsignedMime int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbUnsignedMime OUT

    EXEC sp_OAMethod @mime, 'GetMimeSb', @success OUT, @sbUnsignedMime
    EXEC sp_OAMethod @sbUnsignedMime, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- 	Content-Type: multipart/mixed; boundary="------------080303020600020604010008"
    -- 	someHeader1: Some value 1
    -- 	someHeader2: Some value 2
    -- 
    -- 	--------------080303020600020604010008
    -- 	hdrA: Some value A
    -- 	hdrB: Some value B
    -- 	Content-Type: text/plain
    -- 
    -- 	This is the plain-text body.
    -- 	--------------080303020600020604010008
    -- 	hdrX: Some value X
    -- 	hdrY: Some value Y
    -- 	Content-Type: text/xml
    -- 
    -- 	<a>This is the XML body</a>
    -- 	--------------080303020600020604010008--
    -- 

    -- 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

    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 @mime
        EXEC @hr = sp_OADestroy @part0
        EXEC @hr = sp_OADestroy @part1
        EXEC @hr = sp_OADestroy @sbUnsignedMime
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    -- 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 @mime
        EXEC @hr = sp_OADestroy @part0
        EXEC @hr = sp_OADestroy @part1
        EXEC @hr = sp_OADestroy @sbUnsignedMime
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    -- Indicate that RSASSA-PSS with SHA256 should be used.
    EXEC sp_OASetProperty @mime, 'SigningAlg', 'pss'
    EXEC sp_OASetProperty @mime, 'SigningHashAlg', 'sha256'

    -- Add a detached signature.
    EXEC sp_OAMethod @mime, 'AddDetachedSignature', @success OUT, @cert
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert

        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @part0
        EXEC @hr = sp_OADestroy @part1
        EXEC @hr = sp_OADestroy @sbUnsignedMime
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    -- Show the clear-signed MIME. (see the sample output below)

    PRINT '-------------------------------------------------------------'

    PRINT 'Clear-signed MIME using detached signature:'

    PRINT '-------------------------------------------------------------'
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- Restore the original unsigned MIME, and this time create an opaque signature.
    EXEC sp_OAMethod @sbUnsignedMime, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @mime, 'LoadMime', @success OUT, @sTmp0

    -- Sign using an opaque signature
    EXEC sp_OAMethod @mime, 'ConvertToSigned', @success OUT, @cert
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @cert

        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @part0
        EXEC @hr = sp_OADestroy @part1
        EXEC @hr = sp_OADestroy @sbUnsignedMime
        EXEC @hr = sp_OADestroy @pfx
        RETURN
      END

    -- Show the opaque-signed MIME. (see the sample output below)

    PRINT '-------------------------------------------------------------'

    PRINT 'Opaque-signed MIME :'

    PRINT '-------------------------------------------------------------'
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @cert


    PRINT 'Success.'

    -- -----------------------------------------------
    -- This is sample output for the clear-signed MIME:
    -- -----------------------------------------------
    -- 
    -- 	Content-Type: multipart/signed; boundary="----=_NextPart_672_c95a_97b64c9a.90b5204d";
    -- 	 protocol="application/x-pkcs7-signature";
    -- 	 micalg=sha256
    -- 
    -- 	------=_NextPart_672_c95a_97b64c9a.90b5204d
    -- 	Content-Type: multipart/mixed; boundary="------------030207090102000301050504"
    -- 	someHeader1: Some value 1
    -- 	someHeader2: Some value 2
    -- 
    -- 	--------------030207090102000301050504
    -- 	hdrA: Some value A
    -- 	hdrB: Some value B
    -- 	Content-Type: text/plain
    -- 
    -- 	This is the plain-text body.
    -- 	--------------030207090102000301050504
    -- 	hdrX: Some value X
    -- 	hdrY: Some value Y
    -- 	Content-Type: text/xml
    -- 
    -- 	<a>This is the XML body</a>
    -- 	--------------030207090102000301050504--
    -- 
    -- 	------=_NextPart_672_c95a_97b64c9a.90b5204d
    -- 	Content-Transfer-Encoding: base64
    -- 	Content-Type: application/x-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
    -- 	MQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMTcwNDIwMTU1NDMyWjAvBgkqhkiG9w0BCQQx
    -- 	IgQgFEtkdw1+iBXvplXCUkOPlq66b+mDg+HBcChLn5YowD8wXwYJKoZIhvcNAQkPMVIwUDALBglg
    -- 	hkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFAMAcGBSsO
    -- 	AwIHMA0GCCqGSIb3DQMCAgEoMIGxBgkrBgEEAYI3EAQxgaMwgaAwgZIxCzAJBgNVBAYTAkFVMREw
    -- 	DwYDVQQIDAhWaWN0b3JpYTESMBAGA1UEBwwJTWVsYm91cm5lMSEwHwYDVQQKDBhJbnRlcm5ldCBX
    -- 	aWRnaXRzIFB0eSBMdGQxDzANBgNVBAMMBldpZGdldDEoMCYGCSqGSIb3DQEJARYZYWRtaW5AaW50
    -- 	ZXJuZXR3aWRnZXRzLmNvbQIJAMPsJCT11cniMIGzBgsqhkiG9w0BCRACCzGBo6CBoDCBkjELMAkG
    -- 	A1UEBhMCQVUxETAPBgNVBAgMCFZpY3RvcmlhMRIwEAYDVQQHDAlNZWxib3VybmUxITAfBgNVBAoM
    -- 	GEludGVybmV0IFdpZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAwwGV2lkZ2V0MSgwJgYJKoZIhvcNAQkB
    -- 	FhlhZG1pbkBpbnRlcm5ldHdpZGdldHMuY29tAgkAw+wkJPXVyeIwPQYJKoZIhvcNAQEKMDCgDTAL
    -- 	BglghkgBZQMEAgGhGjAYBgkqhkiG9w0BAQgwCwYJYIZIAWUDBAIBogMCASAEgYAvqIqKBzNXyVbh
    -- 	TmaxdAc4d0ZTqWT6Na3C+DMO8owSKWy5FdGbJ2DLKm92qe/KLPTcj51vuwLUQb0Sw7r5Xknl+/6p
    -- 	Eqn2qe/6hgDn06vvzNofVAweOtSVJsBO7/cYYPc+pXZZ2BcpZDdf/9Ci56Yrlk7A72BeqPe47J3F
    -- 	neC8qw==
    -- 
    -- 	------=_NextPart_672_c95a_97b64c9a.90b5204d--
    -- 

    -- -----------------------------------------------
    -- This is sample output for the opaque-signed MIME:
    -- -----------------------------------------------
    -- 	Content-Type: application/x-pkcs7-mime; name="smime.p7m"; smime-type="signed-data"
    -- 	someHeader1: Some value 1
    -- 	someHeader2: Some value 2
    -- 	Content-Disposition: attachment; filename="smime.p7m"
    -- 	Content-Transfer-Encoding: base64
    -- 
    -- 	MIIItAYJKoZIhvcNAQcCoIIIpTCCCKECAQExDzANBglghkgBZQMEAgEFADCCAdYGCSqGSIb3DQEH
    -- 	AaCCAccEggHDQ29udGVudC1UeXBlOiBtdWx0aXBhcnQvbWl4ZWQ7IGJvdW5kYXJ5PSItLS0tLS0t
    -- 	LS0tLS0wMDA2MDcwNzA3MDcwMTA1MDAwMTA5MDciDQpzb21lSGVhZGVyMTogU29tZSB2YWx1ZSAx
    -- 	DQpzb21lSGVhZGVyMjogU29tZSB2YWx1ZSAyDQoNCi0tLS0tLS0tLS0tLS0tMDAwNjA3MDcwNzA3
    -- 	MDEwNTAwMDEwOTA3DQpoZHJBOiBTb21lIHZhbHVlIEENCmhkckI6IFNvbWUgdmFsdWUgQg0KQ29u
    -- 	dGVudC1UeXBlOiB0ZXh0L3BsYWluDQoNClRoaXMgaXMgdGhlIHBsYWluLXRleHQgYm9keS4NCi0t
    -- 	LS0tLS0tLS0tLS0tMDAwNjA3MDcwNzA3MDEwNTAwMDEwOTA3DQpoZHJYOiBTb21lIHZhbHVlIFgN
    -- 	Cmhkclk6IFNvbWUgdmFsdWUgWQ0KQ29udGVudC1UeXBlOiB0ZXh0L3htbA0KDQo8YT5UaGlzIGlz
    -- 	IHRoZSBYTUwgYm9keTwvYT4NCi0tLS0tLS0tLS0tLS0tMDAwNjA3MDcwNzA3MDEwNTAwMDEwOTA3
    -- 	LS0NCqCCAvgwggL0MIICXaADAgECAgkAw+wkJPXVyeIwDQYJKoZIhvcNAQELBQAwgZIxCzAJBgNV
    -- 	BAYTAkFVMREwDwYDVQQIDAhWaWN0b3JpYTESMBAGA1UEBwwJTWVsYm91cm5lMSEwHwYDVQQKDBhJ
    -- 	bnRlcm5ldCBXaWRnaXRzIFB0eSBMdGQxDzANBgNVBAMMBldpZGdldDEoMCYGCSqGSIb3DQEJARYZ
    -- 	YWRtaW5AaW50ZXJuZXR3aWRnZXRzLmNvbTAeFw0xNjExMDExNjUyMzJaFw0yMTEwMzExNjUyMzJa
    -- 	MIGSMQswCQYDVQQGEwJBVTERMA8GA1UECAwIVmljdG9yaWExEjAQBgNVBAcMCU1lbGJvdXJuZTEh
    -- 	MB8GA1UECgwYSW50ZXJuZXQgV2lkZ2l0cyBQdHkgTHRkMQ8wDQYDVQQDDAZXaWRnZXQxKDAmBgkq
    -- 	hkiG9w0BCQEWGWFkbWluQGludGVybmV0d2lkZ2V0cy5jb20wgZ8wDQYJKoZIhvcNAQEBBQADgY0A
    -- 	MIGJAoGBAMYh2gKPJq+z4X9GbRUgHibo7tUvWP7UiqJ6hH00RfYZC+7ZUJ1x7LF/2prDVvGKkS+M
    -- 	vBSTDsx74FOUulZBsEwfzuzoqmwLwxXJeEwuk/Sgzw0WLH+8vPbB0uFSHsJm8kDSmWqQmT1uPntA
    -- 	i74Q7WS1QczYMVvqxyS8IvbLJsPXAgMBAAGjUDBOMB0GA1UdDgQWBBQ40oq7bOxciuKdbEgZPrni
    -- 	+txDCzAfBgNVHSMEGDAWgBQ40oq7bOxciuKdbEgZPrni+txDCzAMBgNVHRMEBTADAQH/MA0GCSqG
    -- 	SIb3DQEBCwUAA4GBACsW92Lm72LZAXVDD5x0LWXvDZjf6rpEiSWfZbytYkWN7TTrjijyFfLu/jqC
    -- 	IkJ52YVQuWGbWftcixkbLZbeUnvu/lqCf2AXBe7WHdyqQZeSi14TpJAlBdaCxbcZY+945u9MvwsX
    -- 	jLnt+Az1lZL5X0wlqN67jhF/+1TXtMtgqO8SMYIDszCCA68CAQEwgaAwgZIxCzAJBgNVBAYTAkFV
    -- 	MREwDwYDVQQIDAhWaWN0b3JpYTESMBAGA1UEBwwJTWVsYm91cm5lMSEwHwYDVQQKDBhJbnRlcm5l
    -- 	dCBXaWRnaXRzIFB0eSBMdGQxDzANBgNVBAMMBldpZGdldDEoMCYGCSqGSIb3DQEJARYZYWRtaW5A
    -- 	aW50ZXJuZXR3aWRnZXRzLmNvbQIJAMPsJCT11cniMA0GCWCGSAFlAwQCAQUAoIICNDAYBgkqhkiG
    -- 	9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xNzA0MjAxNTU4NTNaMC8GCSqGSIb3
    -- 	DQEJBDEiBCBWTSl3s251U8fqOeSAzUp1Yue53X+UCWT0ux+fclE4FDBfBgkqhkiG9w0BCQ8xUjBQ
    -- 	MAsGCWCGSAFlAwQBAjAKBggqhkiG9w0DBzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAw
    -- 	BwYFKw4DAgcwDQYIKoZIhvcNAwICASgwgbEGCSsGAQQBgjcQBDGBozCBoDCBkjELMAkGA1UEBhMC
    -- 	QVUxETAPBgNVBAgMCFZpY3RvcmlhMRIwEAYDVQQHDAlNZWxib3VybmUxITAfBgNVBAoMGEludGVy
    -- 	bmV0IFdpZGdpdHMgUHR5IEx0ZDEPMA0GA1UEAwwGV2lkZ2V0MSgwJgYJKoZIhvcNAQkBFhlhZG1p
    -- 	bkBpbnRlcm5ldHdpZGdldHMuY29tAgkAw+wkJPXVyeIwgbMGCyqGSIb3DQEJEAILMYGjoIGgMIGS
    -- 	MQswCQYDVQQGEwJBVTERMA8GA1UECAwIVmljdG9yaWExEjAQBgNVBAcMCU1lbGJvdXJuZTEhMB8G
    -- 	A1UECgwYSW50ZXJuZXQgV2lkZ2l0cyBQdHkgTHRkMQ8wDQYDVQQDDAZXaWRnZXQxKDAmBgkqhkiG
    -- 	9w0BCQEWGWFkbWluQGludGVybmV0d2lkZ2V0cy5jb20CCQDD7CQk9dXJ4jA9BgkqhkiG9w0BAQow
    -- 	MKANMAsGCWCGSAFlAwQCAaEaMBgGCSqGSIb3DQEBCDALBglghkgBZQMEAgGiAwIBIASBgILtktxt
    -- 	tXWMWio7hLcvHUWk0Q3jP3rRdgNhcd/NdZsgDFP9/aq8/nHLLHSWfSh1PH+eZeKJwXxUH4Lwk6Sm
    -- 	xFt97jyi3HaC3fezUSEqyg5WFG6fwOZGK4qF0WF+q29lvyNyV3iIQgDWxZQOcVL2fhpqUjoytkxj
    -- 	/QTfrLOuzXKy
    -- 

    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @part0
    EXEC @hr = sp_OADestroy @part1
    EXEC @hr = sp_OADestroy @sbUnsignedMime
    EXEC @hr = sp_OADestroy @pfx


END
GO