Unable to Connect to SQL Server 2005 Db From Vista x64 Windows Service

Question

I'm trying to connect to a remote SQL Server 2005 db from a .NET Windows service running in Vista Home Premium x64. I can access the remote db from a console app with no problem. I can connect to a local db from the Windows service with no problem. I was able to connect from a service from XP with no problem. There's no firewall or anti-virus running. How do I configure this service to be able to connect to the remote db?

I've tried to connect by running the Windows service as a local admin account, LocalSystem, LocalService, and NetworkService.

The connection string:

Data source=SERVER_NAME;Initial Catalog=DB_NAME;Integrated Security=True;

The error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

MORE INFO:

I have also tried to connect using SQL Server authentication with no success:

Data Source=SERVER_NAME;User ID=USER_ID;Password=PWD;Initial Catalog=DB_NAME

This connection string works from the console app too.

MORE INFO:

I ran Process Monitor for the Windows Service and the console app. The Windows service showed \SERVER_NAME\pipe\sql\query was ACCESS DENIED but the console app showed SUCCESS when reading/writing files to \SERVER_NAME\pipe\sql\query.

Answer 1

1) open a command prompt. Type "ping SERVER_NAME". Does it respond? You may have a DNS or connectivity issue if this doesn't work.

2) "telnet SERVER_NAME 1443". Do you see anything or does it refuse your connection? This will definitively tell you whether or not someone is listening on the other end.

3) Go into SQL Server Management Studio. Right click Properties of your server. Select "Connections" from the left side. Is "Allow Remote Connections" checked?

4) Since you're running using network service/system, you will need to make sure you have a login configured on your server for the machine account DOMAIN\CLIENTSYSTEMNAME$. Note the $ sign. This is your machine account, and this will be the user that SQL Server will see.

Answer 2

Good Lord! Why all the gibberish and complex responses on this site. Create a User Account
Either local or Domain and set the service to use that account. Then go into your SQL Server and Add you new account to the Database and set permissions. Voila!

Oh yeah, dont plague yourself with SQL Authentication. Integrated Security is much easier to maintain and without a password in your web.config your much safer.