SQL Server

Create PKCS7 Attached/Opaque Signature (S/MIME)

See more MIME Examples

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

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
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.

    DECLARE @mime int
    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
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    DECLARE @pfxFilepath nvarchar(4000)
    SELECT @pfxFilepath = 'pfxFiles/something.pfx'
    DECLARE @pfxPassword nvarchar(4000)
    SELECT @pfxPassword = 'secret'
    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