About Web service or HTTP ENDPOINT

Hi All,

I met some problems when I walk through a example for Native Http SOAP in SQL Server 2005 for Developers. after I create the HTTP EndPoint in the Management Studio using following code:

USE AdventureWorks
GO

CREATE PROCEDURE EmployeePhoneList
AS
SELECT C.LastName, C.FirstName, C.Phone
FROM Person.Contact AS C
INNER JOIN HumanResources.Employee AS E
ON C.ContactID = E.ContactID
ORDER BY C.LastName, C.FirstName
GO

DROP ENDPOINT HRService;
GO

CREATE ENDPOINT HRService
STATE = STARTED
AS HTTP
(
PATH='/HumanResources',
AUTHENTICATION=(INTEGRATED),
PORTS=(CLEAR)
)
FOR SOAP
(
WEBMETHOD 'EmployeePhoneList'(name='AdventureWorks.dbo.EmployeePhoneList'),
DATABASE='AdventureWorks',
WSDL=DEFAULT
)

then I can see the web service is indeed created by using following cmnd,

SELECT *
FROM sys.endpoints

SELECT *
FROM sys.soap_endpoints;

SELECT *
FROM sys.endpoint_webmethods;

but when I want to use the web service in the VS2005, I can't find it and add the web reference. Any help will be greatly appreciated. and could you please tell me how to check web services in the local computer? Thanks.

Chris

Can you try http://yourmahine/HumanResources?wsd in your browser address bar and see if you can get the wsdl document in xml?

|||

No, I couldn't get the description for the web services, just a "page not found" like error.

In fact, when I run SQL to create HTTP EndPoint, I have to stop the web server first. after the execution of the SQL, I got an internal error from IIS if I restart the server. even though I restart the computer and web server, I still couldn't get the web services description.

I talked with some programmers, none of them use web services directly from sql server2005. and I notice there is few articles about it in the forum. Maybe microsoft just create this funtion for fun?

Anyway, Thanks a lot for you information. Hope get more help from you.

Chris

|||

Chris,

There's a small typo in Nan's post. The url is http://servername/HumanResources?wsdl

His post missed the 'L' at the end. Also, I'm assuming you are using the same user to retrieve the WSDL document as the user who created the endpoint. If not, you will need to grant the user permissions. "GRANT CONNECT on ENDPOINT::<name> to [user]".

HTH
Jimmy

|||

Sorry for the typo. The wsdl url should be http://servername/HumanResources?wsdl. If you can't get the wsdl xml document, please post the error code, 4XX or 5XX.

|||

I'm using IIS 5.1 on WindowsXP SP2. and I'm using the same user account for creation and access the WS.

the error code is HTTP 404 - File not found. Thanks.

Chris

|||

Ah, since you are running on a WinXP SP2 box, please make sure that IIS is not running. SQL 2005's native web serivces uses the OS HTTP kernel mode listener, which is introduced in WinXP SP2 and W2k3, but the IIS 5.1 on WinXP OS does not make use of it. This causes a port contention.

The alternative is to create the endpoint on a different port (ie. port 8080 or 88), such that the request does not end up going to IIS.

Jimmy

|||

I had the exact same problem. I found the answer at the following URL. I have excepted the piece that helped get me on the right path.

http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad125641/ReadmeSQL2005.htm

4.1.18 Remote Connections to a SOAP/HTTP Endpoint May Fail Using Integrated Authentication

Remote connections to a SOAP/HTTP endpoint may fail when the SQL Server service account is a domain account and authentication for the endpoint is set to INTEGRATED, even though local connections succeed. The problem can occur when the Service Principal Name (SPN) is registered to the local system account. You can work around the issue by either registering an SPN for the SQL Server Service account or specifying NTLM authentication for the endpoint. For more information about registering an SPN, see "Registering Kerberos Service Principal Names by Using Http.sys" in SQL Server Books Online.

Then in SQL Books I found the following;

To support mutual authentication under Kerberos, an instance of SQL Server 2005 must associate a Service Principal Name (SPN) with the account it will be running on, such as a local system account or domain user account. The specific details for SPN registration by a specific instance of SQL Server 2005 are determined by the type of service account it has been configured under. If SQL Server 2005 is running under the local system account or network service account, SPNs must be registered under the computer name. If SQL Server 2005 is running under a domain user account, SPNs must be registered under the domain user name.

Using SetSPN.exe

To enable association of an SPN to the account that the instance of SQL Server 2005 is running on, use the Windows SetSPN.exe support tool. The tool adds the SPN for the computer name on which the instance of SQL Server 2005 is running under the Windows domain service user account located in Active Directory. In this scenario, the SetSPN.exe tool can be used to add two SPNs: one for the NetBIOS name and another for the fully qualified DNS name.

For example, if the SetSPN.exe tool is executed from instance of SQL Server 2005 running on MyComputer, the following two SPNs are associated with the account the instance of SQL Server is running under and must be added to directory:

HTTP/MyComputer;

HTTP/MyComputer.fully.qualified.domain.name.com

If an instance of SQL Server is running as a domain user (MyDomain\MySQLAccount) on a computer that is named MySQLHost, the following commands can be used to set the appropriate SPNs:

setspn –A http/MySQLHost MyDomain\MySQLAccount

setspn –A http/MySqlHost.Mydomain.Mycorp.com MyDomain\MySQLAccount