Cannot Connect Remotely to HTTP EndPoint

Dear all,

I appologise that incarnations of this topic have been posted several times on this forum, however I cannot find a scenario that is the same as my own so I've resorted to starting a new thread.

I can successfully create and connect remotely to an HTTP EndPoint on my Windows 2003 machine using Integrated security, the administrator username and password via my static IP address.

I have read that Windows XP SP2 supports HTTP EndPoints, and it would be much more convenient for me to be able to create EndPoints in XP, and connect remotely.

I have created the endpoint like so...

CREATE ENDPOINT sql_tvr10
STATE = STARTED
AS HTTP(
PATH = '/sql/tvr10', AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR))

FOR SOAP (
WEBMETHOD 'StoredProcedure1'
(name='master.dbo.StoredProcedure1'),
WSDL = DEFAULT,
BATCHES = ENABLED,
DATABASE = 'master',
NAMESPACE = 'http://194.106.11.11/' )

In visual studio I can retrive a list of services running at http://localhost/sql/tvr10?WSDL absolutely fine. However, in Visual Studio on the remote machine, I cannot connect to http://194.106.11.11/sql/tvr10?WSDL - it prompts me for a username and password, however I cannot select the username - it is set to "Athlon64\Guest" (Athlon64 is my server computer name). I have no passwords on any of my windows accounts (except administrator), and I have no database passwords set.

Could someone please tell me how I should go about accessing the HTTP EndPoint?

Just so it is clear, I am running a small LAN with no domain, just a workgroup. I need the client application to be able to connect via http regardless of which network it is on. I am using VS2005 and SQL Server 2005 Dev Ed.

Many thanks in advance,
Simon

Hi Simon,

On a workgroup network, I would recommend changing the "AUTHENTICATION" value on the endpoint from "INTEGRATED" to "NTLM". By default VS2005 will try Kerberos first when the server reports that it supports "INTEGRATED" authentication. I remember encountering issues where it will not retry with NTLM. By forcing NTLM on the SQL Server, will help elminate this problem. From a security perspective, I would recommend creating an user on the WinXP machine for use as the user who will access this HTTP Endpoint. You can then grant specific permissions in SQL (including endpoint connect permissions) for this user. This way, when prompted for credentials, you can specify this user "Athlon64\myUser".

Note: If VS2005 does not allow you to specify/choose the correct user, I suggest that you use Internet Explorer to retrieve the WSDL document. You can then save the WSDL document on your development machine and reference the file in VS2005.

A couple of things to remember,
- Ensure the WinXP firewall is not blocking TCP port 80
- Ensure that the WinXP IIS is not running (as it will take over port 80 and you will not end up connecting to SQL Server)

Jimmy

|||

Thanks for your advice, I think I had already tried a NTLM endpoint with no success. However I did as you said, and created a new windows user called db (and set a password),
then from the console I typed ...

"CREATE LOGIN [Athlon64\db] FROM WINDOWS; go",
"CREATE USER [Athlon64\db] FOR LOGIN [Athlon64\db]; go",
"grant connect on endpoint ::sql_tvr14 to [Athlon64\db]; go"

And tried searching http://194.106.11.11/sql_tvr14 for web services, and this time instead of prompting for the password of "Athlon64\GUEST", it prompted for the password of "Athlon64\db" - I thought 'BINGO', but alas when I type the password I specified in Windows control panel it just throws it back at me :'(. I'm pulling my hair out on this one!

I have disabled all my firewalls (router and windows), and I have stopped the ISSAdmin service in Control Panel -> Admininstrator -> Services.

If anyone can solve this I will owe them some sort of Wookie life debt!

|||

If you are in a workgroup environment using integrated logins will be problematic because there is no shared trust (no domain controller). One trick that might work is creating the same account on client and server machines with the same password (create db account on client machine with same pwd as account on Athlon64 machine), then log in as db user, run IE and hit the site, it should work with NTLM because the hash for both accounts will be the same.

Another thing you can try is use digest or basic, which work by hashing or sending the userid and password and then doing the authentication on the server side. Basic over SSL will definately work, I have not tried digest in this case but in theory it should work.

|||Thanks, I'll give that a go now.
Is it worth mentioning that the "Athlon64\db" user I created cannot even log on on the server computer using "osql -U [Athlon64\db]" from the command line? Is there a problem with the way I created a new login/user above?|||I have just tried your suggestions, including creating a new endpoint using BASIC or DIGEST, and I'm still having no luck. BASIC over SSL just refuses the connection (Error 400), DIGEST prompts for a UN and password, but rejects the input.

I am beginning to suspect there's a problem with the user accounts on the server. If I try "osql -U [Athlon64\db]" and the password I specified when I created the user account in control panel, should I be able to connect (because I can't!)? The only way I can connect to the db via the command line is "osql -E", and that logs me in without asking for any passwords.

:'(|||

osql -U [Athlon64\db] will not work.

Instead do this:

goto c:\windows\system32 in explorer, hold down shift key and right click cmd.exe, select Run As... and then enter userid and password for Athlon64\db.

Then from this command prompt (whcih is now running as Athlon64\db) run:

osql -E

This will attempt integrated login.

You can also test launching IE from this console window (run iexplore.exe) and then IE will be running as this user.

Also, inside IE make sure you goto Tools | Internet Options -> Advanced and check "Enable Integrated Windows Authenication" and restart IE if this is not checked.

|||

That worked, so now where do I go from here?

I'd also just like to thank everyone for their support; it's most kind of you guys to volunteer your time to a stranger!

|||

Not sure what your end result is, what are you trying to accomplish with the endpoint?

|||Basically I need to be able to query a personal database via http/the internet in C#, including use from a PDA.

As I said in my first post I had it working well with Server 2003, so I may have to go back to that. The only trouble being that my Server2003 drive crashed and I'll need to reinstall and configure everything :(|||

Yes if it's from a PDA I would use https and basic authentication, this would be the simplest and easiest thing to configure.

With basic the client sends the user id and password and this is authenticated on the server side using only the local security authority so you don't need a domain controller.

Basic should work well even from IE no matter what.