Accessing SQL Server over a network from a Windows Service

Question

I have created a class that will connect to SQL Server to run a stored procedure. When this class is used in a Windows Forms solution, the database can be accessed successfully. When the class is put into a Windows Service, I get the following 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I suspect the problem is permissions-related.

This is the section of relevent code:

SqlConnection conn = new SqlConnection(j.ConnectionString);
SqlCommand cmd = new SqlCommand(j.Query, conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet("Table1");

da.Fill(ds, "Table1");    // <----- error occurs here (Windows Service only)

The SQL Server version is

Microsoft SQL Server 2008 (SP1) - 10.0.2766.0 (X64)   
Enterprise Edition (64-bit) on Windows NT 5.2 <X64> 
(Build 3790: Service Pack 2)

The Visual Studio version is Microsoft Visual C# 2008 Framework version is 3.5 SP1.

Answer 1

That particular error only means that the client cannot connect to the server. This is before it even does permissions. Check to make sure the app server can connect. Some things to try:

1) Do a telnet to the server IP over port 1433 (assuming default port) If this doesn't work, is there a router or firewall blocking access to the server? 2) Go to ODBC (Data Sources) on the app server and set up a DNS entry and hit the "test" button at the end. This will test if the client driver can talk to SQL Server.

Most likely step 1 will fail. You'd get the same error if you changed the server name to "BigBird". It's a generic error saying "you told me to connect to this server, but no server responded to a connect request". This happens before the "handshake" and permissions check happen between the client and server.

HTH, Eric

Answer 2

Windows services are restricted from using network resources when using the default "LocalService" login. Thus, you need to have the service run under a user account which allows access to the network. Here are the possibilities:

1.) If you use the "NetworkService" connection, you should not use Windows' integrated authentication on the connection to the SQL Service, but you can use SQL authentication with a username and password. (Technically, you may be able to use the integrated authentication if you give the client computer's domain account--CLIENT$ for example--permissions to the database... but that's not recommended.)

2.) If you set the service to run under a real user account of your choosing, then the SQL connection can use Windows integrated authentication, but you have to give that account permissions to the database.