Can't connect to SQL Server 2005 Db from Vista x64 Windows Service
I am trying to connect to a remote SQL Server 2005 db from a .NET Windows service running on Vista Home Premium x64. I can access the remote db from the console application without issue. I can connect to the local database from a windows service with no problem. I was unable to contact the service from XP. There is no firewall or antivirus. How do I configure this service to connect to a remote db?
I tried to connect by starting the windows service as local administrator account, LocalSystem, LocalService and NetworkService.
Connection string:
Data source = SERVER_NAME; Start directory = DB_NAME; Integrated Security = True;
Mistake:
A network related or instance specific error occurred while establishing a connection to SQL Server. The server was not found or was not available. Verify that the instance name is correct and configure SQL Server for remote connection. (provider: Named Pipes provider, error: 40 - Could not open connection to SQL Server)
ADDITIONAL INFORMATION:
I also tried to connect using SQL Server Authentication with no success:
Data source = SERVER_NAME; User ID = USER_ID; Password = PWD; Start directory = DB_NAME
This connection string also works with a console application.
ADDITIONAL INFORMATION:
I launched Process Monitor for Windows Service and Console Application. 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.
source to share
Good Lord! Why are all the nonsense and complex answers on this site. Create a user account
Either locally or Domain, and configure the service to use that account. Then go to your SQL Server and add a new account to the database and set permissions. Voila!
Oh yes, don't be offended by SQL Authentication. Integrated security is much easier to maintain and no password in your web.config is much more secure.
source to share
1) open command prompt. Enter "ping SERVER_NAME". Will he answer? You may have a DNS or connectivity issue if that doesn't work.
2) "telnet SERVER_NAME 1443". Do you see anything or give up your connection? This will definitively tell you if someone will listen on the other end.
3) Login to SQL Server Management Studio. Right click your Server Properties. Select "Connections" on the left side. Is the "Allow remote connections" option checked?
4) Since you are working with a network service / system, you need to make sure your server is configured with a login for the computer account DOMAIN \ CLIENTSYSTEMNAME $. Notice the $ sign. This is your computer account and this will be the user who will see SQL Server.
source to share