Web Service Hair Pulling

Hi,

I know there's probably an obvious answer to this one, but unfortunately I'm blinded by sheer exhaustion (and web surfing for the answer)...

I've created a real basic web service via VB.net that pulls in the Customers table from the Northwind database:

Imports System.Web.Services
Imports System.Data.OleDb

<WebService(Namespace:="http://tempuri.org/")> _
Public Class AnotherTest
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function ListCustomers() As DataSet
Dim myConnection As OleDbConnection
Dim myDataAdapter As OleDbDataAdapter
Dim myDataSet As DataSet
myConnection = New OleDbConnection _
("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; Initial Catalog=Northwind;Data Source=TEST\TEST;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TEST1;Use Encryption for Data=False;Tag with column collation when possible=False")
myDataAdapter = New OleDbDataAdapter("SELECT * FROM customers", myConnection)
myDataSet = New DataSet()
myDataAdapter.Fill(myDataSet, "Customers")
Return myDataSet
End Function
End Class

The code runs without error and displays the usual service description page and then allows me to click the created service...unfortunately when I click the 'Invoke' button all I get is a HTTP 500 error 'The page cannot be displayed'.

To add insult to injury the 'Hello World' code works fine, it just seems to be an issue with my ability to display SQL data. On the off chance this is useful I also tried running a service supplied as an example on the web with the same result...

I welcome comments from anyone with any thoughts on this issue!

Regards, Mark

As A quick follow-up: I used the same code but built the service using Web Matrix - it also failed with the same results.

Now I'm pretty sure that the issue is with my IIS 5.0 configuration, but where to look?

Ok, I've got a little further. I unchecked 'Show friendly HTTP error messages' in IE and I received this message...

Login failed for user TEST1\ASPNET

I figure that somehow there is a ASPNET user that I need to add to a group somewhere to provide access to the SQL database - but I can't find where...

Any help please?

I believe the reason you are getting that message is in your connection statement:

myConnection = New OleDbConnection _
("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; Initial Catalog=Northwind;Data Source=TEST\TEST;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=TEST1;Use Encryption for Data=False;Tag with column collation when possible=False")

It appears that the line "Workstation ID=TEST1" is acting like a domain and since it's not specified in the login it appears that the ASP is giving itself a username of ASPNET.

So what you probably should do is specify your login in the login statement and take out the "Workstation ID=TEST1". Try specifying the username of your SQL login by adding "User ID=username;Password=pwd" without the quotes.

Hope that works for you.