When you fire up your application and you receive this message, you follow some standard steps and try to find the answer to the following questions
- Is my SQL Server up and running?
- Can I connect to the box SQL Server is hosted?
- Is my connection string correct?
- Am I using the correct IP address and instance name?
- Is SQL Server accepting the type of connection I want (Named Pipes, TCP/IP, etc)?
- Is my firewall blocking either the outgoing connection request or the inbound one?
- Is my router not forwarding the connection request to the correct machine and port?
Somewhere between #1 and #7 you will find yourself answering "no", fixing the problem so the answer becomes "yes" and the annoying error message "Error: 40 - Could not open a connection to SQL Server" goes away.
So, what happens when you have answered "yes" to all the questions and the stupid message is still there? In my case it was because I answered "yes" to question #5 without it being true. You see, I assumed that if the SQL Server Configuration Manager says that TCP/UP is enabled (see screenshot below) then my SQL Server is ready to accept TCP/IP connections.
Silly me! When the machine has more than one interface (i.e. IP address that SQL Server can listen to for incoming connections), each one has to be enabled separately.
You will need to enable at least one IP address before SQL Server starts accepts TCP/IP connections. Make sure you enable the one you are trying to connect through :)