Accessing remote SQL Server DB from a Windows Service

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

Hello all!

I have a windows service that needs to access a remote SQL Server 2000
database (actually an MSDE db, but that info shouldn't matter, right?). The
Service is set up as a Local System service.

My connection string looks like this and it works fine under a console app:
Server=INGD00FC21;Database=DLClient;User ID=user;Password=pw;Integrated
Security=true;

This connection String also works fine under a console app:
Server=INGD00FC21;Database=DLClient;Integrated Security=true;

In either case, I get the following error, which I need to resolve:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Any suggestions?
Thanks in advance,
Will.
 
When you specify "Integrated Security=SSPI", ADO.NET ignores any UserID
Passord you provide. The login exception is because there is no account
registered in SQL Server for the windows domain credentials you are
supplying.
If you have applied SP3a to your MSDE server, you can only use SSPI security
unless you have switched to mixed mode security. In any case you need to
make sure that the server has a Login account that matches the credentials
being supplied.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill, thanks for the info about how SSPI works.

However, I'm confused as to why my local Windows Service isn't using my user
credentials to log in to the remote (eh, it's actually across the room) SQL
Server. It says that this user:
NT_AUTHORITY\ANONYMOUS LOGON
is trying to log in, rather than using my acutal NT DOMAIN\USERNAME
credentials.

Any clue as to why this is happening?

Thanks,
Will.
 
Is the ADO.NET code running on your system or on the remote server under IIS
or COM+?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
My computer currently hosts the windows service containing the ADO.NET code
during development, but eventually it will reside on the same box as the SQL
Server, which is on a different machine.
 
Ah. So the code is run by a Windows service. THAT's whose credentials are
being used. You'll need to setup an account in SQL Server for those
credentials.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I'm unsure how to go about setting up a user account in SQL Server for my
Windows Service.

When I attempt to set up a new NT Server login in my SQL Server tool with
the username "NT_AUTHORITY\ANONYMOUS" it says the user cannot be found. I
assume this means that the user is not in the list of users for the server
PC. An NT username can only be 20 characters long, so there's no way to add
that as a user.

When I try to add "NT_AUTHORITY\ANONYMOUS" as an SQL Server User, it
complains that the username string contains invalid characters.

My suspicion is that the Windows Service is actually using some other
credentials - not "NT_AUTHORITY\ANONYMOUS". How do I find out which
credentials my Windows service is using so that I can add them to the SQL
Server permissions?

Confused and a little confounded,
Will.
 
I found a solution that works - configuring my windows service to log on
using my NT user credentials. However, I'm 90% sure this is an undvisable
solution, as well as being a pain in the rear to have to configure the
service every time I need to re-install it.

Any suggestions?

Thanks,
Will.
 
Okay, what about having your service log on as some known username and
password that's a member of the domain and make sure that member has a SQL
Server login account with appropriate permissions? This way it can use SSPI
security.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top