Not able to connect to the SQL Server data from asp.net app

Question

We are upgrading from an environment where the development web server, the SQL Server 2005, and SQL Server data are all on the same machine, a Windows XP Machine. We are upgrading to having the web server on one Windows 2008 server, the SQL Server on a 2nd Windows 2008 Server, and to Server to a Windows 2008 server, nd the data on a SAN.

Now we are getting the error message:

A connection was succesfully established with the server, but then an error occured during the login process. (provider: named pipes provider, error 0, no process is on the other end of the pipe.)

The network guy thinks it is a problem with the connection string:

<add name="CNString" 
     connectionString="Data Source=WEBSERVER;
                       Initial Catalog=PCIdb;
                       User ID=sa;Password=pass;"
     providerName="System.Data.SqlClient"/>

Can anyone help out here?

Answer 1

I got that error before...Make sure SQL server services are started

If that's not it, change the authentication mode to both server and windows.

Answer 2

This can be caused by any number of reasons, but the first thing to look at is the configuration and setup of the SQL Server itself.

Are named pipes enabled on the server? You can check this through the server configuration manager (on the SQL Server machine itself).

Is the user on the connection string (or the connection pool user if you are using SSPI) setup on the server?

Note:

I do hope the connection string you put up is an edited version of your real one, as there are a couple of issues with it:

• Using the sa login - you should never do that as now any SQL exploit can do any damage it wants
• Calling a sql server "webserver" - a really confusion naming decision

When it comes to connection strings, look at connectionstrings.com - they hold a good list of valid connection strings to many databases using different providers.

Answer 3

It could be a connectionstring problem. Try comparing your connectionstring to the connectionstrings at http://www.connectionstrings.com but it could also be that the remote connection or the remote server (the webserver in this case) isn't allowed to connect to SQL. Check if remote connections are allowed and named pipes and/or TCP/IP protocols are enabled on the database server.

But, based on the scenario you've described, I'd say it's the database-end that's refusing the connection (since you've already had a working solution).

Answer 4

Is your SQL Server really called WEBSERVER (DataSource="WEBSERVER") ??

Also, I would never EVER use the sa account in a connection string - NEVER, period. Use an application specific user or something, but do not use the sa account under any circumstances.