Connecting to SQL 2005 through SOAP Native Web Services using SQL user credentials from a different domain

Just as normal connections to SQL server support SQL user credentials, when logging into SQL 2005 through Native Web Services SQL user credentials can be used as well.  This is done by utilizing the SOAP Headers functionality of SOAP.

Please see SQL Books Online under topic “SQL Server Authentication over SOAP” for instructions on how to update the client application.  This topic can be reached through the index by “Native XML Web Services” -> “SQL Server Authentication”.  At the end of the topic there is a reference to “Adding SOAP Headers to Client Applications”.  You will also find a link to sample client side code implementing the SQL 2005 supported SOAP headers within the “Adding SOAP Headers to Client Applications” topic.

Alternatively, you can use MSN Search for “SQL SOAP Server Authentication” to reach the same information online (https://msdn2.microsoft.com/en-us/library/ms180919.aspx).

Below outlines the SQL server configuration needed to enable this:

  • Install a valid SSL certificate that can be used to validate the server (this can be the same certificate used by IIS)
  • Create a local Windows user
  • Install SQL 2005 with SQL authentication support
  • Create a SQL user
  • Create a sample Stored Procedure
  • Create a sample SOAP endpoint with LOGIN_TYPE=MIXED
    • eg.
     CREATE ENDPOINT Sample_EP
         AS HTTP (
         SITE='*',
         PATH='/SQL/SqlAuth',
         AUTHENTICATION=(BASIC),
         PORTS=(SSL)
    )
    FOR SOAP 
         WEBMETHOD 'https://tempuri.org'.'echoString'(NAME='sampleDB.dbo.funcString'),
         LOGIN_TYPE = MIXED,
         WSDL=DEFAULT,
         SCHEMA = STANDARD
    )
  • Grant the Windows user login permissions to SQL server
    • EXEC sp_grantlogin @loginame = [machineName\userName]
  • Grant the Windows user access to the database containing the stored procedure
    • EXEC sp_grantdbaccess @loginame = [machineName\userName]
  • Grant the Windows user connect permission to the endpoint
    • GRANT CONNECT ON ENDPOINT::Sample_EP TO [machineName\userName]
  • Grant the Windows user execute permission on the stored procedure
    • GRANT EXEC on funcString to [machineName\userName]
  • Grant the SQL user access to the database containing the stored procedure
    • EXEC sp_grantdbaccess @loginame = [sqlUser]
  • Grant the SQL user connect permission to the endpoint
    • GRANT CONNECT ON ENDPOINT::Sample_EP TO [sqlUser]
  • Grant the SQL user execute permission on the stored procedure
    • GRANT EXEC on funcString to [sqlUser]

You can verify that the endpoint is working by using an web explorer tool (such as Internet Explorer) and type in the URL of the endpoint.  For example, https://myMachine.domain.com/sql/sqlauth?wsdl.  When prompted for credentials, specify the credentials of the Windows user.  If the endpoint is working, then you should see the WSDL document.  Please make sure that the Windows Firewall is not blocking the port (443).

Below outlines the client application configuration needed to send the SQL user credentials (assuming client application developed with Visual Studio 2005):

  • Create a C# project
  • Add Web Reference to the endpoint created above (https://myMachine.domain.com/sql/sqlauth?wsdl)
  • Using either Books Online or MSDN, save the code sample for SQL SOAP Header class mentioned above.  Direct link to the page below:
  • Add the SOAP Header class file you’ve just saved to the project
  • Add the following member variable (as mentioned in Books Online) to the class generated when the web reference was added:
    • public SqlSoapHeader.Security sqlSecurity;
  • Add the following method markup (as mentioned in Books Online) to each and every method you wish to have SQL user authentication support:
    • [System.Web.Services.Protocols.SoapHeaderAttribute("sqlSecurity")]
  • In the main execution code section, in between instantiating the web reference class and calling the web method, add the following code to set the credentials:
       proxy.sqlSecurity = new SqlSoapHeader.Security();
      proxy.sqlSecurity.MustUnderstand = true;
      proxy.sqlSecurity.Username = "sqlUser";
      proxy.sqlSecurity.Password = "pwd";
 
      System.Net.CredentialCache myCreds = new System.Net.CredentialCache();
      myCreds.Add(new Uri(proxy.Url), "Basic", new System.Net.NetworkCredential("userName", "pwd"));
      proxy.Credentials = myCreds;
    • Where “proxy” is the variable name of the web reference class

The end result of your client application would look something like:

       Sample_EP proxy = new Sample_EP();
      proxy.sqlSecurity = new SqlSoapHeader.Security();
      proxy.sqlSecurity.MustUnderstand = true;
      proxy.sqlSecurity.Username = "sqlUser";
      proxy.sqlSecurity.Password = "pwd";
 
      CredentialCache myCreds = new CredentialCache();
      myCreds.Add(new Uri(proxy.Url), "Basic", new NetworkCredential("userName", "pwd"));
      proxy.Credentials = myCreds;
 
      SqlString res = proxy.echoString(new SqlString("Hello World"));

For additional information regarding the various SOAP Headers SQL 2005 supports, please refer to Books Online topic “SQL Optional Headers”, which is reachable through the index “Native XML Web Services” -> “extension headers” or online at https://msdn2.microsoft.com/en-us/library/ms186402(en-US,SQL.90).aspx.

Jimmy Wu, SQL Server Protocols
Disclaimer: This posting is provided “AS IS” with no warranties, and confers no rights