SQL Server

Encrypt/Decrypt using PFX to produce -----BEGIN PKCS7----- ... -----END PKCS7-----

See more Encryption Examples

First we encrypt using a certificate + public key to produce output such as:

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

Then we decrypt using the cert + private key. 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

    -- 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.
    -- Althought the PFX contains the associated private key, we don't need it for encryption.
    -- (A certificate usually contains the public key by default.)
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

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

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

    DECLARE @toBeEncrypted nvarchar(4000)
    SELECT @toBeEncrypted = 'This string is to be encrypted.'

    -- Get the result in multi-line BASE64 MIME format.
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64_mime'

    DECLARE @encryptedStr nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'EncryptStringENC', @encryptedStr OUT, @toBeEncrypted
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @crypt
        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 @sb, 'Append', @success OUT, @encryptedStr
    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-----
    -- MIICXAYJKoZIhvcNAQcDoIICTTCCAkkCAQAxggH0MIIB8AIBADCBrDCBlzELMAkGA1UEBhMCR0Ix
    -- GzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMHU2FsZm9yZDEaMBgGA1UEChMR
    -- Q09NT0RPIENBIExpbWl0ZWQxPTA7BgNVBAMTNENPTU9ETyBSU0EgQ2xpZW50IEF1dGhlbnRpY2F0
    -- aW9uIGFuZCBTZWN1cmUgRW1haWwgQ0ECEB6M1ZwZdZU7LrAIdurulmUwOAYJKoZIhvcNAQEHMCug
    -- DzANBglghkgBZQMEAgEFAKEYMBYGCSqGSIb3DQEBCDAJBgUrDgMCGgUABIIBAK/BZG/iXJ8az7zL
    -- 8EQ77mc+oDPQ4w1hyytK2ip4djkPVvTfYhcoDQ+G/DBU+urJfrVBi5H9gmpXwYyfKlyUxBVRVEJl
    -- V/V5QQi4JmNTFbmgWh5tp9zDS98l6A2Va4Zs0Wy/owGLfvwitlxd1dsfVAV2hmBYS24BMpNcty5/
    -- 0atcKYmSou13G78ztTKdMy1tECgZy8kerMsPdDQbSxEZkT3KpQ8C5uEQqYF3bIVaeZzha/Ywieh/
    -- tvO0T4aAmeJufwkNdVECmU7kuhnNaVPXknFl7jeibTl6zA/VcJKBKcIYT9FRC7KjdooI8q+jtQ/V
    -- k6RP4POaowkFg1QWRPEWeqIwTAYJKoZIhvcNAQcBMB0GCWCGSAFlAwQBAgQQEEFQduqeJqXQXzy4
    -- JpkoDoAgdldJDB9zEkpMpgr5/fR2iLvh5kC6BPfhOYjsawBY4Ok=
    -- -----END PKCS7-----

    -- ----------------------------------------------------------------------------------------
    -- Let's Decrypt the above string.

    -- Start with what was produced above..
    EXEC sp_OAMethod @sb, 'Clear', NULL
    DECLARE @bCrlf int
    SELECT @bCrlf = 1
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '-----BEGIN PKCS7-----', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'MIICXAYJKoZIhvcNAQcDoIICTTCCAkkCAQAxggH0MIIB8AIBADCBrDCBlzELMAkGA1UEBhMCR0Ix', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'GzAZBgNVBAgTEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBxMHU2FsZm9yZDEaMBgGA1UEChMR', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'Q09NT0RPIENBIExpbWl0ZWQxPTA7BgNVBAMTNENPTU9ETyBSU0EgQ2xpZW50IEF1dGhlbnRpY2F0', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'aW9uIGFuZCBTZWN1cmUgRW1haWwgQ0ECEB6M1ZwZdZU7LrAIdurulmUwOAYJKoZIhvcNAQEHMCug', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'DzANBglghkgBZQMEAgEFAKEYMBYGCSqGSIb3DQEBCDAJBgUrDgMCGgUABIIBAK/BZG/iXJ8az7zL', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '8EQ77mc+oDPQ4w1hyytK2ip4djkPVvTfYhcoDQ+G/DBU+urJfrVBi5H9gmpXwYyfKlyUxBVRVEJl', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'V/V5QQi4JmNTFbmgWh5tp9zDS98l6A2Va4Zs0Wy/owGLfvwitlxd1dsfVAV2hmBYS24BMpNcty5/', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '0atcKYmSou13G78ztTKdMy1tECgZy8kerMsPdDQbSxEZkT3KpQ8C5uEQqYF3bIVaeZzha/Ywieh/', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'tvO0T4aAmeJufwkNdVECmU7kuhnNaVPXknFl7jeibTl6zA/VcJKBKcIYT9FRC7KjdooI8q+jtQ/V', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'k6RP4POaowkFg1QWRPEWeqIwTAYJKoZIhvcNAQcBMB0GCWCGSAFlAwQBAgQQEEFQduqeJqXQXzy4', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'JpkoDoAgdldJDB9zEkpMpgr5/fR2iLvh5kC6BPfhOYjsawBY4Ok=', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '-----END PKCS7-----', @bCrlf

    DECLARE @decrypt int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @decrypt OUT

    EXEC sp_OASetProperty @decrypt, 'CryptAlgorithm', 'pki'

    -- Use the same cert + private key from the PFX above.
    -- For decryption, we need the private key.  Given that the certificate was loaded from a PFX,
    -- we should already have it.
    EXEC sp_OAMethod @decrypt, 'SetDecryptCert', @success OUT, @cert

    EXEC sp_OASetProperty @decrypt, 'EncodingMode', 'base64'
    DECLARE @decryptedText nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetBetween', @sTmp0 OUT, '-----BEGIN PKCS7-----', '-----END PKCS7-----'
    EXEC sp_OAMethod @decrypt, 'DecryptStringENC', @decryptedText OUT, @sTmp0


    PRINT @decryptedText

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @decrypt


END
GO