SQL Server

Sign and Encrypt using Cert to produce -----BEGIN PKCS7----- ... -----END PKCS7-----

See more Encryption Examples

Demonstrates how to first sign content using your own cert + private key to produce PKCS7 binary output. Then encrypt the signed binary output using the recipient's public key to produce output such as:

-----BEGIN PKCS7-----
MIIHPwYJKoZIhvcNAQcEoIIHMDCCBywC ...
...
...
-----END PKCS7-----

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

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

    DECLARE @toBeSignedAndEncrypted nvarchar(4000)
    SELECT @toBeSignedAndEncrypted = 'This string is to be signed and then encrypted.'

    DECLARE @myCert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @myCert OUT

    EXEC sp_OAMethod @myCert, 'LoadPfxFile', @success OUT, 'qa_data/pfx/cert_test123.pfx', 'test123'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @myCert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @myCert
        RETURN
      END

    EXEC sp_OAMethod @crypt, 'SetSigningCert', @success OUT, @myCert
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @myCert
        RETURN
      END

    -- Get the PKCS7 output in base64 format.
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    -- Perhaps we don't want the entire cert chain to be included in the signature?
    EXEC sp_OASetProperty @crypt, 'IncludeCertChain', 0
    DECLARE @signedPkcs7 nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'OpaqueSignStringENC', @signedPkcs7 OUT, @toBeSignedAndEncrypted

    -- We're going to encrypt the binary PKCS7 signature (which contains the content)
    DECLARE @bdPkcs7 int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdPkcs7 OUT

    EXEC sp_OAMethod @bdPkcs7, 'AppendEncoded', @success OUT, @signedPkcs7, 'base64'

    -- ---------------------------------------------------
    -- Now for the encryption...

    -- Specify the encryption to be used.
    -- "pki" indicates "Public Key Infrastructure" and will create a PKCS7 encrypted (enveloped) message.
    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'pki'
    EXEC sp_OASetProperty @crypt, 'Pkcs7CryptAlg', 'aes'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 128
    EXEC sp_OASetProperty @crypt, 'OaepHash', 'sha256'
    EXEC sp_OASetProperty @crypt, 'OaepPadding', 1

    -- A certificate is needed as the encryption key..
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/certs/testCert.pem'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @myCert
        EXEC @hr = sp_OADestroy @bdPkcs7
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Tell the crypt object to use the certificate.
    EXEC sp_OAMethod @crypt, 'SetEncryptCert', @success OUT, @cert

    EXEC sp_OAMethod @crypt, 'EncryptBd', @success OUT, @bdPkcs7
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @myCert
        EXEC @hr = sp_OADestroy @bdPkcs7
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Make a "-----BEGIN PKCS7-----" ... "-----END PKCS7-----" sandwich...
    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '-----BEGIN PKCS7-----', 1
    EXEC sp_OAMethod @bdPkcs7, 'GetEncoded', @sTmp0 OUT, 'base64_mime'
    EXEC sp_OAMethod @sb, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '-----END PKCS7-----', 1

    DECLARE @outStr nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetAsString', @outStr OUT


    PRINT @outStr

    -- Sample output:

    -- -----BEGIN PKCS7-----
    -- MIIMFQYJKoZIhvcNAQcDoIIMBjCCDAICAQAxggH5MIIB9QIBADCBsTCBmzELMAkGA1UEBhMCR0Ix
    -- GzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMHU2FsZm9yZDEaMBgGA1UEChMR
    -- Q09NT0RPIENBIExpbWl0ZWQxQTA/BgNVBAMTOENPTU9ETyBTSEEtMjU2IENsaWVudCBBdXRoZW50
    -- aWNhdGlvbiBhbmQgU2VjdXJlIEVtYWlsIENBAhEAuBl4qE2MODB05C5h53M5UDA4BgkqhkiG9w0B
    -- AQcwK6APMA0GCWCGSAFlAwQCAQUAoRgwFgYJKoZIhvcNAQEIMAkGBSsOAwIaBQAEggEAA34hkvxY
    -- TXMUAsWBmnRIvfFK920WeUVenM4ob7qibDiaXI2qWBgzkIfQH2T1vIYHHzNQuaaiTrK3THn8Nzfy
    -- RFqTnhfWwsIL/yinHVn5rA9CbHFmxqHr2UctZxLB7x/DxV7pjqpTTveCwgC9FykxGBcNO92Thlho
    -- bHyq1VM+tm4ppkyV982xL1VAIUX6BvqRlfPsB3hJsdGz/Z3HBdLNgkp2z8mMcLZx9vXwZ3Nyrsi1
    -- JqUP4wJ+HHYb5aJseE2UtrEh+s/eq+49OS9BhAWKRlmTxZo8XSQPNeVIRt0HvFAr/+E+jiyASI7b
    -- 9mjPPWP36ia4+/yhUwchnD/bJxa5gjCCCf4GCSqGSIb3DQEHATAdBglghkgBZQMEAQIEEFApbYsk
    -- 2qdwHv7dkngFg3CAggnQiMpHuoyOxSilO2lowI73vwX44PKKu1aGFW+mCbne/UZfOAgbvpHQ1LcP
    -- Nam+I8MZRtT7nqN3ZWIIvW468z2Z8ne4DqO/D0adlSwE66Z2JxkZncrUM+QR1Oo9sz7aY0eUxzeB
    -- 5yTUcm3gZSFsKJ0czKzxF7r8IE2ytRVjYWbSZ4eGcJ9Pjpxk38+IZTkcWyzreOCSE9NvH+mefJHT
    -- IeTH05w96Rei5YEq1Zn41+bw/Yh19hKHarudAerA1v5f36gz/eWMF2VF5/D23dW3tbw1DpDc/96B
    -- 3Mu4/Z1CREQ1C6NLlRHIoULADScuzyQTlmbj2v7vBJa46GZvjswyDbALIJ7B026JHpSUCPcf90q4
    -- qlAoOjLCOSEMb8qhHHchwEaHcydCRB1bschj+eBHJp6R3spbQXCtRnSLuvTdBSPVR5sD140q8f68
    -- YHyozbBWkxp5vv6fOyQMBibUhW7tyLod7M4pcgZlSKE6v9qhOCnE5Ym+wkX/t78E+W1wflmAwVJo
    -- WZXoJNul6b6Ith9nCWwX3d6iJY/mdapaqabFGV2qFu1MG1wiKzrZtl/5qbVNXeraLLOTBW+hPeNt
    -- hb0XpWHtR28Ig1Y51P2wEGNoTsZrkJrOn8Go0mgHlWnUPLJImyDZYdJF6ZvlRhpSj/99RLHZORT7
    -- m1akNtXeQhfE3bj2tlYkviuBYtetwIGos9y74XCQWkours/5as6WqKozYHpFigPB3OLdWbq2txxc
    -- k741BL/Mw5ugtHmPqNzRn4txMvB+lAcE1y75sESqC774bSCmrn+uVmOTZFvNob0dhZNTGsbcLCTe
    -- LLGS3ZUscah/9iVte5xGnsCOSq18PjPIdqMvzSEcrYQVyqe2/68mae6dpk7gyeiTYpDsSYO+KaHw
    -- bwwViPnVOhRkXxU54unymiSmgIRrhn+hsbkxvJk3Q307a7wcj+/ygw5stTV2hLmIQotMvmlpFTLZ
    -- PhB5eyFIZaWKV8XJgPV5JInrm39taw8ClHeSC31BNgfkX5u2h0pA1f5zMZ/SvgriW0Xumyoy/9XC
    -- eVL6fCZEWmaRZbAy5RlVELR/N2/Z5VS6s8U6Wrck9Zq9sGelf+0QWbCEwNoxQf+xxH55QnzHRvpu
    -- s839qRy/5msXSYno0fl3uE6cP/bWwk0Rvt3QYp75UTZDkfXl0ywy00V6AMPssKlDZe0GYWaqcHqz
    -- xj2sOK++Iz0tlmhS6QWcGrgWyVaHG9E2van/yLE4Sl4qnKt88/vK+cTKCrRCStslrt2Z/1myLnaB
    -- 6b6uAlVFk+kIHH82WaXxfCVedoGVk+jL3qPRbQiCzm7O66N6ecXIFWQ10sR5AXk/k4M6rM2Zhgoh
    -- yjPAxEHOzZ7nTLMb2MvPSV4KbrQT7Qs8prefY6o1OANXu4HRzL+zzttiIrV72vVemF80q7e5wYKR
    -- pGC85zZwz084RSWSwHTXDeWpI8zRPM7eboR97q9dfy7V23YlYJyM2RYrusweULib6X4me9nPhOzD
    -- 4KmqI6FSOvwTUdEDb8k32+GVB+zXsEhI6uPFz+Fgve8WUKcWX0DFoD905+KICeDFlOxWjB5FhKqF
    -- DysgV1FCCN8fGWRvbe7R74KR8SYwUOSAE63PDtGDWK345sOQXp5b0Vv4lVG5/ECZqYCM9RixLsyx
    -- vrozd8L0wqQJ5vlMOt9wksSYa8zzShC/tO3aGyw9NLz6y3lKoz3NfA04k8IyT2wtx2cG82h4bdwo
    -- aBPdFlV9P/WlVPJXgRv0GFJFX4HiVMQBEn3Rj8H/BHzZYPo9tLnPRmuoTWv+HaoAEzxwQ4O+L9av
    -- 0BO0eJSJNxs3UTaxl/psm17EinNLCV7lx7jKppj8v7Xxdem0+Fu0ME92XmFKG52tT9XkniF6orNP
    -- 9G48f93G7tIx4CLNT5QXhnUejZG0v0uBxnL5dURV4W/49Qg49lEZZr6yUZlDUhMBSKwCW/gcTeGv
    -- XVscW4UaGH9ewPFYVHNaDh7ehxkaTtuTrVpS7fPhaWLwozR1ro+U/WIqjE7QeharK+eqJN7OJNcV
    -- vx42zsCCzAankT70bGsEqtA/il4IbxEa3EQTqOMLdxSJ/62IZRKGNxwZnRou8i1pswMM5q/9/ITi
    -- MweZHH+AWeXrf5CLVObq2ZPk6NUPScFKSy7Vao1SxORhj+YJDd3vznWpjjq88Qs1ntwitY13jnxh
    -- 58XEwI+vkjAY34lOEtr2UGXS0DrANN0//VMk40WZat5rVnx9XITC9RmGFvtM0RDgMZ3BOEXyI1mI
    -- nGLILcAVW+mnxJcafwFCbI1nd6KyfQU+/RmXdOLPtKMjEKm2jBZt3UGaxLkHKCuzESdYtlUkbbcL
    -- K5ynNEANfBNJjf494Sd+jbKe9031E7bl4nbQGFpmJDEnszWfKMdg9ChlOfYLM26pFN0hQahA26fn
    -- PgrBYvKaHauR0SUPtibwmy2kIdKoakJoIvyDDYh6HHrdcwGVHfA5nCSFIHGsv3lcdLc2sMq38TR/
    -- zh7verXj0Aa71VFfmxBrRvasvdp9ms7GZFzDzMq2yf+a4knzTdRHtst6jOoTXb0RXJuLfvDTJqmF
    -- tue95J8OELZOpWnp21Yj52So78BlcOC4W3VexT4hyHhtziG9BKeXvc6WSU1pUEoNkXAXXrHLi/AG
    -- lC45zJ+LzDp/3rOzGQ5SIIi67ZSSQNiW85ektAoSEJZlZ1xaXt9DEbugpkySfjI/GFxSmFZqCs6h
    -- RNT4f8BAKX56ItobBORZbvk12Mn5QrxIEGFbOFcLt6cA/zqixS8ej1cz+SsMUkuzl/KDh6ncXeuF
    -- 2DawyL7/konWins6Jo3gYhRgw4fpck14aaEF5/+h3FvgVw23FieWYc6AImn/2zvtv6+gCJl7YnrG
    -- 9zSjHx2Kf7WRiFbpSqYXGGA0iD0b7So9oyrTItepIRQh0OFcDhCGXvhl2Sb7IQM7sL3WvbAHcseS
    -- 0xJiEIZogWbVKL5OqAn/VrJsL+0atvkh6qy5FVmk9CBWh92lkmwWyk8+CoTVC6DPMRXLv/MQar2W
    -- j0cOL12vohVJe9MSHTMR6kHaaxj1J8b5AswZTBsbgFo16Q6ihxLC1fd70OM1c6Hak341UgCxHsOr
    -- j9JfT+r5jpK3booIzBorGNJOkAuaBAEKdcnNpyCP2sDCzG+7Ws1H+lJzXVDXARftZSbMvfLctU49
    -- 5YZl+bBifBF8fSSSRNKXMbPeyww1idjezWKiXQC3hA6VPKtW7rn1py7hTlkdm6os1jW9vmSmz30j
    -- KHvey6Kcy+fdc1gyYmXhSDD55uYVrj1JmbaZvIeGTh8cQ8E4Dsq2Hy2PQ8S2e2S6YcJ9X6yNkwXG
    -- KrqG2vK4AeBuro1IfoUG1EJ17Q==
    -- -----END PKCS7-----

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @myCert
    EXEC @hr = sp_OADestroy @bdPkcs7
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @sb


END
GO