(SQL Server) SOAP WS-Security Username Authentication

Demonstrates creating SOAP XML for WS-Security Username Authentication. The client user name and password are encapsulated in a WS-Security <wsse:UsernameToken>.

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)
    -- Generate this XML with the code that follows:

    -- 	<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    -- 	 <soap:Header>	     
    -- 	  <wsse:Security xmlns:wsse="http://schemas.xmlsoap.org/ws/2003/06/secext">
    -- 	   <wsse:UsernameToken wsu:Id="sample" 
    -- 	       xmlns:wsu="http://schemas.xmlsoap.org/ws/2003/06/utility">
    -- 	    <wsse:Username>sample</wsse:Username>
    -- 	    <wsse:Password Type="wsse:PasswordText">oracle</wsse:Password>
    -- 	    <wsu:Created>2004-05-19T08:44:51Z</wsu:Created>
    -- 	   </wsse:UsernameToken>
    -- 	  </wsse:Security>
    -- 	  <wsse:Security soap:actor="oracle" 
    -- 	      xmlns:wsse="http://schemas.xmlsoap.org/ws/2003/06/secext">
    -- 	   <wsse:UsernameToken wsu:Id="oracle" 
    -- 	       xmlns:wsu="http://schemas.xmlsoap.org/ws/2003/06/utility">
    -- 	    <wsse:Username>myUsername</wsse:Username>
    -- 	    <wsse:Password Type="wsse:PasswordText">myPassword</wsse:Password>
    -- 	    <wsu:Created>2004-05-19T08:46:04Z</wsu:Created>
    -- 	   </wsse:UsernameToken>
    -- 	  </wsse:Security>
    -- 	 </soap:Header>
    -- 	  <soap:Body>
    -- 	   <getHello xmlns="http://www.oracle.com"/>
    -- 	  </soap:Body>
    -- 	</soap:Envelope>
    -- 

    -- First build the outer housing:
    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', 'soap:Envelope'
    DECLARE @success int
    EXEC sp_OAMethod @xml, 'AddAttribute', @success OUT, 'xmlns:soap', 'http://schemas.xmlsoap.org/soap/envelope/'
    EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'soap:Header', ''
    EXEC sp_OAMethod @xml, 'UpdateAttrAt', @success OUT, 'soap:Body|getHello', 1, 'xmlns', 'http://www.oracle.com'

    -- Get the current date/time in a string with this format: 2004-05-19T08:46:04Z
    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

    -- Now build each UsernameToken block:
    DECLARE @wsse1 int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @wsse1 OUT

    EXEC sp_OASetProperty @wsse1, 'Tag', 'wsse:Security'
    EXEC sp_OAMethod @wsse1, 'AddAttribute', @success OUT, 'xmlns:wsse', 'http://schemas.xmlsoap.org/ws/2003/06/secext'
    EXEC sp_OAMethod @wsse1, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken', 1, 'wsu:Id', 'sample'
    EXEC sp_OAMethod @wsse1, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken', 1, 'xmlns:wsu', 'http://schemas.xmlsoap.org/ws/2003/06/utility'
    EXEC sp_OAMethod @wsse1, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Username', 'sample'
    EXEC sp_OAMethod @wsse1, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken|wsse:Password', 1, 'Type', 'wsse:PasswordText'
    EXEC sp_OAMethod @wsse1, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Password', 'oracle'
    EXEC sp_OAMethod @wsse1, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsu:Created', @created

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

    EXEC sp_OASetProperty @wsse2, 'Tag', 'wsse:Security'
    EXEC sp_OAMethod @wsse2, 'AddAttribute', @success OUT, 'soap:actor', 'oracle'
    EXEC sp_OAMethod @wsse2, 'AddAttribute', @success OUT, 'xmlns:wsse', 'http://schemas.xmlsoap.org/ws/2003/06/secext'
    EXEC sp_OAMethod @wsse2, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken', 1, 'wsu:Id', 'oracle'
    EXEC sp_OAMethod @wsse2, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken', 1, 'xmlns:wsu', 'http://schemas.xmlsoap.org/ws/2003/06/utility'
    EXEC sp_OAMethod @wsse2, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Username', 'oracle'
    EXEC sp_OAMethod @wsse2, 'UpdateAttrAt', @success OUT, 'wsse:UsernameToken|wsse:Password', 1, 'Type', 'wsse:PasswordText'
    EXEC sp_OAMethod @wsse2, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsse:Password', 'oracle'
    EXEC sp_OAMethod @wsse2, 'UpdateChildContent', NULL, 'wsse:UsernameToken|wsu:Created', @created

    -- Insert the UsernameToken blocks:
    DECLARE @xHeader int
    EXEC sp_OAMethod @xml, 'GetChildWithTag', @xHeader OUT, 'soap:Header'
    EXEC sp_OAMethod @xHeader, 'AddChildTree', @success OUT, @wsse1
    EXEC sp_OAMethod @xHeader, 'AddChildTree', @success OUT, @wsse2
    EXEC @hr = sp_OADestroy @xHeader

    -- Show the XML:
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @xml
    EXEC @hr = sp_OADestroy @dt
    EXEC @hr = sp_OADestroy @wsse1
    EXEC @hr = sp_OADestroy @wsse2


END
GO