Jaa

SQL Server Authentication over SOAP

  • Artikkeli

For Native XML Web Services in SQL Server 2005, security can be managed by using either of the following types of security principals:

  • Windows user or group accounts, or both
  • SQL Server logins

When Windows accounts are used for HTTP endpoint authentication, additional SOAP authentication headers are not required. For more information about HTTP endpoint authentication, see Endpoint Authentication Types and GRANT Endpoint Permissions (Transact-SQL).

However, when SQL Server logins are used, the client application must additionally implement Web Services Security (WS-Security) headers for SOAP authentication to include and submit SQL Server login information to the server.

For SOAP authentication of SQL Server logins, any HTTP authentication type can be used. Additionally, the user account that is used to authenticate at the HTTP layer only requires Windows access to the local server, and does not necessarily require a SQL Server login.

Note

For SQL Server-based authentication over SOAP, the LOGIN_TYPE option for the endpoint must be configured for mixed mode (LOGIN_TYPE=MIXED). Operating an endpoint in mixed mode also requires that the endpoint be configured to provide service over a Secure Sockets Layer (SSL) channel. For more information, see CREATE ENDPOINT (Transact-SQL).

Using WS-Security Headers to Authenticate a SQL Server Login (Mixed Mode only)

WS-Security is a proposed specification for extending the SOAP message structure to help secure Web services. Specifically, for SQL Server 2005, WS-Security can be used as needed to provide an authentication mechanism where credentials and tokens can be passed between SOAP clients and the instance of SQL Server directly within the SOAP message, by extending its structure.

If the following conditions are true, you can use WS-Security headers to supply SQL Server user and password information as part of your SOAP client application:

  • The instance of SQL Server 2005 has been installed in mixed mode. This means both Windows and SQL Server logins are supported.
  • The endpoint has the following settings:
    • LOGIN_TYPE = MIXED.
    • PORTS=(SSL), an SSL type port was specified.
  • Endpoint permissions have been granted to users that are not part of Windows login, such as SQL Server logins.

When a SOAP request contains a WS-Security header, credentials in the header override any credentials that were used when HTTP-based authentication was performed for the endpoint.

Using the WS-Security Header for SQL Server Authentication

The WS-Security header can be used in SOAP requests sent to an instance of SQL Server 2005 to support the following:

  • Transporting SQL Server authorization (SQL-Auth) credentials
  • Handling an expired SQL Server password

Transporting SQL-Auth Credentials

The following example shows how the WS-Security header can be used in a SOAP request to an instance of SQL Server 2005 to transport SQL Server authorization (SQL-Auth) credentials.

<SOAP-ENV:Header>
            <wsse:Security  xmlns:wsse=
                        "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
            <wsse:UsernameToken>
                        <wsse:Username>JohnDoe</wsse:Username>
            <wsse:Password Type=
                        "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">pass-word1</wsse:Password>
            </wsse:UsernameToken>
            </wsse:Security>
</SOAP-ENV:Header>

In this example, the <wsse:UserNameToken> element is used to contain the following two subelements:

  • <wsse:Username> element that has the SQL Server user name.
  • <wsse:Password> element that has its optional Type attribute set to a value of "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText".This is used to contain the text of the password for the user.

Note

When mixed-mode is supported for SQL Server 2005 and the HTTP SOAP endpoint, an SSL port is required to provide at least nominal encryption of the credentials of the user.

Handling an Expired SQL Server Password

SQL Server 2005 provides the password expiration feature. When a server determines during a login attempt that the password for the user has expired, the password feature requires the client to respond with both the old password for the user and the new password to successfully complete the login process.

To update the client to support this feature, you can complete this process by using the following WS-Security header as a template example.

<SOAP-ENV:Header>
 <wsse:Security  xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
            <wsse:UsernameToken>
                        <wsse:Username>JohnDoe</wsse:Username>
            <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">pass-word2</wsse:Password>
            <sql:OldPassword Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText" xmlns:sql="https://schemas.microsoft.com/sqlserver/2004/SOAP">pass-word1</sql:OldPassword>
            </wsse:UsernameToken>
 </wsse:Security>
</SOAP-ENV:Header>

Changing a password can also be performed as part of a SOAP session. The request to change the password will be honored only when a new session is initiated.

For example, to change the password as part of a new SOAP session, add the following to the <SOAP-ENV:Header> section:

<sqloptions:sqlSession xmlns:sqloptions="https://schemas.microsoft.com/sqlserver/2004/SOAP/Options" initiate="true"/>

For more information, see Working with SOAP Sessions.

Also, the <wsse:UserNameToken> element is slightly modified to contain the following three subelements with minor differences from the previous example for sending user credentials:

  • <wsse:Username> element that has the SQL Server user name.
  • <wsse:Password> element that has its optional Type attribute set to a value of "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText". This is used to contain the text of the new password for the user.
  • <sql:OldPassword> element that has its optional Type attribute set to a value of "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText"". This is used to contain the text of the old password for the user.

XML Schema for WS-Security Header

The following is a schema fragment for the WS-Security header for implementation only with SQL Server 2005.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="https://schemas.microsoft.com/sqlserver/2004/SOAP" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
  <xsd:import namespace="https://schemas.microsoft.com/sqlserver/2004/SOAP" />
  <xsd:annotation>
    <xsd:documentation xml:lang="en">(c) Copyright 2004, Microsoft Corporation The following schema for Microsoft SQL Server is presented in XML format and is for informational purposes only. Microsoft Corporation ("Microsoft") may have trademarks, copyrights, or other intellectual property rights covering subject matter in the schema. Microsoft does not make any representation or warranty regarding the schema or any product or item developed based on the schema. The schema is provided to you on an AS IS basis. Microsoft disclaims all express, implied and statutory warranties, including but not limited to the implied warranties of merchantability, fitness for a particular purpose, and freedom from infringement. Without limiting the generality of the foregoing, Microsoft does not make any warranty of any kind that any item developed based on the schema, or any portion of the schema, will not infringe any copyright, patent, trade secret, or other intellectual property right of any person or entity in any country. It is your responsibility to seek licenses for such intellectual property rights where appropriate. MICROSOFT SHALL NOT BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS), PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF SUCH DAMAGES.</xsd:documentation> 
  </xsd:annotation>
  <xsd:element name="Security">
    <xsd:complexType>
      <xsd:sequence minOccurs="1" maxOccurs="1">
        <xsd:element name="UsernameToken">
          <xsd:complexType>
            <xsd:sequence minOccurs="1" maxOccurs="1">
              <xsd:element name="Username" type="xsd:string" />
              <xsd:element name="Password">
                <xsd:complexType>
                  <xsd:simpleContent>
                    <xsd:extension base="xsd:string">
                      <xsd:attribute name="Type" type="xsd:anyURI" />
                    </xsd:extension>
                  </xsd:simpleContent>
                </xsd:complexType>
              </xsd:element>
              <xsd:element name="OldPassword" type="sql:OldPassword" minOccurs="0" />
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="https://schemas.microsoft.com/sqlserver/2004/SOAP" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="https://schemas.microsoft.com/sqlserver/2004/SOAP">
  <xsd:annotation>
    <xsd:documentation xml:lang="en">(c) Copyright 2004, Microsoft Corporation The following schema for Microsoft SQL Server is presented in XML format and is for informational purposes only. Microsoft Corporation ("Microsoft") may have trademarks, copyrights, or other intellectual property rights covering subject matter in the schema. Microsoft does not make any representation or warranty regarding the schema or any product or item developed based on the schema. The schema is provided to you on an AS IS basis. Microsoft disclaims all express, implied and statutory warranties, including but not limited to the implied warranties of merchantability, fitness for a particular purpose, and freedom from infringement. Without limiting the generality of the foregoing, Microsoft does not make any warranty of any kind that any item developed based on the schema, or any portion of the schema, will not infringe any copyright, patent, trade secret, or other intellectual property right of any person or entity in any country. It is your responsibility to seek licenses for such intellectual property rights where appropriate. MICROSOFT SHALL NOT BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS), PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF SUCH DAMAGES.</xsd:documentation> 
  </xsd:annotation>
  <xsd:complexType name="OldPassword">
    <xsd:simpleContent>
      <xsd:extension base="xsd:string">
        <xsd:attribute name="Type" type="xsd:anyURI" />
      </xsd:extension>
    </xsd:simpleContent>
  </xsd:complexType>
</xsd:schema>

See Also

Reference

Adding SOAP Headers to Client Applications
Endpoint Authentication Types

Other Resources

Authentication Mode

Help and Information

Getting SQL Server 2005 Assistance