(SQL Server) Decrypt a SAML Response

Demonstrates how to decrypt a SAML response.

Note: This example requires Chilkat v9.5.0.76 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

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

    -- This example decrypts this SAML response:

    -- <?xml version="1.0" encoding="UTF-8" ?>
    -- <saml2p:Response Destination="https://deskflow-asp2.com/ubc/ubcdfe.dll/cwlacs" ID="_e4585eaeedbcaf7c24dff7f1ee2499f5" IssueInstant="2018-10-11T17:46:20.727Z" Version="2.0" xmlns:saml2p="urn:oasis:names:tc:SAML:2.0:protocol">
    --     <saml2:Issuer xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion">https://authentication.stg.id.ubc.ca</saml2:Issuer>
    --     <ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
    --         <ds:SignedInfo>
    --             <ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
    --             <ds:SignatureMethod Algorithm="http://www.w3.org/2001/04/xmldsig-more#rsa-sha256"/>
    --             <ds:Reference URI="#_e4585eaeedbcaf7c24dff7f1ee2499f5">
    --                 <ds:Transforms>
    --                     <ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/>
    --                     <ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/>
    --                 </ds:Transforms>
    --                 <ds:DigestMethod Algorithm="http://www.w3.org/2001/04/xmlenc#sha256"/>
    --                 <ds:DigestValue>1ui22tqFyYEOoWI19CMwz4n+ynxNjLDGdTeRMdi60EU=</ds:DigestValue>
    --             </ds:Reference>
    --         </ds:SignedInfo>
    --         <ds:SignatureValue>ROg7FXV6vsp8socVhdo76/i7cRHGGKIveAiScKdujZT0QrHVqIvvbZ/RnwvEMJ9H9i/kJFAQA171
    -- 		Eo2kDjSdvNFQ/YcKaJUwMtAwT05yVatGV42RZKEf7ME+vpcCTR1LWZdrhat1FWCg1MNQwNWB0EL5
    -- 		fEP2a4jAcSTB8tFbjTAHsv7IWC39E5RVv99mACYXLa7iGZLtORANZxgYu5qQgmH6pUkI6Z1cpmf+
    -- 		m9mIjKM6LF0EvLfWOBWL6udZ+GsHPOLjVTJg+1S0xb9FQCYDVW1QhbjSS0icKHKTNNbrsaxllVDY
    -- 		m4q27YQjRh+XxugPgvsZ61Pxlto8Jbg+6jUlMQ==</ds:SignatureValue>
    --         <ds:KeyInfo>
    --             <ds:X509Data>
    --                 <ds:X509Certificate>MIIDTTCCAjWgAwIBAgIVAJccYyIV6wly8XyddumpgnHMJ2JLMA0GCSqGSIb3DQEBCwUAMCcxJTAj
    -- 			BgNVBAMMHGF1dGhlbnRpY2F0aW9uLnN0Zy5pZC51YmMuY2EwHhcNMTcwMzAxMTk1NDM0WhcNMzcw
    -- 			...
    -- 			xUuh6HuHKIwQqHBz7udxbH3Zbb6jXGDJjiDHt1LRJ8xbVisFIcDlIwsGQQi0HeEJfx4P</ds:X509Certificate>
    --             </ds:X509Data>
    --         </ds:KeyInfo>
    --     </ds:Signature>
    --     <saml2p:Status>
    --         <saml2p:StatusCode Value="urn:oasis:names:tc:SAML:2.0:status:Success"/>
    --     </saml2p:Status>
    --     <saml2:EncryptedAssertion xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion">
    --         <xenc:EncryptedData Id="_314d80b9cf02d8eda8d686a6ffd626cf" Type="http://www.w3.org/2001/04/xmlenc#Element" xmlns:xenc="http://www.w3.org/2001/04/xmlenc#">
    --             <xenc:EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#aes128-cbc" xmlns:xenc="http://www.w3.org/2001/04/xmlenc#"/>
    --             <ds:KeyInfo xmlns:ds="http://www.w3.org/2000/09/xmldsig#">
    --                 <xenc:EncryptedKey Id="_d7b6da6fb59a627ebb4a96928441ab79" Recipient="https://ubcdfe.deskflow-asp2.com" xmlns:xenc="http://www.w3.org/2001/04/xmlenc#">
    --                     <xenc:EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-oaep-mgf1p" xmlns:xenc="http://www.w3.org/2001/04/xmlenc#">
    --                         <ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" xmlns:ds="http://www.w3.org/2000/09/xmldsig#"/>
    --                     </xenc:EncryptionMethod>
    --                     <ds:KeyInfo>
    --                         <ds:X509Data>
    --                             <ds:X509Certificate>MIICuzCCAiQCCQD3bpigRnKMSzANBgkqhkiG9w0BAQsFADCBoTELMAkGA1UEBhMCQ0ExEDAOBgNV
    -- 				BAgMB09udGFyaW8xEDAOBgNVBAcMB1Rvcm9udG8xJjAkBgNVBAoMHVRhY3RpY2FsIEJ1c2luZXNz
    -- 				...
    -- 				kVRcHd1UK3q7G8FoykWjdQz/0EoMTfEZ+Md56mLOe48eMUZV2ONZuL1kDCEKw1UwkaDQI4Pf8pzx
    -- 				82b9rgw9wBDtvu5eFPlUGEGIBw==</ds:X509Certificate>
    --                         </ds:X509Data>
    --                     </ds:KeyInfo>
    --                     <xenc:CipherData xmlns:xenc="http://www.w3.org/2001/04/xmlenc#">
    --                         <xenc:CipherValue>BNHfUOpgdPE5BgpN2VIZIDthMAv1rxk91qVnWyCZOG9bmUKChJtTUqMpndot7VJwYuyKFshkAdnT
    -- 				D79KGdlSA1xHKcVeZXXzDWglqSyYjzhDCsyOhPaI4HelMFgCLwyFz89uEpUpqlvfl8ol3Am/XnzQ
    -- 				Vp7V7oS76hocjUI51Qs=</xenc:CipherValue>
    --                     </xenc:CipherData>
    --                 </xenc:EncryptedKey>
    --             </ds:KeyInfo>
    --             <xenc:CipherData xmlns:xenc="http://www.w3.org/2001/04/xmlenc#">
    --                 <xenc:CipherValue>R6l7tmbnXrOfBgB8lA3KnwLYsLH5ZO5omQ7Hp5K05atzw2o55xmCXVMYhNneFxMtxUh6raEyHeZX
    -- 			PTZNgWrvdqc4GYND/R7MhRrJzk9OAq1WyoOXwbtRpwNDwWA4N2IuprPQJbvjVxaw/PesZMZwZqlp
    -- 			...
    -- 			zm9zAxahyu8Ooe8M4r3HN2cY0JxxxkZtDiulbnyA+rRtXfBRJtangvFQ4iFAnzM/Yg9hMyW9jcu0
    -- 			S7FzuRB9ONMxi+nh0IFWgqp+</xenc:CipherValue>
    --             </xenc:CipherData>
    --         </xenc:EncryptedData>
    --     </saml2:EncryptedAssertion>
    -- </saml2p:Response>

    -- The sample encrypted SAML response and RSA private key are available online:
    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @sbSamlResponse int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSamlResponse OUT

    DECLARE @sbPrivateKeyPem int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPrivateKeyPem OUT

    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://chilkatdownload.com/data/samlresponse.xml', @sbSamlResponse
    IF @success = 1
      BEGIN
        EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://chilkatdownload.com/data/samlresponse_privkey.pem', @sbPrivateKeyPem
      END
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSamlResponse
        EXEC @hr = sp_OADestroy @sbPrivateKeyPem
        RETURN
      END

    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadSb', @success OUT, @sbSamlResponse, 1

    -- Load the RSA private key..
    DECLARE @privkey int
    -- Use "Chilkat_9_5_0.PrivateKey" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.PrivateKey', @privkey OUT

    EXEC sp_OAMethod @sbPrivateKeyPem, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @privkey, 'LoadPem', @success OUT, @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @privkey, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSamlResponse
        EXEC @hr = sp_OADestroy @sbPrivateKeyPem
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @privkey
        RETURN
      END

    -- Prepare an RSA object w/ the private key...
    DECLARE @rsa int
    -- Use "Chilkat_9_5_0.Rsa" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT

    EXEC sp_OAMethod @rsa, 'ImportPrivateKeyObj', @success OUT, @privkey
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSamlResponse
        EXEC @hr = sp_OADestroy @sbPrivateKeyPem
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @privkey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    -- RSA will be used to decrypt the xenc:EncryptedKey
    -- The bytes to be decrypted are in xenc:CipherValue (in base64 format)
    DECLARE @encryptedAesKey nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @encryptedAesKey OUT, 'saml2:EncryptedAssertion|xenc:EncryptedData|ds:KeyInfo|xenc:EncryptedKey|xenc:CipherData|xenc:CipherValue'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Encrypted AES key not found.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSamlResponse
        EXEC @hr = sp_OADestroy @sbPrivateKeyPem
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @privkey
        EXEC @hr = sp_OADestroy @rsa
        RETURN
      END

    PRINT 'Encrypted AES key (base64) = ' + @encryptedAesKey

    DECLARE @bdAesKey int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdAesKey OUT

    EXEC sp_OAMethod @bdAesKey, 'AppendEncoded', @success OUT, @encryptedAesKey, 'base64'

    DECLARE @sbRsaAlg int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRsaAlg OUT

    EXEC sp_OAMethod @xml, 'ChilkatPath', @sTmp0 OUT, 'saml2:EncryptedAssertion|xenc:EncryptedData|ds:KeyInfo|xenc:EncryptedKey|xenc:EncryptionMethod|(Algorithm)'
    EXEC sp_OAMethod @sbRsaAlg, 'Append', @success OUT, @sTmp0

    EXEC sp_OAMethod @sbRsaAlg, 'GetAsString', @sTmp0 OUT
    PRINT 'sbRsaAlg contains: ' + @sTmp0
    EXEC sp_OAMethod @sbRsaAlg, 'Contains', @iTmp0 OUT, 'rsa-oaep', 1
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OASetProperty @rsa, 'OaepPadding', 1
      END

    -- Note: The DecryptBd method is introduced in Chilkat v9.5.0.76
    EXEC sp_OAMethod @rsa, 'DecryptBd', @success OUT, @bdAesKey, 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rsa, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSamlResponse
        EXEC @hr = sp_OADestroy @sbPrivateKeyPem
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @privkey
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @bdAesKey
        EXEC @hr = sp_OADestroy @sbRsaAlg
        RETURN
      END


    EXEC sp_OAMethod @bdAesKey, 'GetEncoded', @sTmp0 OUT, 'hex'
    PRINT 'Decrypted AES key (hex) = ' + @sTmp0

    -- Get the encrypted XML (in base64) to be decrypted w/ the AES key.
    DECLARE @encrypted64 nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetChildContent', @encrypted64 OUT, 'saml2:EncryptedAssertion|xenc:EncryptedData|xenc:CipherData|xenc:CipherValue'
    EXEC sp_OAGetProperty @xml, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'Encrypted data not found.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSamlResponse
        EXEC @hr = sp_OADestroy @sbPrivateKeyPem
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @privkey
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @bdAesKey
        EXEC @hr = sp_OADestroy @sbRsaAlg
        RETURN
      END
    DECLARE @bdEncrypted int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdEncrypted OUT

    EXEC sp_OAMethod @bdEncrypted, 'AppendEncoded', @success OUT, @encrypted64, 'base64'

    -- Get the symmetric algorithm:  "http://www.w3.org/2001/04/xmlenc#aes128-cbc"
    -- and set the symmetric decrypt properties.
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    DECLARE @sbAlg int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbAlg OUT

    EXEC sp_OAMethod @xml, 'ChilkatPath', @sTmp0 OUT, 'saml2:EncryptedAssertion|xenc:EncryptedData|xenc:EncryptionMethod|(Algorithm)'
    EXEC sp_OAMethod @sbAlg, 'Append', @success OUT, @sTmp0
    EXEC sp_OAMethod @sbAlg, 'Contains', @iTmp0 OUT, 'aes128-cbc', 1
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'aes'
        EXEC sp_OASetProperty @crypt, 'KeyLength', 128
        EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
        -- The 1st 16 bytes of the encrypted data are the AES IV.
        EXEC sp_OAMethod @bdEncrypted, 'GetEncodedChunk', @sTmp0 OUT, 0, 16, 'hex'
        EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @sTmp0, 'hex'
        EXEC sp_OAMethod @bdEncrypted, 'RemoveChunk', @success OUT, 0, 16
      END
    -- Other algorithms, key lengths, etc, can be supported by checking for different Algorithm attribute values..

    EXEC sp_OAMethod @bdAesKey, 'GetEncoded', @sTmp0 OUT, 'hex'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @sTmp0, 'hex'

    -- AES decrypt...
    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @bdEncrypted
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @crypt, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbSamlResponse
        EXEC @hr = sp_OADestroy @sbPrivateKeyPem
        EXEC @hr = sp_OADestroy @xml
        EXEC @hr = sp_OADestroy @privkey
        EXEC @hr = sp_OADestroy @rsa
        EXEC @hr = sp_OADestroy @bdAesKey
        EXEC @hr = sp_OADestroy @sbRsaAlg
        EXEC @hr = sp_OADestroy @bdEncrypted
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @sbAlg
        RETURN
      END

    -- Get the decrypted XML
    DECLARE @decryptedXml nvarchar(4000)
    EXEC sp_OAMethod @bdEncrypted, 'GetString', @decryptedXml OUT, 'utf-8'

    PRINT 'Decrypted XML:'

    PRINT @decryptedXml

    -- The decrypted XML looks like this:

    -- <saml2:Assertion xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion" ID="_226e565c548db7986d165d7d969b48b4" IssueInstant="2018-10-11T17:46:20.727Z" Version="2.0">
    -- ...
    -- ...
    -- ...
    -- </saml2:Assertion>

    DECLARE @xmlAssertion int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlAssertion OUT

    EXEC sp_OAMethod @xmlAssertion, 'LoadXml', @success OUT, @decryptedXml

    -- Replace the saml2:EncryptedAssertion XML subtree with the saml2:Assertion XML.
    DECLARE @xmlEncryptedAssertion int
    EXEC sp_OAMethod @xml, 'FindChild', @xmlEncryptedAssertion OUT, 'saml2:EncryptedAssertion'
    EXEC sp_OAMethod @xmlEncryptedAssertion, 'SwapTree', @success OUT, @xmlAssertion
    EXEC @hr = sp_OADestroy @xmlEncryptedAssertion

    -- The decrypted XML assertion has now replaced the encrypted XML assertion.
    -- Examine the fully decrypted XML document:

    PRINT 'Full XML SAML document with decrypted assertion:'
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbSamlResponse
    EXEC @hr = sp_OADestroy @sbPrivateKeyPem
    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @privkey
    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @bdAesKey
    EXEC @hr = sp_OADestroy @sbRsaAlg
    EXEC @hr = sp_OADestroy @bdEncrypted
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sbAlg
    EXEC @hr = sp_OADestroy @xmlAssertion


END
GO