SQL Server

PKCS7 Decrypt Using .pfx or .p12 Data

See more MIME Examples

Demonstrates how to decrypt MIME using a specifc PFX file (.pfx or .p12) for the source of certificate(s) and private key(s). 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
    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 the MIME
    EXEC sp_OAMethod @mime, 'LoadMimeFile', @success OUT, 'encrypted.mim'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        RETURN
      END

ERROR: Cannot use pbytes in SQL Server examples...

    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

    EXEC sp_OAMethod @fac, 'ReadEntireFile', @pfxData OUT, 'pfxStore/myPkcs12.p12'
    EXEC sp_OAGetProperty @fac, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END

    DECLARE @pfxPassword nvarchar(4000)
    SELECT @pfxPassword = 'myPassword'
    EXEC sp_OAMethod @mime, 'DecryptUsingPfxData', @success OUT, @pfxData, @pfxPassword
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mime, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mime
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END

    --  Display the decrypted MIME:
    EXEC sp_OAMethod @mime, 'GetMime', @sTmp0 OUT
    PRINT @sTmp0

    --  Note: The MIME to be decrypted should look something like this
    --  when viewed in a text editor:
    --  (Note that the Content-Type is application/x-pkcs7-mime)

    --  Content-Disposition: attachment; filename="smime.p7m";
    --  Content-Transfer-Encoding: base64
    --  Content-Type: application/x-pkcs7-mime;
    --   name="smime.p7m";
    --  
    --  MIICRAYJKoZIhvcNAQcDoIICNTCCAjECAQAxggHIMIIBxAIBADCBqzCBlTELMAkGA1UEBhMCVVMx
    --  CzAJBgNVBAgTAlVUMRcwFQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJU
    --  UlVTVCBOZXR3b3JrMSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xHTAbBgNVBAMT
    --  FFVUTi1VU0VSRmlyc3QtT2JqZWN0AhEA5vxolKNrYgp1Z7ABICdK+zANBgkqhkiG9w0BAQEFAASC
    --  AQAJ68jTh3u3ovsOuz1aHXTV90GBB6DMfCdx0bZ85trSvDXBevMOtQdctz653de8EB2xU4RlaPQa
    --  oBpF8G01PMaKezd+FGLbhm7gmKl7boej3BboUzM4wrwjc8qHTGc/haT1ZA3jUU3DR4emVXNmRgWc
    --  FDU5Dnkf+Z2YXQop9wPCf1Ng+8LLjBX9yxgXP1ZyFDe9vUnSkk2fmZatEY2Y6eWHNbrj6q4F40qN
    --  PAyZW1rJSFmKTSPSzv7S0VWBMh+FgUKtqk4I6/+zhneLYeJBZFskmMIGFpgKdgrN7vbbr1a2gv/M
    --  PbHUxwKMD4KZ2auyuGKMVLBOm+n8v5f8EegloL/UMGAGCSqGSIb3DQEHATAZBggqhkiG9w0DAjAN
    --  AgE6BAgcohHNLdl9FoA4q22x8vXs05wuScRgLhwMOzvdGQkBJx+6iQDBuoCVxF8sQ35g+0PPxN3M
    --  6Zw6nI1gSIIKBS3ZjtI=

    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @fac


END
GO