SQL Server

Create CMS (PKCS7) Detached MIME Signature

Demonstrates how to add a CMS detached signature to MIME. 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

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

    EXEC sp_OAMethod @cert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/ecc256_12345.p12', '12345'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    EXEC sp_OAGetProperty @cert, 'SubjectDN', @sTmp0 OUT
    PRINT 'Cert: ' + @sTmp0

    -- Create a 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; format=flowed
    -- 
    -- This is a plain-text body.

    -- Use SHA256 as the hash algorithm in the CMS signature.
    EXEC sp_OASetProperty @mime, 'SigningHashAlg', 'SHA256'

    -- Add a detached signature:
    EXEC sp_OAMethod @mime, 'AddDetachedSignature', @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 MIME with detached signature:

    PRINT '--- MIME with detached signature ---'
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    -- Content-Type: multipart/signed; boundary="----=_NextPart_6e3_c655_9641494d.b4f00501";
    --  protocol="application/x-pkcs7-signature";
    --  micalg=sha256
    -- 
    -- ------=_NextPart_6e3_c655_9641494d.b4f00501
    -- Subject: This is a test
    -- MyCustomHeader: abc123
    -- Content-Type: text/plain; format=flowed
    -- 
    -- This is a plain-text body.
    -- ------=_NextPart_6e3_c655_9641494d.b4f00501
    -- Content-Transfer-Encoding: base64
    -- Content-Type: application/x-pkcs7-signature; name="smime.p7s"
    -- Content-Disposition: attachment; filename="smime.p7s"
    -- 
    -- MIID7AYJKoZIhvcNAQcCoIID3TCCA9kCAQExDzANBglghkgBZQMEAgEFADALBgkqhkiG9w0BBwGg
    -- ggJxMIICbTCCAhOgAwIBAgIJAJuS2kgOoyr+MAkGByqGSM49BAEwWzELMAkGA1UEBhMCQVUxEzAR
    -- BgNVBAgTClNvbWUtU3RhdGUxITAfBgNVBAoTGEludGVybmV0IFdpZGdpdHMgUHR5IEx0ZDEUMBIG
    -- A1UEAxMLZWNjMjU2LXRlc3QwHhcNMTYwODIzMTU0OTM5WhcNNDQwMTA4MTU0OTM5WjBbMQswCQYD
    -- VQQGEwJBVTETMBEGA1UECBMKU29tZS1TdGF0ZTEhMB8GA1UEChMYSW50ZXJuZXQgV2lkZ2l0cyBQ
    -- dHkgTHRkMRQwEgYDVQQDEwtlY2MyNTYtdGVzdDBZMBMGByqGSM49AgEGCCqGSM49AwEHA0IABARy
    -- Hd86A7+qQ4DlIfKynZaFGdGLpkU3GlQwqaVD6GIJg3QIDhaWEksYtZ9OWjNHn9a6+i/P9o5/NrdI
    -- SP0VWDWjgcAwgb0wHQYDVR0OBBYEFGmd2DfK4c3VBTVn8ebKH2BTmRicMIGNBgNVHSMEgYUwgYKA
    -- FGmd2DfK4c3VBTVn8ebKH2BTmRicoV+kXTBbMQswCQYDVQQGEwJBVTETMBEGA1UECBMKU29tZS1T
    -- dGF0ZTEhMB8GA1UEChMYSW50ZXJuZXQgV2lkZ2l0cyBQdHkgTHRkMRQwEgYDVQQDEwtlY2MyNTYt
    -- dGVzdIIJAJuS2kgOoyr+MAwGA1UdEwQFMAMBAf8wCQYHKoZIzj0EAQNJADBGAiEAhuiwGy0lZKtK
    -- LA13Ffc8AMTK7NsePPSfs0clXlZUR78CIQDXsJXpYsJ7tlTeockrK+6099sG/gGFTRPiQYU8juyf
    -- LzGCAT8wggE7AgEBMGgwWzELMAkGA1UEBhMCQVUxEzARBgNVBAgTClNvbWUtU3RhdGUxITAfBgNV
    -- BAoTGEludGVybmV0IFdpZGdpdHMgUHR5IEx0ZDEUMBIGA1UEAxMLZWNjMjU2LXRlc3QCCQCbktpI
    -- DqMq/jANBglghkgBZQMEAgEFAKBpMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZIhvcN
    -- AQkFMQ8XDTIyMDUwODE0MzM1NFowLwYJKoZIhvcNAQkEMSIEIMvNpYuymLvF68aFFe+l3NEQTdkB
    -- 7T8U6/j+Y5FgDlIUMAoGCCqGSM49BAMCBEYwRAIgQ6migEn3Le9ciIVUP/qc2JZAJZWjpulJXY9Q
    -- IZJ3XK0CIGZwpLOgiv7ES8958hqT9a/q7aPc1xmkaZHtsat/k+Ki
    -- 
    -- ------=_NextPart_6e3_c655_9641494d.b4f00501--

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


END
GO