(SQL Server) Create PKCS7 Attached/Opaque Signature (S/MIME)

Create an S/MIME message with a PKCS7 attached/opaque signature.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @mime int
    -- Use "Chilkat_9_5_0.Mime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Mime', @mime OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load a certificate w/ private key for signing.
    -- There are many ways of getting a certificate object:
    -- from a pfx file, from .cer and private-key files, from the
    -- Windows registry-based certificate stores, etc.
    -- Load a PFX file into a certificate object.
    DECLARE @cert int
    -- Use "Chilkat_9_5_0.Cert" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @pfxFilepath nvarchar(4000)
    SELECT @pfxFilepath = 'pfxFiles/something.pfx'
    DECLARE @pfxPassword nvarchar(4000)
    SELECT @pfxPassword = 'secret'
    DECLARE @success int
    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, @pfxFilepath, @pfxPassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Create a very simple MIME message to be signed:
    EXEC sp_OAMethod @mime, 'AddHeaderField', @success OUT, 'Subject', 'This is a test'
    EXEC sp_OAMethod @mime, 'AddHeaderField', @success OUT, 'MyCustomHeader', 'abc123'
    EXEC sp_OASetProperty @mime, 'ContentType', 'text/plain'
    EXEC sp_OAMethod @mime, 'SetBody', NULL, 'This is a plain-text body.'

    -- Examine the MIME prior to signing:

    PRINT '--- MIME prior to signing ---'
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- The MIME prior to signing looks like this:
    -- -------------------------------------------------------------------------------

    -- Subject: This is a test
    -- MyCustomHeader: abc123
    -- Content-Type: text/plain
    -- 
    -- This is a plain-text body.

    -- -------------------------------------------------------------------------------

    -- The default hash algorithm used in the signing process is "SHA1".
    -- To change it, set the SigningHashAlg property:
    EXEC sp_OASetProperty @mime, 'SigningHashAlg', 'SHA256'

    -- Convert the MIME to PKCS7 signed-data.
    EXEC sp_OAMethod @mime, 'ConvertToSigned', @success OUT, @cert
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Examine the signed MIME

    PRINT '--- MIME converted to PKCS7 signed-data ---'
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- The MIME after signing looks like this:
    -- -------------------------------------------------------------------------------

    -- Subject: This is a test
    -- MyCustomHeader: abc123
    -- Content-Disposition: attachment; filename="smime.p7m"
    -- Content-Transfer-Encoding: base64
    -- Content-Type: application/x-pkcs7-mime;
    --  name="smime.p7m"; smime-type="signed-data"
    -- 
    -- MIIObAYJKoZIhvcNAQcCoIIOXTCCDlkCAQExDzANBglghkgBZQMEAgEFADB2BgkqhkiG9w0BBwGg
    -- aQRnU3ViamVjdDogVGhpcyBpcyBhIHRlc3QNCk15Q3VzdG9tSGVhZGVyOiBhYmMxMjMNCkNvbnRl
    -- bnQtVHlwZTogdGV4dC9wbGFpbg0KDQpUaGlzIGlzIGEgcGxhaW4tdGV4dCBib2R5LqCCCaAwggRm
    -- MIIDTqADAgECAhBEvgyLUAAktBHTNi3gs18bMA0GCSqGSIb3DQEBBQUAMIGVMQswCQYDVQQGEwJV
    -- UzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNF
    -- UlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTEdMBsGA1UE
    -- AxMUVVROLVVTRVJGaXJzdC1PYmplY3QwHhcNOTkwNzA5MTgzMTIwWhcNMTkwNzA5MTg0MDM2WjCB
    -- lTELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwG
    -- A1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVz
    -- dC5jb20xHTAbBgNVBAMTFFVUTi1VU0VSRmlyc3QtT2JqZWN0MIIBIjANBgkqhkiG9w0BAQEFAAOC
    -- AQ8AMIIBCgKCAQEAzqqBP6OjYXiqMQBVlRGeJw8fHN86m4JoMMBKYR3xLw76vnn3pSPvVVGWhM3b
    -- 47luPjHYCiBnx/TZv5TrRwQ+As4qol2HBAn2MJ0Yipeyqhz8QdKhNsv7PZG659lwNfrk55DDm6Ob
    -- 0zz1Epl3sbcJ4GjmHLjzlGOIamr+C3bJvvQi5Ge5qxped8GFB90NbL/uBsd3akGepw/X++6UF7f8
    -- hb6kq8QcMd3XttHk8O/fFo+yUpPXodSJoQcuv+EBEkIeGuHYlTTbZHko/7ouEcLl6FuSSPtHC8Js
    -- 2q0yg0HzpeVBcP1lkG36+lHE+b2WKxkELNNtp9zwf2+DZeJqq4eGdQIDAQABo4GvMIGsMAsGA1Ud
    -- DwQEAwIBxjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdDgQWBBTa7WR0FJwUPKvdmam9WyhNizzJ2DBC
    -- BgNVHR8EOzA5MDegNaAzhjFodHRwOi8vY3JsLnVzZXJ0cnVzdC5jb20vVVROLVVTRVJGaXJzdC1P
    -- YmplY3QuY3JsMCkGA1UdJQQiMCAGCCsGAQUFBwMDBggrBgEFBQcDCAYKKwYBBAGCNwoDBDANBgkq
    -- hkiG9w0BAQUFAAOCAQEACB9SsTdEeNv9zrnalZaYqlVkgLVaQN0hpcXB818sTMhHWmnq6PA1NfTQ
    -- JfPIpqSHSr0bsXMIvdTDyrY1u1mGdzHNp4AUrhPv/LFI+WslJS1RtixtRcGYyIpWXT7uQ04+ayeO
    -- 0DpLhQtf0+1qp3XL0VqHLzl1E1pysAKBn77wD4RUIGJsadThTcYNmUMBDRKWjHidv1CisUSqas8X
    -- es9vD9T4JFVf8DQWSWY+UEbJY3E4MWK4YrnzU61stSuiEqoZTwnaXueTxo4UCP7wMIAYoIaFTch9
    -- 14sD/m7V950WrJIsoCPlnJFSH5TfF5Rzw7PBwXEFIAB4vRNSHag+zQAfyDCCBTIwggQaoAMCAQIC
    -- EQDm/GiUo2tiCnVnsAEgJ0r7MA0GCSqGSIb3DQEBBQUAMIGVMQswCQYDVQQGEwJVUzELMAkGA1UE
    -- CBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5l
    -- dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTEdMBsGA1UEAxMUVVROLVVT
    -- RVJGaXJzdC1PYmplY3QwHhcNMDkxMDEyMDAwMDAwWhcNMTAxMDEyMjM1OTU5WjCBoDELMAkGA1UE
    -- BhMCVVMxDjAMBgNVBBEMBTYwMTg3MREwDwYDVQQIDAhJbGxpbm9pczEQMA4GA1UEBwwHV2hlYXRv
    -- bjEaMBgGA1UECQwRMTcxOSBFIEZvcmVzdCBBdmUxHzAdBgNVBAoMFkNoaWxrYXQgU29mdHdhcmUs
    -- IEluYy4xHzAdBgNVBAMMFkNoaWxrYXQgU29mdHdhcmUsIEluYy4wggEiMA0GCSqGSIb3DQEBAQUA
    -- A4IBDwAwggEKAoIBAQC5ueUJPjI74J5Yavc3JUCWHEMMd2C9T+4VDuUcN7vzYuIOjsyV1yhzAl8+
    -- uxzE+dhfcRK8qw9JbRV56Dzfyz5DqJH9x0rlbzkM24NeNZyM1kXIs2NHCJTYlYUqlVLZ2SXpiqru
    -- nnYEFScZho3knWoBYC4841sdUay70pqluo2Y1CBbx5t+zYPXit370hEGzKLNK6XRTevcVQQMvL/f
    -- d/gz241M8KBT8yvqTAjv6NJz9tHNUqhYO5mTT65nsIwKQ5/T8L+hsOHi4VM2QdVW6ZD9jrfPIfES
    -- yOyfXCx2l9oEHfB3gwgdYQ4Mq9hRG77mIc9H+pw1V0yMmQmEVs0LP6hvAgMBAAGjggFuMIIBajAf
    -- BgNVHSMEGDAWgBTa7WR0FJwUPKvdmam9WyhNizzJ2DAdBgNVHQ4EFgQUBMe/RfjpWAOjAMHml1qw
    -- DvPa4OowDgYDVR0PAQH/BAQDAgeAMAwGA1UdEwEB/wQCMAAwEwYDVR0lBAwwCgYIKwYBBQUHAwMw
    -- EQYJYIZIAYb4QgEBBAQDAgQQMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQMCMCswKQYIKwYBBQUH
    -- AgEWHWh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMEIGA1UdHwQ7MDkwN6A1oDOGMWh0dHA6
    -- Ly9jcmwudXNlcnRydXN0LmNvbS9VVE4tVVNFUkZpcnN0LU9iamVjdC5jcmwwNAYIKwYBBQUHAQEE
    -- KDAmMCQGCCsGAQUFBzABhhhodHRwOi8vb2NzcC5jb21vZG9jYS5jb20wIAYDVR0RBBkwF4EVYWRt
    -- aW5AY2hpbGthdHNvZnQuY29tMA0GCSqGSIb3DQEBBQUAA4IBAQAP/2IpG75SHSFFqjHqvCXy0n0O
    -- MTQk2IexnIUBlF7wm0oZquXBG2GlOPd9IXNr7k3wf4G+nZ6rgit6slVT1VxZS8RzqhGT3ZRTa2ud
    -- dSqJyKxxrqdBJMY/WWaWVtCSU74AKAAm/ONwD1Zz39IZ2Mr8ApXn2LnZy4ZgI3hZnVR8I92/sppl
    -- +NKWr0iMBpcz2KEwXZ5lrS5/4qRFlY+LQgRBFnjoM9WDW1lGEqeHFQQ/dqyrNiRAp+l7OzPBS4aM
    -- nfxS+Yj/blpnuW41dVi9TJTXozRwiHzQ41FfrkJMD4JZULc539SK1EU1cYMl13x3JODHlng2IrjZ
    -- pCKrFCsSCP2BMYIEJTCCBCECAQEwgaswgZUxCzAJBgNVBAYTAlVTMQswCQYDVQQIEwJVVDEXMBUG
    -- A1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBVU0VSVFJVU1QgTmV0d29yazEhMB8G
    -- A1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMR0wGwYDVQQDExRVVE4tVVNFUkZpcnN0LU9i
    -- amVjdAIRAOb8aJSja2IKdWewASAnSvswDQYJYIZIAWUDBAIBBQCgggJKMBgGCSqGSIb3DQEJAzEL
    -- BgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTEwMDYzMDE0NTg0MFowLwYJKoZIhvcNAQkEMSIE
    -- ILEFXqrFbp/l0XOJyyjW1IJhjJxJaOhLUlZTc8VcabVMMF8GCSqGSIb3DQEJDzFSMFAwCwYJYIZI
    -- AWUDBAECMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMC
    -- BzANBggqhkiG9w0DAgIBKDCBvAYJKwYBBAGCNxAEMYGuMIGrMIGVMQswCQYDVQQGEwJVUzELMAkG
    -- A1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNU
    -- IE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTEdMBsGA1UEAxMUVVRO
    -- LVVTRVJGaXJzdC1PYmplY3QCEQDm/GiUo2tiCnVnsAEgJ0r7MIG+BgsqhkiG9w0BCRACCzGBrqCB
    -- qzCBlTELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYDVQQHEw5TYWx0IExha2UgQ2l0eTEe
    -- MBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0
    -- cnVzdC5jb20xHTAbBgNVBAMTFFVUTi1VU0VSRmlyc3QtT2JqZWN0AhEA5vxolKNrYgp1Z7ABICdK
    -- +zANBgkqhkiG9w0BAQEFAASCAQBfYh75saVFNXRPOzXOemaoVIFz+OHSsQB1fqXbV+hjGkxuvWAT
    -- ldq32luVFFCz2gErWDT8eZ0yFVVwi16XyP7ptZeGU4cIGtrOozq78dziWwbNB021c3Dcw0vmH83m
    -- FrU8ZNXYhL2+Lu4PlgvSCxHtC9EKrsP0s3WvBk3nMgXhu4Nmvkl1pCAofhAXWs545TfFB3jLEgeR
    -- spbT6PhRR2lErXKF/Ga5NUyUqpw/Ba2YB3fFsbwiMyncmYicnUT2eYxxYBPtX9X6DQSKh3+iLNz0
    -- sx89fluXIoqWONjR/XyN8zYd6WtEqMMwY6HQPAblFEuMPHZIhC0h/hfGAVisKLmh
    -- 

    -- ------------------------------------------------------------------------------

    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @cert


END
GO