SQL Server
SQL Server
SOAP WS-Security UsernameToken
See more XML Examples
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 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
-- 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.
-- 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
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
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
EXEC @hr = sp_OACreate 'Chilkat.Prng', @prng OUT
DECLARE @bd int
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
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
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