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 (http://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 ‘http://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 http://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 (50)

  1. wangzhongtao says:

    hi

    i done it step by step as you have told me,

    but in outlines "add web service reference in vs2005", i had a error

    "The underlying connection was closed: An unexpected error occurred on a receive.

    m_safeCertContext is an invalid handle"

    can you help me? thanks!

  2. SQL Protocols says:

    Please try using a web browser (like IE) to retrieve the WSDL document (https://FullServerName/sql/Sqlauth?wsdl). If that works, then there’s a problem with Visual Studio. In this case, you can try saving the WSDL document through IE and when adding the web reference in VS, point to the path of the saved WSDL document.

    If the web browser can not retrieve the WSDL document, there may be an issue with the SSL certificate you are using or maybe the "HTTP SSL" OS service is not running. You can verify if the "HTTP SSL" service is running or not by opening up a command prompt and running "net start HTTP SSL". If it is a SSL certificate issue, in general IE will show a warning message.

    Please reply with your observations.

    Jimmy Wu

  3. Soumini Varma says:

    I am not using SSL connection in the endpoint.

    Will the above code work for me if i use Integrated instead of basic ?

  4. SQL Protocols says:

    Soumini,

    If you would like to login to SQL server using SQL user credentials, then you must use SSL.  If you would like to login to SQL server using Windows user credentials then you can connect over clear port and Integrated authentication.

    Please see http://blogs.msdn.com/sql_protocols/archive/2005/10/18/482315.aspx

    for details on how the client code will look like.

    Jimmy Wu

  5. sezkar@gmail.com says:

    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

  6. SQL Protocols says:

    Gk.Sezhian,

    SQL Server 2005 Native Web Services does not support accessing the web methods through HTTP GET requests.  To access the web methods, you must send HTTP POST requests.  Currently, only the WSDL document for the endpoint can be retrieved through HTTP GET requests.

    We are investigating supporting accessing web methods through HTTP GET requests in a future release.  Thank you for your feedback.

    Jimmy Wu

  7. sezhian .gk says:

    Hi Jimmy

      Sorry for disturbing u again.

    I have a query

    For making a client to use soap to access endpoint is it required to create him in the windows login also ? is it .

    Does a plain sql sever user with endpoint access crentials cant access with basic authentiaction and ssl is it ? Does he also needs to be a Windows machine User ?

    I created a sql server login user but found wsdl page access is failing.

    If replied it willbe helpful

  8. Matt Neerincx [MSFT] says:

    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

  9. sezhian .gk says:

    Thanks a lot ..i was able to implement it but with the windows-sql server login

  10. tkh says:

    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?

  11. SQL Protocols says:

    quote from tkh post:

    "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?"

    If I understand the question correctly, you want to only allow "SoapUser" connect rights to the endpoint, but revoke rights to execute the stored procedures and database access.

    Granting a NT user account access to SQL server is only needed to retrieve the WSDL document.  For normal SOAP requests, you will need to supply a valid NT user credential at the HTTP request level, but the actual SQL Server login is based on the SOAP Security Header user info.  So,once you’ve retrieved the WSDL document, or if you do not need to retrieve the WSDL document at all, you can revoke access for the NT user account.  In this scenario only SQL users accounts with specific GRANT CONNECT permissions and members of the SQL sysadmin role (which in general includes NT administrators group) will have access to the endpoint.  This will allow for the scenario you are looking for, where the NT user (SoapUser) does not have access to SQL Server.

    Note: It is recommended to use NT user accounts whenever possible.

    Jimmy

  12. Reinfried says:

    Hi Jimmy,

    all works perfect with a windows application (Net Framework 2.0). Now I have created a Windows Mobile 5.0 application (Net Compact Framework 2.0 SP1) at the same way.

    But now, when I call a webmethod from the endpoint, my application returns an Timeout-Error.

    With a endpoint without SSL (AUTHENTICATION = NTLM) the Windows Mobile 5.0 application works perfect.

    Thank for your feedback.

    Reinfried

  13. Reinfried says:

    Hi Jimmy,

    all works perfect with a windows application (Net Framework 2.0). Now I have created a Windows Mobile 5.0 application (Net Compact Framework 2.0 SP1) at the same way.

    But now, when I call a webmethod from the endpoint, my application returns an Timeout-Error.

    With a endpoint without SSL (AUTHENTICATION = NTLM) the Windows Mobile 5.0 application works perfect.

    Thank for your feedback.

    Reinfried

  14. SQL Protocols says:

    Hi Reinfried,

    Sounds like you may be having SSL cert validation issue.  Are you testing the .Net CF application using the Visual Studio IDE emulator or on a real PDA?

    BTW, .Net Compact Frameworks 2.0 is not 100% equivalent with .Net Frameworks 2.0.  As such, I believe there are some minor incompatibilities between SQL Server Natvie Web Services and .Net CF 2.0.  If you can mention what the webmethod is doing (such as is it a stored procedure or user defined function; is there a SELECT statement, etc.), I can try to repro the issue myself.

    Jimmy

  15. sezhian gk says:

    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.

  16. sezhian gk says:

    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

  17. SQL Protocols says:

    Hi Sezhian.gk,

    Regarding your question about accessing the WSDL document using a SQL Server user credentials, unfortunately, this is NOT possible at this time.  We’ll take your feedback into consideration for a future SQL release.

    Regarding using Perl to connect to the endpoint, please refer to an article written by my colleague up on MSDN, http://msdn2.microsoft.com/en-us/library/ms345140.aspx

    I have not tried it on a Linux box, but I would assume similar Perl behavior on both Windows and Linux.

    Regarding JAVA, you will again need to set the NT user credentials.  Below is a snipet from a JAVA application we used to test (we used the tool to generate stub classes from the Simple WSDL document):

       org.tempuri.Interop_EPSoapStub binding;

       try {

           binding = (org.tempuri.Interop_EPSoapStub)

                         new org.tempuri.Interop_EPLocator().getInterop_EP();

       }

       catch (javax.xml.rpc.ServiceException jre) {

           if(jre.getLinkedCause()!=null)

               jre.getLinkedCause().printStackTrace();

           throw new junit.framework.AssertionFailedError("JAX-RPC ServiceException caught: " + jre);

       }

       junit.framework.Assert.assertNotNull("binding is null", binding);

       // Add credentials

       binding.setUsername("userName");

       binding.setPassword("userPassword");

       // Time out after a minute

       binding.setTimeout(60000);

    Jimmy

  18. gk_sezhian says:

    Thanks Jimmy!

    rgds

    SEzhian.gk

  19. howyue says:

    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.

  20. SQL Protocols says:

    howyue,

    There are multiple ways to solve your scenario.  You can log the info programmatically by adding code in the Stored Procedure (webmethod) to write this info to a table, which you can later query.  An alternative method, is to use the SQL Server Profiler tool available with the SQL Server installation.  The Profiler tool allows you to trace various things that is happening on the SQL instance.  It should provide all the necessary traces you are looking for.

    If there is some trace you want but is currently missing from the Profiler tool, please feel free to send us the feedback.

    Jimmy

  21. Brent says:

    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

  22. SQL Protocols says:

    Hi Brent,

    Please elaborate a little bit more on your situation.  I’m assuming you are referring to writing an ASP.NET client application to connect to the backend Native Web Services endpoint.  And, when adding the web reference, you are not seeing a class file generated for the web methods exposed on the endpoint.

    Please double check that the proper permissions have been granted to the user (most likely your user account) for the endpoint and stored procedures(webmethod), see http://msdn2.microsoft.com/en-us/library/ms187811.aspx.

    Potentially, one quick way to verify you have the proper permissions is to use a web browser and point it to the endpoint url (http://server/endpointURL?wsdl).  You can search for the webmethod name in the server returned info.

    HTH,

    Jimmy

  23. Brent says:

    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;

           }

       }

    }

  24. SQL Protocols says:

    Hi Brent,

    403 generally implies that some level of permissions has not been granted.  Looking at the sample code, it does not seem like the "SqlSoapHeader.Security" object is part of the "sqlendpointname" class.  You will need to modify the generated class that was created from the WSDL document to include the following (as mentioned in the original post):

    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")]

    From there, the WSData() code would look something like:

    _ws.sqlSecurity = new SqlSoapHeader.Security();

    _ws.sqlSecurity.MustUnderstand = true;

    _ws.sqlSecurity.Username = "sqluser";

    CredentialCache myCreds = new CredentialCache();

    _ws.Credentials = myCreds;

    Jimmy

  25. Brent says:

    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?

  26. SQL Protocols says:

    Brent,

    I had a chance to quickly look into how ASP.NET web site project in Visual Studio 2005 adds a web reference.  Yes, it looks like it uses the WSDL to dynamically generate the proxy class.

    The SOAP header used for passing SQL user credentials to SQL Server is the same as the WS-Security header.  It is potentially possible to install WSE 3.0 to automatically get this to work (http://msdn2.microsoft.com/en-us/webservices/aa740663.aspx).

    Below is what I quickly tried without using WSE 3.0.  This is but one possible solution.  What I did is modify the WSDL document that the ASP.NET project saved.

    Note: If you refresh the web reference any changes you make to the saved WSDL file WILL be lost.

    Warning aside, you’ll need to update the WSDL file to add something like the following:

    1) Add a namespace definition at the <wsdl:definitions> element:

    xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd&quot;

    2) In the <wsdl:types> element:

       <xsd:schema targetNamespace="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd&quot; xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd&quot; xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema&quot; elementFormDefault="qualified" attributeFormDefault="unqualified">

         <xsd:complexType name="PasswordString">

           <xsd:simpleContent>

             <xsd:extension base="xsd:string">

               <xsd:attribute name="Type" type="xsd:anyURI" />

             </xsd:extension>

           </xsd:simpleContent>

         </xsd:complexType>

         <xsd:complexType name="UsernameTokenType">

           <xsd:sequence>

             <xsd:element name="Username" type="xsd:string"/>

             <xsd:element name="Password" type="wsse:PasswordString" />

           </xsd:sequence>

         </xsd:complexType>

         <xsd:complexType name="SecurityHeaderType">

           <xsd:sequence>

             <xsd:element name="UsernameToken" type="wsse:UsernameTokenType">

             </xsd:element>

           </xsd:sequence>

         </xsd:complexType>

         <xsd:element name="Security" type="wsse:SecurityHeaderType">

         </xsd:element>

       </xsd:schema>

     </wsdl:types>

    3) Add an additional <wsdl:message> element to the <wsdl:message> section:

     <wsdl:message name="s2Security">

       <wsdl:part name="header" element="wsse:Security" />

     </wsdl:message>

    4) For the WebMethod that you are interested in supporting this header add the following to the <wsdl:binding><wsdl:operation><wsdl:input> element:

    <wsdl:input name="s1MsgMyMethodSoapIn">

     <soap:body use="literal" />

     <soap:header message="tns:s2Security" use="literal" part="header" />

    </wsdl:input>

    Now, in the .cs file you should get IntelliSense for the "Security" class and you can add code like:

     aspendpointname.sqlendpointname proxy = new aspendpointname.sqlendpointname();

     proxy.Security = new aspendpointname.SecurityHeaderType();

     proxy.Security.UsernameToken = new aspendpointname.UsernameTokenType();

     proxy.Security.UsernameToken.Username = "sqluser";

     proxy.Security.UsernameToken.Password = new aspendpointname.PasswordString();

     proxy.Security.UsernameToken.Password.Value = "sqlpassword";

     proxy.Security.UsernameToken.Password.Type = "http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText&quot;;

    At this point, you should be able to call the web method and the code will send the appropriate SOAP header to SQL Server.

    Jimmy

  27. Brent says:

    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

  28. SQL Protocols says:

    Brent,

    Yes, if you are getting a 401:Unauthorized, it is most likely eith the user does not have permissions to login to SQL Server or the application is not sending the NT user/password as part of the HTTP request.

    If the application is not sending the NT user credentials, you will likely need add something like the following to the code:

         CredentialCache myCreds = new CredentialCache();

         myCreds.Add(new Uri(proxy.Url), "Basic", new NetworkCredential("userName", "pwd"));

         proxy.Credentials = myCreds;

    Please remember that the NT user needs to be a valid user for that machine (it can be a local user or a domain user).  The SQL user credential within the WS-Security header needs to have permissions to the endpoint and associated SQL objects.

    If you are access to some network tracing tool or adding tracing code to ASP.NET, it will help in troubleshooting whether the application is issuing the correct request or if it is the server rejecting the request.

    Jimmy

  29. Brent says:

    Thank you so much, you really know your stuff!

    One thing I observed is that if the  <soap:header message="tns:s2Security" use="literal" part="header" /> (From your Sat May 19 2007 post) tag is in my methods in the wsdl file, my windows user does not need connect permissions to the endpoint or exec on the functions/ procedures but if that tag is not there it still works as long as the windows user has connect and execute permissions.

    Do you have any hints for a solution which will no require editing the wsdl file everytime the web references are updated?

    Again, thank you so much.

    Brent

  30. SQL Protocols says:

    Brent,

    I’m glad things worked out, even if it takes some extra coding.

    You are correct in that if you use the WS-Security header to pass in a SQL user credential, then the windows user specified on the HTTP request only needs to be a valid user credential and does not need to have SQL logon permission.  We will use the SQL user credentials specified in the WS-Security header to logon to SQL Server.

    Currently, I do not have any solution for not requiring editing of the WSDL file.

    Note: I did not have a chance install WSE 3.0 and try your scenario again to see if by installing WSE 3.0, it will automatically generate code for sending WS-Security header.

    Jimmy

  31. Brent says:

    Hi Jimmy,

    I am trying to set this up with an SSL port other than 443 but nothing seems to work.

    My code works fine using 443 but as soon as I change the "SSL_PORT = 443" line in my code to something other than 443, my server ceases to respond to the wsdl request and I get a "Cannot find server" error. My firewall is off. Is there a setting in SQL Server 2005 I need to change? Any assistance you could provide would be greatly appreciated.

    Thanks,

    Brent

  32. SQL Protocols says:

    Hi Brent,

    I would double check the configuration of Windows HTTP service.  There were a couple of things that I did to make this work, but can’t be sure if I needed to configure both or not.

    1) Check that HTTP service is configured with the proper SSL certficate for the IP:Port combination.  This can be done using the httpcfg.exe tool (sample syntax: httpcfg query ssl).  If there isn’t a SSL certificate configured for the Port, you can add the SSL certificate configuration by using “httpcfg set ssl …”.  You may want to restart the HTTP service (net stop HTTP; net start HTTP).  Note: you should stop SQL Server service first before stopping HTTP service.

    2) If the above does not resolve the issue, please double check to see if the URL is reserved with the HTTP service.  This can be done with “httpcfg query urlacl”.  If the URL is not reserved, please connect to SQL Server and execute the “sp_reserve_http_namespace” stored procedure (sample syntax: sp_reserve_http_namespace N’https://MyServer:1000/sql‘).

    The only other thing I did was since I had firewall running, I added a TCP port exception for the port I’m using for the SSL.

    For additional information on configuring SSL certificates for on HTTP service, please refer to the following MSDN topic http://msdn2.microsoft.com/en-us/library/ms186362.aspx

    For additional information on reserving URL namespace on HTTP service, please refer to the following MSDN topic http://msdn2.microsoft.com/en-us/library/ms190614.aspx

    HTH,

    Jimmy

  33. Brent says:

    Thanks Jimmy! I didn’t have my ssl certificate configured for the port. The namespace reservation didn’t seem to make a difference.

    Thanks again,

    Brent

  34. Ashis says:

    Hai,

    I create a procedure and on that procedure i want to create a ENDPOINT. I am working in Client Machine.and when i execute this code i found an Error.

    ——————–

    //prcjaj- procedure

    ——————–

    CREATE ENDPOINT prcjaj

            AS HTTP (

            SITE=’*’,

            PATH=’/JAJ’,

            AUTHENTICATION=(BASIC),

            PORTS=(ssl)

       )

       FOR SOAP

            (WEBMETHOD ‘http://tempuri.org‘.’prcjaj'(NAME=’JAJ.dbo.prcjaj’),

            LOGIN_TYPE = MIXED,

            WSDL=DEFAULT,

            SCHEMA = STANDARD

       )

    An error occurred while attempting to register the endpoint ‘prcjaj’.  One or more of the ports specified in the CREATE ENDPOINT statement may be bound to another process. Attempt the statement again with a different port or use netstat to find the application currently using the port and resolve the conflict.

  35. 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…  

  36. 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

  37. SQL Protocols says:

    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

  38. SQL Protocols says:

    Hi Radha,

    It is a little confusing on the name in which the SSL certificate should be issued to.  Hopefully this will help clear things up.

    Example:

    Machine name is "myMachine"

    The SSL certificate used on the SQL Server machine is issued to "myMachine.domain.com"

    The client application should then specify the following as the HTTP URL:

     "https://myMachine.domain.com/endpointPath&quot;

    If you were to specify:

     "https://myMachine/endpointPath&quot;

    thinking that you are already in the same domain and can reach the machine, you will get the "the name on the certificate does not match with the web site name" error message you are seeing.

    The reverse is also true.  So, if the SSL certificate is issued to "myMachine" and the URL you are specifying is "https://myMachine.domain.com/…" you will also get this error.

    NOTE:  The SSL certificate name is suppose to be the same as the machine name.  It has not relationship to the ENDPOINT name.

    Additional information on how to manage SSL certificates can be found at http://msdn2.microsoft.com/en-us/library/ms186362.aspx

    HTH,

    Jimmy

  39. Hi Jimmy,

    thanks for ur last reply..

    I am facing another problem with it…

    I will go later for SSL-INTEGRATED-MIXED Login.

    Right now I have to do for BASIC-CLEAR endpoint. I am able to create sql http endpoint and consume it on my LAN. But i am not able to consume the created endpoint in case of publishing my website including the endpoint on a live IP and try to call the website from a LAN which has their own proxy/firewall, i get all pages files from the website but get error in case of sql data transaction, because the sql data would come through the endpoint. And the error i am getting while trying to access database through  the sql http endpoint through the website is "Object reference not set to an instance of an object"

    And when i try to call the created sql http endpoint from LAN which is other than endpoint  belonging network as http://59.165.29.20/SqlHttpEndPoint?WSDL where "SqlHttpEndPoint" is the path, then i get HTTP-500/HTTP-501 error in the browser.

    So can we not access a sql http endpoint from a proxy wall type LAN ?

    Please try to resolve it…

  40. SQL Protocols says:

    Hi Radha,

    Sorry, I hope I didn’t confuse you.  In you most recent post dated Dec. 29, 2007, you mentioned that you have to do for Basic-CLEAR endpoint.  Unfortunately, SQL Server 2005 SOAP endpoints do not support BASIC authentication over CLEAR http.  Basic authentication is only supported over SSL http.  Mixed login is also only supported over SSL http.

    Assuming that the endpoint was created for BASIC auth and SSL, the WSDL can be retrieved at https://59.165.29.20/SqlHttpEndPoint?WSDL

    or http://59.165.29.20:443/SqlHttpEndPoint?WSDL

    Also, make sure the proxy/firewall has the endpoint port open to allow traffic to the endpoint.

    HTH,

    Jimmy

  41. Radha Krishna Prasad says:

    Hi Jimmy,

    I am really very sorry for writing BASIC-CLEAR instead of INTEGRATED-CLEAR… I was not confused but did mistake in writing the last post.

    The issue is :–

    I have to do for INTEGRATED-CLEAR–Port-80  endpoint. I am able to create sql http endpoint and consume it on my LAN. But i am not able to consume the created endpoint in case of publishing my website including the endpoint on a live IP and try to call the website from a LAN which has their own proxy/firewall, i get all pages files from the website but get error in case of sql data transaction, because the sql data would come through the endpoint. And the error i am getting while trying to access database through  the sql http endpoint through the website is "Object reference not set to an instance of an object"

    And when i try to call the created sql http endpoint from LAN (which is other than endpoint  belonging network) as http://59.165.29.20/SqlHttpEndPoint?WSDL where "SqlHttpEndPoint" is the path, then i get HTTP-500/HTTP-501 error in the browser.  

    Could you please help in this issue ?

    Thanks,

    -Radha Krishna Prasad

  42. SQL Protocols says:

    Hi Radha,

    Regarding the HTTP 500/501 error, there may be a couple of possibilities:

    1) WSDL support is not enabled on the endpoint (double check to make sure the endpoint syntax looks something like:

      FOR SOAP

    (  WSDL=DEFAULT,

      …

      )

    2) Try stopping IIS to see if somehow IIS is receiving the HTTP request instead of SQL Server

    BTW, which OS is SQL Server running on?

    Generally "Object reference not set to an instance of an object" is a client side error message that typically means no data received from the server to create the object.

    I recommend if possible, capture the HTTP network messages between your client and the server to see what is being sent to the server and what the server returns.  It will help to ensure that the client sends the proper message to SQL Server.  It will also help to figure out what SQL Server is having trouble with.

    If you must use INTEGRATED-CLEAR on port 80 and the SQL Server is running on a different domain from the client machines, one other possible method is:

    – both the client machine and SQL Server machine must be running Windows OS

    – create the same local user with the same password on both machines

    – instead of specifying Basic auth, you would specify NTLM: myCreds.Add(new Uri(proxy.Url), "NTLM", System.Net.CredentialCache.DefaultCredentials.GetCredential(new Uri(proxy.Url), "NTLM"));

    If you prefer to use Windows user logins instead of SQL Server user logins, you do not need to use the SqlSoapHeader.Security optional SOAP Header.

    Additional information about .Net Framework client authentication type setting please refer to http://msdn2.microsoft.com/en-us/library/ms175929.aspx

    HTH,

    Jimmy

  43. Samik Banerjee says:

    Hi Jimmy,

    Below is the Endpoint we created in a machine over LAN in our company:

    /****** Object:  Endpoint [GetPerson]    Script Date: 01/04/2008 11:00:54 ******/

    CREATE ENDPOINT [GetPerson]

         AUTHORIZATION [sa]

         STATE=STARTED

         AS HTTP (PATH=N’/Person’,

    PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED), SITE=N’localhost’,

    CLEAR_PORT = 8080,

    COMPRESSION=DISABLED)

         FOR SOAP (

    WEBMETHOD ‘PersonList'( NAME=N'[test].[dbo].[GetPerson]’

    , SCHEMA=DEFAULT

    , FORMAT=ALL_RESULTS), BATCHES=DISABLED, WSDL=N'[master].[sys].[sp_http_generate_wsdl_defaultcomplexorsimple]’, SESSIONS=DISABLED, SESSION_TIMEOUT=60, DATABASE=N’test’, NAMESPACE=N’http://localhost/Person‘, SCHEMA=STANDARD, CHARACTER_SET=XML)

    When we try to access the Endpoint from the same machine as http://localhost:8080/Person?wsdl, it returns the XML absolutely fine.

    However, when we try to access it from another machine over the LAN as http://machinename:8080/Person?wsdl, it returns a HTTP 400 Bad Request page.

    I do not quite understand the concept of granting Connect permissions to the Endpoint. In our case, what accounts should be granted permissions to the Endpoint?

  44. SQL Protocols says:

    Hi Samik,

    In your sample, the endpoint is specified with "SITE = N’localhost’".  This tells the HTTP listener to only route HTTP messages sent to "http://localhost/Person&quot; to this endpoint.  All other HTTP messages sent to "http://serverName/Person&quot; or http://<IP address>/Person" will not be routed to this endpoint.

    The possible values for the "SITE" propery is as follows:

    [ SITE = { ‘ * ‘ | ‘ + ‘ | ‘webSite’ } ]

    Specifies the name of the host computer. If SITE is omitted, the asterisk is the default. If sp_reserve_http_namespace was executed, pass <hostpart> to the SITE keyword. For example, if sp_reserve_http_namespace N’http://MyServer:80/sql‘ was executed, specify SITE=’MyServer’ in the CREATE ENDPOINT statement.

    * (asterisk)

    Implies that a listening operation applies to all possible host names for the computer that are not otherwise explicitly reserved.

    + (plus sign)

    Implies that a listening operation applies to all possible host names for the computer.

    webSite

    Is the specific host name for the computer.

    For additional information about how the "SITE" property affects endpoints, please refer to http://msdn2.microsoft.com/en-us/library/ms181591.aspx

    My recommendation is to specify ‘*’ in development stages.  When you want to lock down on security, specify the machine name as the "SITE" value.

    HTH,

    Jimmy

  45. Radha Krishna Prasad says:

    Hi Jimmy,

    I did follow your steps, but issue remains same.

    The issue is: I am able to access the endpoint created on a live IP containing server with no firewall/proxy from any point those dont come under a proxy or firewall, but I am not able to access the endpoint from a LAN with proxy/firewall.

    Thanks,

    Radha Krishna Prasad  

  46. SQL Protocols says:

    Hi Radha,

    From your post dated Jan. 05, 2008, it seems that the same SQL Server SOAP endpoint is working fine if the client machine is not behind a proxy or firewall, but once the client machine is behind a proxy or firewall, things no longer works.

    At this moment, it looks like SQL Server is properly configured and working.  The question is how come sometimes the network is causing client machine behind a proxy or firewall to fail accessing the same SQL Server.

    I highly suggest using a network trace tool to see the difference between a working client machine and a non-working client machine.

    Hopefully from there you can ask the network admin to help you troubleshoot the problem.

    Jimmy

  47. Gustavo says:

    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

  48. krithi says:

    Can any one help to resolve my problem below ?

    I tried to access the CRM web service from the .net compact framework from a mobile application. When I intialise the crmservice and when I put Sevice.Execute(), it is throwing error that " An Unexpected Error Occurred" . When the code is run in ordinary Windows application, it is working fine. Can any one help me on this?I am using .net compact framework 2.0 with SP2 and windows mobile 6 professional emulator

    code is below :

     CrmAuthenticationToken token = new CrmAuthenticationToken();

                   token.AuthenticationType = 0;

                   token.OrganizationName = "Orgcrm";

                   CrmService service = new CrmService();

                   service.CrmAuthenticationTokenValue = token;

                   service.Url = "http://MachineName:5555/mscrmservices/2007/CrmService.asmx&quot;;

                   service.Credentials = System.Net.CredentialCache.DefaultCredentials;

        WhoAmIRequest req = new WhoAmIRequest();

                   WhoAmIResponse res = (WhoAmIResponse)service.Execute(req);

    ERROR :

    An Unexpected error occurred – System.web.Services.protocols.SoapException

  49. SQL Protocols says:

    Hi Krithi,

    Unfortunately, the error message you are receiving is just a generic failure message.  I suggest contacting the .Net Frameworks Compact team to see if there are difference in support between .Net Frameworks and Compact Frameworks.

    Here are some general web service troubleshooting ideas:

    – Narrow down to see if the issue is when sending the request or when receiving the response.

    – Capture the network traffic between the 2 machines to see what client and server are sending.

    -Jimmy

  50. Chris says:

    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.