Unable to connect to SQL Server from Windows Service running on Windows Web Server 2008

Question

I am facing a following error while trying to connect with an SQL server from Windows Service (written in vb.net).

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)

This service is running on a Windows Web Server 2008 R2 and with SQL Server 2008. SQL Server is also located on the same machine. Also, the firewall is disabled on the server. I have couple of web applications running on the same windows server connecting to the same sql server and there is no connection issue. But, this issue is faced only when I try to connect from the windows service only.

Any help will be greatly appreciated.

Thanks

Answer 1

You probably have 1 of the following problems.

You are not using the correct connection string. Dump out the string you are actually using from from your running service and make sure you can connect with it.

You have a permissions problem -- you should double check the security context the service is using. This is a common problem if you are trying to connect using integrated security, or user impersonation in particular.

ADDED

If you have intermittent problems, there is different problem. (unlikely based on your problem description.) The reason I suggest dumping the connection string from your service to make sure it is correct, is that so often the developer (including me) is SURE that it is correct, when it is not.

If you are using integrated security, the process runs using the security context of the service, and this the user context that will be used by the database -- look at service properities, you specify which user is used to run the service.

If using impersonation, you basically switch the security context to be that of the user -- this would normally be done if the website is using integrated security as well, i.e., not forms based security -- suitable for in-house use, not public facing websites.