IIS 7 cannot connect to SQL Server 2008

Question

Bit of a puzzle here.

I have a Windows Server 2008 running IIS7 and MS SQL Server 2008 R2. I have opened up the neccessary ports on the firewall and enabled the correct SQL Server configuration options to allow remote connections.

I have proven this works by being able to connect both in SQL Server Management Studio Express and VS 2010 from my dev machine. However, when I upload my site to the server, it bugs out attempting to connect to the database - using the very same connection string that works on my dev machine.

Data Source=MY.IP.ADD.DDRE.SS;Initial Catalog=relenster_v2_updated;Persist Security Info=True;User ID=<username>;Password=<mypassword>

The error I'm picking up is:

provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

Any ideas?

UPDATE: SQL Server and IIS are on the same machine

My Dev machine is totally separate

Answer 1

A pretty lengthy discussion about this same problem can be found on MSDN. Perhaps you'll find an answer there.

Answer 2

Solved

The issue was that the connection string on the server referenced the server by IP address directly. However, this IP address meant nothing to the server, which only knows about its internal IP address.

In short, changing the IP address to localhost solved the issue

Answer 3

You may have an issue related to the sever running IIS. I am assuming that the SQL server instance is running on a different machine than your "dev machine". I am also assuming that your IIS server is not the same as the SQL server or your dev machine. In that case I would test to see if you can connect to your SQL server from your IIS server. A little more information about the different servers (nework environment) would help. Windows server 2008 might be blocking outbound connections.