Could not open a connection to SQL Server

Question

I have problems connecting to my database server. The database server is not local, I am connected via its IP address.

It works fine in my development machine. After publishing the website to my server, it can not connect to my database server.

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)

Why can't my publish server connect to the database server, whereas it works fine through the development machine?

Answer 1

I'll attempt to go two for two on the psychic debugging for today...

I will assume that you are not using integrated security? If so it might explain things as the account on your local machine probably has permissions, but the SYSTEM account that is running on the server does not. Just a shot in the dark though.

Answer 2

It sounds like your server can't make the network connection, rather than a security issue.

• Ensure that any firewalls on both the DB and app servers allow traffic on the port (1433).
• Ensure you're able to ping or tracert from both machines.

Answer 3

I often had that problem. Mostly it's because of two problems.

1. Open the SQL Server Configuration Manager.

   • Check if the SQL Server Network Configuration supports TCP/IP, if it's disabled, enable it. e.g. SqlServer 2005 Network Configuration, Protocols for SQLEXPRESS

2. Open the SQL Server Management Studio

   • Click on the Sql Server Properties (right click on the server name and select properties).
   • After that, select the page "Security" and switch the check to "SQL Server and Windows Authentication mode".

That's all.

Answer 4

Is it your first time you publish your website on the web server ? if it is , Are you sure you have set your connection string properly ?

You need to create a login ( of curse a password for that too ) and the IP Address of that SQL on the web server .

So you need IP Address of the sql server host , Database Name , UId , Pwd .

Answer 5

I will agree that this sounds like a network issue and not a security issue.

Remote into the web server and ping the DB server by IP address. If this does not work, your server cannot see the DB server via that address (different subnet, incorrect firewall/proxy setup, etc). There may be a proxy address you must use to get to the DB server from the web server, or your web server may also be the gateway and IIS doesn't know to look for the DB server on the LAN. If it does work, the computers may not be talking on the same port, or the firewall may be blocking that port exiting the web server.