Upgraded .net webapp can't open connection to database

Question

I have an old .net 1.0 webapp that needs a bit of maintenance done on it. I've used the auto-upgrader to upgrade it to .net 3.5 (and also later tried 2.0) but now it can't connect to the database.

On the surface this looks like a noob connection-string problem, but I'm thinking it's more likely to be related to some subtle problem from the upgrade.

I get the generic error message:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The source code is pretty basic, with no bells or whistles:

protected static SqlConnection objConn;

objConn = new SqlConnection(strConnectionString);

try
{
    objConn.Open();
}

and also tried it as:

using (objConn = new SqlConnection(strConnectionString))
{
    objConn.Open();
    ...
}

The connection string comes from the web.config, and using the debugger to set a break point and look through the properties of the connection before it tries to open it, I can see that its found the connection string properly:

connectionString="Data Source=XXX.XXX.XXX.XXX;Initial Catalog=XXXXXX;User ID=XXXXXX;Password=XXXXXX"

A handful of things I think I've ruled out:

The error message is the same whether connecting to Sql Server 2005 or Sql Server 2000.

No errors or warnings in the VS Error List.

The connection string is the same as a couple of other websites that also use the same database, so I know that the connection string is correct.

I've tried having it connect from my local computer (where I can connect to the databases via Sql Query Analyzer) and from the normal webserver, so it's not a firewall issue, neither is it a max-connections issue.

It's connecting to the server's IP address, so its not a computer browser issue (and other webapps can connect fine.

TCP and named pipes are the 2 network protocols enabled on the sql server.

Adding "Network Library=DBMSSOCN;" to the connection string changes the error message to include "provider: TCP Provider, error" (there was something else I changed in the connection string the other day that also had that affect, but I can't remember what now.)

I've already looked through 3 other similar posts on stack overflow:
(Can't list links here because I'm a new user, but their question ID's if someone else wants to link to them in a comment are: 63875, 1038888, 846479)
And this article on another site had some good ideas of things to try that didn't help either:
http://weblogs.sqlteam.com/tarad/archive/2008/05/23/60609.aspx

My best guess is that its something caused by the upgrade between .net versions - maybe something wrong in the web.config?

It's a C# app, pretty small/basic, but has been split into three projects.

Answer 1

According to this, SQL 2005 does not come configured to allow incoming local or remote connections and you have to enable it yourself.

Answer 2

I'm sure you have gone through these steps already but...try enabling remote connection for SQL Server and enabling the SQL Server Browser service. A walkthrough for both can be found here.

Answer 3

You could try using another type of connection string. My connection strings are usually formatted like:

<add name="DataFrom" connectionString="SERVER=[servername];Database=[dbname];UID=[userid];PWD=[password];"/>

Are the other aplication you said that are working also .NET 3.5 applications?

I am also curious how you managed to get this piece of code working

using (var = new SqlConnection(strConnectionString))
{
    objConn.Open();
    ...
}

I think you mean

using (objConn = new SqlConnection(strConnectionString))

right?

Answer 4

Write a console application that does nothing but try to connect to the database using the code you posted. Hard-code the connection string in the console program.

In other words, remove all the distractions to make sure that the core code you are trying to run actually works correctly.

Answer 5

As there was never an actual solution provided for this problem, here is the one that helped me:

I had the exact same problem when I upgraded a perfectly running ASP.NET 2.0 application to .NET 3.5. The web app is being hosted by IIS 5.1 on Windows XP and I'm using SQL Server Express 2005. I'm using the following connection string in my web.config:

<add name="myDbConnection" providerName="System.Data.SqlClient" connectionString="Data Source=http://localhost;Server=.\SQLEXPRESS;Initial Catalog=MyDatabaseName;Integrated Security=True"/>

So as you see, I'm doing no tcp connection here or connecting to a remote server. I'm just connecting the SQLEXPRESS instance on the localhost thru named pipes. That's why I thought, it will have absolutely nothing to do with allowing remote connections on SQL Server 2005. But as I found out, it does! (I don't know the actual reason for this!) I spent a few hours to find that out, so maybe this helps someone...

What you have to do (please use google for the details):

1. Allow Local and Remote Connections via SQL Server 2005 Surface Area Configuration Tool (I choose the option "Using both TCP/IP and named pipes")
2. Enable the SQL Server Browser service
3. Set the listening TCP port to 1433 for all IPs via SQL Server Configuration Manager Tool
4. Enjoy your running web app :-)

Why all this is necessary in order to succesfully connect from an .NET 3.5 Web App to SQL Server when it works already from .NET 2.0 is beyond me!