SQL Server
SQL Server
Decrypt a SAML Response
See more Encryption Examples
Demonstrates how to decrypt a SAML response.Chilkat SQL Server Downloads
-- 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
-- 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
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @sbSamlResponse int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbSamlResponse OUT
DECLARE @sbPrivateKeyPem int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPrivateKeyPem OUT
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 = 0
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
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
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 = 0
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
EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT
EXEC sp_OAMethod @rsa, 'UsePrivateKey', @success OUT, @privkey
IF @success = 0
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
EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdAesKey OUT
EXEC sp_OAMethod @bdAesKey, 'AppendEncoded', @success OUT, @encryptedAesKey, 'base64'
DECLARE @sbRsaAlg int
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, 'PkcsPadding', 0
END
EXEC sp_OAMethod @rsa, 'DecryptBd', @success OUT, @bdAesKey, 1
IF @success = 0
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
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
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT
DECLARE @sbAlg int
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 = 0
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
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