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

  • Article

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

Comments

  • Anonymous
    December 22, 2005
    The comment has been removed

  • Anonymous
    January 16, 2006
    The comment has been removed

  • Anonymous
    August 17, 2006
    I am not using SSL connection in the endpoint.
    Will the above code work for me if i use Integrated instead of basic ?

  • Anonymous
    August 18, 2006
    The comment has been removed

  • Anonymous
    September 02, 2006
    Can You please suggest a way to access SOAP endpoints through browser HTTP GET directly so that it will display the results in the browser instead of going through a ASP.NET or winforms..
    Thanks in Advance

    Gk.Sezhian

  • Anonymous
    September 05, 2006
    The comment has been removed

  • Anonymous
    September 11, 2006
    The comment has been removed

  • Anonymous
    September 12, 2006
    Yes, strange but true!  With SQL SOAP you always need to log in using NT account first, even if you want to use SQL account.

    So one way to do this is:

    1. Create endpoint using SSL and basic auth ->

    create endpoint ssl_basic_mixed
    state=STARTED
    as http (path='/ssl_basic_mixed',authentication=(basic),ports=(ssl),site='*')
    for soap (batches=enabled,wsdl=default,login_type=mixed,database='MySoapDb',schema=standard)
    go

    2. Setup a local NT account (call it SoapUser) and then grant this account access to the endpoint.

    grant connect on endpoint ::ssl_basic_mixed to [mmyComputersoapUser]

    3, Then from client connect using SSL, use basic auth to pass in SoapUser's password, then use the SQL Server account in the SOAP header to log in (like .NET example below):

    private void soapBasicStandardLoginTest()
    {
    soapclient.endpoint soapClient = new soapclient.endpoint();
    soapClient.Url = "https://" + soapServer + "/ssl_basic_mixed";
    CredentialCache credCache = new CredentialCache();
    NetworkCredential netCred = new NetworkCredential("soapUser", "MyNtPassword!", "MyComputer");
    credCache.Add(new Uri(soapClient.Url), "Basic", netCred);
    soapClient.Credentials  = credCache;

    soapClient.sqlSecurity = new SoapStress.Security();
    soapClient.sqlSecurity.Username = "MySQLStandardLogin";
    soapClient.sqlSecurity.Password = "MySQLPassword!";

    try
    {
    SqlInt32 result = soapClient.multiply(2, 2);
    log(result.ToString());
    }
    catch (Exception ex)
    {
    log(ex.Message);
    }
    }

    Matt

  • Anonymous
    September 13, 2006
    Thanks a lot ..i was able to implement it but with the windows-sql server login

  • Anonymous
    October 30, 2006
    i follow the steps, it's fine. but i have some questions, i'l b appreciated if u would answer me. for security reason, i don wan the NT account (SoapUser for ur example) to have access to my sql server and database, can i grant only the permission to the endpoint for SoapUser?

  • Anonymous
    October 31, 2006
    The comment has been removed

  • Anonymous
    November 13, 2006
    The comment has been removed

  • Anonymous
    November 13, 2006
    The comment has been removed

  • Anonymous
    November 27, 2006
    The comment has been removed

  • Anonymous
    January 16, 2007
    Hi   This is Sezhian.gk.   I wish to know whether native xml webservices are accessible from linux .For eg) using java / perl running on linux.If means can you please give some references. I tried to access an endpoint using linux from a perl script but it failed stating 401 Unauthorized.I used NT login which is also a sql server login to pass from linux.

  • Anonymous
    January 16, 2007
    Is it possible to access the wsdl document from a remote machine using the sql server user and not using NT-sql server user. Iam able to access using NT-sql server user BUT NOT THROUGH AN SQLUSER who has access permission set for endpoints. Thanks and Regards Sezhian.gk

  • Anonymous
    January 17, 2007
    The comment has been removed

  • Anonymous
    January 17, 2007
    Thanks Jimmy! rgds SEzhian.gk

  • Anonymous
    March 29, 2007
    is it possible to have a table/place to log every successful attempt to the web service? i might need to keep track when, what, who attempted my endpoint in my database.

  • Anonymous
    March 30, 2007
    The comment has been removed

  • Anonymous
    May 12, 2007
    I am trying to do this with an ASP.Net application which does not generate a new class when the web reference is added. What do I need to do differently? Thanks, Brent

  • Anonymous
    May 14, 2007
    The comment has been removed

  • Anonymous
    May 17, 2007
    I'll preface this by saying I am very new to C# and ASP.Net, I am a db developer. I've implemented a class (WSData) for my SQL2005 Endpoints and throughout my application when I want to use them I declare a variable like: WSData ws = new WSData(); and then call my methods like: ws.functions.methodname(param1, param2) My wsdl is fine and contained in: App_Webreferencesaspendpointnamesqlpath.wsdl It all works fine with integrated security and clear ports using: System.Net.CredentialCache.DefaultCredentials But with Basic and SSL I keep getting 403:Forbidden. I assume I am not getting my my credentials into the SOAP header but the solution infers that there is a reference class which doesn't seem to exist with asp, only the wsdl file. My Class is below, any help you can provide is greatly appreciated, I am stumped. Sincerely, Brent using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Net; /// <summary> /// Summary description for WSData /// </summary> [Serializable] public class WSData {    private aspendpointname.sqlendpointname _ws = new aspendpointname.sqlendpointname();    public SqlSoapHeader.Security sqlSecurity = new SqlSoapHeader.Security(); public WSData() {        //this works:        //_ws.Credentials = System.Net.CredentialCache.DefaultCredentials;        //my attempt at Basic/SSL which does not work:        sqlSecurity.MustUnderstand = true;        sqlSecurity.Username = "sqluser";        sqlSecurity.Password = "sqlpassword";        CredentialCache myCreds = new CredentialCache();        myCreds.Add(new Uri(_ws.Url), "Basic", new NetworkCredential("windowsuser", "windowspassword"));        _ws.Credentials = myCreds; }    public aspendpointname.sqlendpointname functions    {        get        {            return _ws;        }    } }

  • Anonymous
    May 18, 2007
    The comment has been removed

  • Anonymous
    May 19, 2007
    In a VS2005 website there is no generated class when a web reference is added. It somehow just uses the wsdl and discomap files in the App_WebReferences folder. When I add a web reference in a windows application it does generate a Reference.cs file so the provided solution can be implemented but when I add the web reference in a website there is no class generated so I have nothing to add the member variable to and nothing to add the method markup to. What am I missing?

  • Anonymous
    May 22, 2007
    The comment has been removed

  • Anonymous
    June 02, 2007
    Thanks for the response, however this produces a 401:Unauthorized. Judging by earlier postings I assume this is because I am not sending the NT user / password with the request which seems necessary. Any thoughts? Thanks, Brent

  • Anonymous
    June 04, 2007
    The comment has been removed

  • Anonymous
    June 07, 2007
    The comment has been removed

  • Anonymous
    June 08, 2007
    The comment has been removed

  • Anonymous
    July 02, 2007
    The comment has been removed

  • Anonymous
    July 03, 2007
    The comment has been removed

  • Anonymous
    July 06, 2007
    The comment has been removed

  • Anonymous
    December 21, 2007
    The comment has been removed

  • Anonymous
    December 23, 2007
    I followed all steps and got success in each steps except in ssl certificate step. SSL certificate gets added successfully in the website but only an error like "the name on the certificate does not match with the web site name" after this error i created a ssl cert. with same name inside the certificate and try to consume the created end point remotely or locally but the exception remains there. I gave add to the popup window for security/settings, i am able to access the webmethod of the endpoint but it generates an exception like "the ssl certificate of server is not compatible with the procedure of consuming secure end point". Please try to resolve my problem...  

  • Anonymous
    December 25, 2007
    Now I am getting this exception "the underlying connection was closed: could not establish trust relationship for the ssl/tls secure channel" on trying to get data through published website from sql server 2005 using ssl-integrated-mixed login type http end point. Help plz... Thanks in advance -Radha Krishna Prasad

  • Anonymous
    December 27, 2007
    Hi Ashis, By default, when you specify the "PORTS=(ssl)", SQL Server will attempt to bound to port 443. It is possible that IIS or another application is already listening on port 443.  As the message suggested, you can run "netstat" from the command prompt to see which process is already listening on which port. C:>netstat -ano Active Connections  Proto  Local Address          Foreign Address        State           PID  TCP    0.0.0.0:80             0.0.0.0:0              LISTENING       4  TCP    0.0.0.0:445            0.0.0.0:0              LISTENING       4 Once you know which application is already listening on port 443, you can either stop that application or change the SQL Server endpoint to listen on a different port.  To specify a non-default port to listen on you will need to add "SSL_PORT" to the Endpoint syntax.  So the endpoint syntax will look like: CREATE ENDPOINT prcjaj        AS HTTP (        SITE='*',        PATH='/JAJ',        AUTHENTICATION=(BASIC),        PORTS=(ssl), SSL_PORT=1000   )   FOR SOAP (WEBMETHOD 'http://tempuri.org'.'prcjaj'(NAME='JAJ.dbo.prcjaj'),        LOGIN_TYPE = MIXED,        WSDL=DEFAULT,        SCHEMA = STANDARD   ) HTH, Jimmy

  • Anonymous
    December 27, 2007
    The comment has been removed

  • Anonymous
    December 28, 2007
    The comment has been removed

  • Anonymous
    December 31, 2007
    The comment has been removed

  • Anonymous
    January 02, 2008
    The comment has been removed

  • Anonymous
    January 03, 2008
    The comment has been removed

  • Anonymous
    January 03, 2008
    The comment has been removed

  • Anonymous
    January 04, 2008
    The comment has been removed

  • Anonymous
    January 04, 2008
    The comment has been removed

  • Anonymous
    January 09, 2008
    The comment has been removed

  • Anonymous
    March 20, 2008
    Hello, I've followed the steps as outlined above. When when I test the web service using IE (by typing https://localhost/...?wsdl just so I get the description back), the browser asks me for a username and password. I supply the windows user/pasword that I've set up, but I get back a 403 error (not authorized) any Ideas on how to fix this? Thank you. Gustavo

  • Anonymous
    October 13, 2008
    The comment has been removed

  • Anonymous
    October 14, 2008
    The comment has been removed

  • Anonymous
    November 30, 2008
    From reading through these posts, it seems that a stand alone Java program may not be able to POST across the LAN to a SQL2005 endpoint, that is setup for NTLM.  Let me know if that is correct. I have been trying to make this work, and was optimistic when I was able to view the WSDL page across the LAN by adding the Autenticator Class/code to my test program, and setting credentials in it.  However, that uses the url.getcontent() method instead of the url.openconnection(), and write methods used for POST.  Getting a java.io.IOException: Server returned HTTP response code: 500. If it should be possible to do the POST, do you have suggestions, on how to trap the full HTTP trafic being posted?  I don't seem to be getting logged in the HTTPERR logfile.