SQL Server

Decrypt PKCS7 (CMS) EnvelopedData

This example receives a PKCS7 (CMS) EnvelopedData message that was encrypted to me. To decrypt it:

1. The CMS structure contains -
   • Encrypted content (ciphertext)
   • The symmetric key used to encrypt the content, itself encrypted with my public key
   • Information identifying the recipient (me), typically by X.509 certificate
2. Decryption process (what Chilkat does internally)
   • My software uses my digital certificat's private RSA key (which corresponds to the public key used during encryption) to decrypt the encrypted symmetric key.
   • The decrypted symmetric key is then used to decrypt the content-encryption layer, typically encrypted with a symmetric algorithm like AES.
3. Result -
   • I recover the original plaintext data.

---

Summary:

• The content itself is encrypted with a symmetric key (e.g., AES).
• That symmetric key is encrypted using my RSA public key and included in the EnvelopedData.
• I use my RSA private key to decrypt the symmetric key and then use it to decrypt the actual data.

Only the intended recipient, who possesses the correct private key, can successfully decrypt the EnvelopedData.

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 @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- Here's the PKCS7 (CMS) EnvelopedData we received.
    -- It was created using this example:  Create PKCS7 CMS EnvelopedData

    -- MIICSgYJKoZIhvcNAQcDoIICOzCCAjcCAQAxggHiMIIB3gIBADCBljCBgTELMAkGA1UEBhMCSVQx
    -- EDAOBgNVBAgMB0JlcmdhbW8xGTAXBgNVBAcMEFBvbnRlIFNhbiBQaWV0cm8xFzAVBgNVBAoMDkFj
    -- dGFsaXMgUy5wLkEuMSwwKgYDVQQDDCNBY3RhbGlzIENsaWVudCBBdXRoZW50aWNhdGlvbiBDQSBH
    -- MwIQPCWvkSv8oQ7xRmEHJ6TzEDA8BgkqhkiG9w0BAQcwL6APMA0GCWCGSAFlAwQCAQUAoRwwGgYJ
    -- KoZIhvcNAQEIMA0GCWCGSAFlAwQCAQUABIIBAKqHAPQNSsQoX7B2NH7QyEOWQRsSVs8oCHXmy8f4
    -- MVZD2er3bvYUCIomxpwbLEAl14qjUIMynahooYGgqip7+4FqL301G+BVjZVfEhHWj+VI1dAWnWuL
    -- VHlvc/pbQNBWqV8rKVJsNIsuAZkdj4WSwLVKxYkYX43B8fh/g71XN2DTJu7Z/824v48KBmgpQBOT
    -- 2q7IcDGxNPAFN2p6eavIVGn2LvhEbf/Fszyj+GR5tMcnQP1BOLJ3s3JzUBbvj8hcZrF1Vhl9HnTU
    -- YQx8G/KdW1mR+Wlhl3BWoK0LYKRTbnTx2BXOs0CY1SXOAdhKr01ZYjA+xW4nGzY0lfXS9QZjh9gw
    -- TAYJKoZIhvcNAQcBMB0GCWCGSAFlAwQBKgQQw0xTbfmnt0zjWHo5SaQIp4AgxTVY9E/Ncqy6t+RM
    -- 8y4c3Av62/wB8IpPUEmtM2OeuZo=

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

    DECLARE @bCrlf int
    SELECT @bCrlf = 1
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'MIICSgYJKoZIhvcNAQcDoIICOzCCAjcCAQAxggHiMIIB3gIBADCBljCBgTELMAkGA1UEBhMCSVQx', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'EDAOBgNVBAgMB0JlcmdhbW8xGTAXBgNVBAcMEFBvbnRlIFNhbiBQaWV0cm8xFzAVBgNVBAoMDkFj', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'dGFsaXMgUy5wLkEuMSwwKgYDVQQDDCNBY3RhbGlzIENsaWVudCBBdXRoZW50aWNhdGlvbiBDQSBH', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'MwIQPCWvkSv8oQ7xRmEHJ6TzEDA8BgkqhkiG9w0BAQcwL6APMA0GCWCGSAFlAwQCAQUAoRwwGgYJ', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'KoZIhvcNAQEIMA0GCWCGSAFlAwQCAQUABIIBAKqHAPQNSsQoX7B2NH7QyEOWQRsSVs8oCHXmy8f4', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'MVZD2er3bvYUCIomxpwbLEAl14qjUIMynahooYGgqip7+4FqL301G+BVjZVfEhHWj+VI1dAWnWuL', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'VHlvc/pbQNBWqV8rKVJsNIsuAZkdj4WSwLVKxYkYX43B8fh/g71XN2DTJu7Z/824v48KBmgpQBOT', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '2q7IcDGxNPAFN2p6eavIVGn2LvhEbf/Fszyj+GR5tMcnQP1BOLJ3s3JzUBbvj8hcZrF1Vhl9HnTU', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'YQx8G/KdW1mR+Wlhl3BWoK0LYKRTbnTx2BXOs0CY1SXOAdhKr01ZYjA+xW4nGzY0lfXS9QZjh9gw', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, 'TAYJKoZIhvcNAQcBMB0GCWCGSAFlAwQBKgQQw0xTbfmnt0zjWHo5SaQIp4AgxTVY9E/Ncqy6t+RM', @bCrlf
    EXEC sp_OAMethod @sb, 'AppendLine', @success OUT, '8y4c3Av62/wB8IpPUEmtM2OeuZo=', @bCrlf

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

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'pki'

    -- The CMS message contains information about the digital certificate that was used to encrypt.
    -- Assuming the certificate is installed in a Windows certificate store, or macOS keychain,
    -- Chilkat will automatically locate and use the certificate + private key for decryption.

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'

    DECLARE @decryptedText nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'DecryptStringENC', @decryptedText OUT, @sTmp0
    EXEC sp_OAGetProperty @crypt, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @crypt
        RETURN
      END


    PRINT @decryptedText

    -- We can get the digital certificate used for decryption by calling LastDecryptCert
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat.Cert', @cert OUT

    EXEC sp_OAMethod @crypt, 'LastDecryptCert', @success OUT, @cert
    EXEC sp_OAGetProperty @crypt, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    EXEC sp_OAGetProperty @cert, 'SubjectCN', @sTmp0 OUT
    PRINT 'Cert used to decrypt: ' + @sTmp0

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


END
GO