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

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

  • Comments 50

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

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • 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!
  • 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
  • I am not using SSL connection in the endpoint.
    Will the above code work for me if i use Integrated instead of basic ?
  • 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
  • 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
  • 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
  • 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
  • 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 [mmyComputer\soapUser]

    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
  • Thanks a lot ..i was able to implement it but with the windows-sql server login
  • 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?

  • 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

  • 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

  • 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

  • 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

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

Page 1 of 4 (50 items) 1234