(SQL Server) SOAP WS-Security UsernameToken

Demonstrates how to add a UsernameToken with the WSS SOAP Message Security header.

Note: This example requires Chilkat v9.5.0.66 or later.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat Crypt API to have been previously unlocked.
    -- See Unlock Chilkat Crypt for sample code.

    -- An HTTP SOAP request is an HTTP request where the SOAP XML composes the body.
    -- This example demonstrates how to add a WS-Security header such as the following:
    -- 
    -- <wsse:UsernameToken xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="SecurityToken-6138db82-5a4c-4bf7-915f-af7a10d9ae96">
    --   <wsse:Username>user</wsse:Username>
    --   <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordDigest">CBb7a2itQDgxVkqYnFtggUxtuqk=</wsse:Password>
    --   <wsse:Nonce>5ABcqPZWb6ImI2E6tob8MQ==</wsse:Nonce>
    --   <wsu:Created>2010-06-08T07:26:50Z</wsu:Created>
    -- </wsse:UsernameToken>
    -- 

    -- First build some simple SOAP XML that has some header and body.
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @xml, 'Tag', 'env:Envelope'
    DECLARE @success int
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:env', 'http://www.w3.org/2003/05/soap-envelope'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'env:Header|n:alertcontrol', 1, 'xmlns:n', 'http://example.org/alertcontrol'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'env:Header|n:alertcontrol|n:priority', '1'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'env:Header|n:alertcontrol|n:expires', '2001-06-22T14:00:00-05:00'
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'env:Body|m:alert', 1, 'xmlns:m', 'http://example.org/alert'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'env:Body|m:alert|m:msg', 'Pick up Mary at school at 2pm'
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'

    -- The following SOAP XML is built:

    -- 	<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
    -- 	 <env:Header>
    -- 	  <n:alertcontrol xmlns:n="http://example.org/alertcontrol">
    -- 	   <n:priority>1</n:priority>
    -- 	   <n:expires>2001-06-22T14:00:00-05:00</n:expires>
    -- 	  </n:alertcontrol>
    -- 	 </env:Header>
    -- 	 <env:Body>
    -- 	  <m:alert xmlns:m="http://example.org/alert">
    -- 	   <m:msg>Pick up Mary at school at 2pm</m:msg>
    -- 	  </m:alert>
    -- 	 </env:Body>
    -- 	</env:Envelope>
    -- 

    -- Now build the WSSE XML housing that we'll insert into the above SOAP XML at the end.

    -- 	<wsse:Security>
    -- 	  <wsse:UsernameToken xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="WSU_ID">
    -- 	    <wsse:Username>USERNAME</wsse:Username>
    -- 	    <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordDigest">PASSWORD_DIGEST</wsse:Password>
    -- 	    <wsse:Nonce>NONCE</wsse:Nonce>
    -- 	    <wsu:Created>CREATED</wsu:Created>
    -- 	  </wsse:UsernameToken>
    -- 	</wsse:Security>

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

    EXEC sp_OASetProperty @wsse, 'Tag', 'wsse:Security'
    EXEC sp_OAMethod @wsse, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken', 1, 'xmlns:wsu', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd'
    EXEC sp_OAMethod @wsse, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken', 1, 'wsu:Id', 'WSU_ID'
    EXEC sp_OAMethod @wsse, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Username', 'USERNAME'
    EXEC sp_OAMethod @wsse, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken|wsse:Password', 1, 'Type', 'http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordDigest'
    EXEC sp_OAMethod @wsse, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Password', 'PASSWORD_DIGEST'
    EXEC sp_OAMethod @wsse, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Nonce', 'NONCE'
    EXEC sp_OAMethod @wsse, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsu:Created', 'CREATED'
    EXEC sp_OAMethod @wsse, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'

    -- Insert the wsse:Security XML into the existing SOAP header:
    DECLARE @xHeader int
    EXEC sp_OAMethod @xml, 'GetChildWithTag', @xHeader OUT, 'env:Header'
    EXEC sp_OAMethod @xHeader, 'AddChildTree', @success OUT, @wsse
    EXEC @hr = sp_OADestroy @xHeader

    -- Now show the SOAP XML with the wsse:Security header added:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'

    -- Now our XML looks like this:
    -- 	<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
    -- 	    <env:Header>
    -- 	        <n:alertcontrol xmlns:n="http://example.org/alertcontrol">
    -- 	            <n:priority>1</n:priority>
    -- 	            <n:expires>2001-06-22T14:00:00-05:00</n:expires>
    -- 	        </n:alertcontrol>
    -- 	        <wsse:Security>
    -- 	            <wsse:UsernameToken xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="WSU_ID">
    -- 	                <wsse:Username>USERNAME</wsse:Username>
    -- 	                <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordDigest">PASSWORD_DIGEST</wsse:Password>
    -- 	                <wsse:Nonce>NONCE</wsse:Nonce>
    -- 	                <wsu:Created>CREATED</wsu:Created>
    -- 	            </wsse:UsernameToken>
    -- 	        </wsse:Security>
    -- 	    </env:Header>
    -- 	    <env:Body>
    -- 	        <m:alert xmlns:m="http://example.org/alert">
    -- 	            <m:msg>Pick up Mary at school at 2pm</m:msg>
    -- 	        </m:alert>
    -- 	    </env:Body>
    -- 	</env:Envelope>
    -- 

    -- -----------------------------------------------------
    -- Now let's fill-in-the-blanks with actual information...
    -- -----------------------------------------------------

    DECLARE @wsu_id nvarchar(4000)
    SELECT @wsu_id = 'Example-1'
    EXEC sp_OAMethod @wsse, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken', 1, 'wsu:Id', @wsu_id

    DECLARE @password nvarchar(4000)
    SELECT @password = 'password'
    DECLARE @username nvarchar(4000)
    SELECT @username = 'user'
    EXEC sp_OAMethod @wsse, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Username', @username

    -- The nonce should be 16 random bytes.
    DECLARE @prng int
    -- Use "Chilkat_9_5_0.Prng" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT

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

    -- Generate 16 random bytes into bd.
    -- Note: The GenRandomBd method is added in Chilkat v9.5.0.66
    EXEC sp_OAMethod @prng, 'GenRandomBd', @success OUT, 16, @bd

    DECLARE @nonce nvarchar(4000)
    EXEC sp_OAMethod @bd, 'GetEncoded', @nonce OUT, 'base64'
    EXEC sp_OAMethod @wsse, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Nonce', @nonce

    -- Get the current date/time in a string with this format: 2010-06-08T07:26:50Z
    DECLARE @dt int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dt OUT

    EXEC sp_OAMethod @dt, 'SetFromCurrentSystemTime', @success OUT
    DECLARE @bLocal int
    SELECT @bLocal = 0
    DECLARE @created nvarchar(4000)
    EXEC sp_OAMethod @dt, 'GetAsTimestamp', @created OUT, @bLocal
    EXEC sp_OAMethod @wsse, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsu:Created', @created

    -- The password digest is calculated like this:
    -- Password_Digest = Base64 ( SHA-1 ( nonce + created + password ) )
    EXEC sp_OAMethod @bd, 'AppendString', @success OUT, @created, 'utf-8'
    EXEC sp_OAMethod @bd, 'AppendString', @success OUT, @password, 'utf-8'

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

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'SHA-1'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    -- Note: The HashBdENC method is added in Chilkat v9.5.0.66
    DECLARE @passwordDigest nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'HashBdENC', @passwordDigest OUT, @bd
    EXEC sp_OAMethod @wsse, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Password', @passwordDigest

    -- Examine the final SOAP XML with WS-Security header added.
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @wsse
    EXEC @hr = sp_OADestroy @prng
    EXEC @hr = sp_OADestroy @bd
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @crypt


END
GO