Error: 40 - Could not open a connection to SQL Server - only on Staging website

Question

I have two ASP.NET websites on a web-server, and two SQL Server databases.

I have a folder for Website #1, which contains a web.config with a connection string pointing to Database #1.

Under the folder for Website #1 there's a staging folder called 'staging', containing Website #2, and the web.config there has a connection string that points to Database #2.

Website #1 works perfectly.

But when I try to run Website 2, I always 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I've verified that the connection string is correct and that it's pointing to Database #2.

But even when I swap in the connection string from Website #1, which works perfectly on Website #1, it still fails.

Here's what the connection string looks like:

<connectionStrings>
    <add name="WebsiteConnectionString" connectionString="Data Source=localhost;Initial Catalog=WebsiteStaging;User Instance = false;User Id=xxxx;Password=yyyy" providerName="System.Data.SqlClient" />
</connectionStrings>

Is there anything unusual going on here that I'm not aware of?

Answer 1

Not sure which version of IIS you're using, but you might need to create two separate web applications: one pointing to the Website #1 folder and the other Website #2.

When you view Properties of the Website/virtual directory, the Application name should be set for both directories. Is this the case?

Answer 2

LOCALHOST is for IIS

(LOCAL) is for SQL Server

My connection strings usually say "Server=(LOCAL);Integrated Security=SSPI...."

I would check the security tab to find out the identity that both web sites are running under.

Answer 3

Really sorry... looks like it wasn't to do with the connection at all.

It was because the Linq-to-SQL data-layer (which was in another project) was referencing its own connection string (even though, wherever I instantiated a DataContext in the Website project, I always used the web.config connection string as a parameter).

But thanks for your answers, those would probably be good things to check, had the above not been the problem.