Why am I getting “Cannot Connect to Server - A net

Question

I get the following error when trying to connect to SQL Server:

Cannot connect to 108.163.224.173.

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) (Microsoft SQL Server, Error: 1326)

This error is thrown when I try to configure my database to gridview in Visual Studio 2010. I'm at a loss as to how to debug this error.

How would you debug this error? What steps should I take in order to determine what is really going on here, in addition to the one mentioned in the error message?

Answer 1

I was experiencing the same problem and the problem was that I hade several projects in the solution (Weband Droid) and even though Default project was choosen in the Package Manager Console it used the connection string from the Droid project:

PM> update-database -Verbose
Using StartUp project 'Droid'. <-- DROID
Using NuGet project 'Web'. <-- WEB
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
<!-- BAD TARGET DATABASE -->
Target database is: 'DefaultConnection' (DataSource: .\SQLEXPRESS, Provider: System.Data.SqlClient, Origin: Convention).
System.Data.SqlClient.SqlException (0x80131904): 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
   [REMOVED TEXT]
ClientConnectionId:00000000-0000-0000-0000-000000000000
Error Number:-1,State:0,Class:20
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

After setting the Startup Project to Web and the Default Project in Package Manger Console I got it to work.

Answer 2

I think you are using the Express Edition

Try to add "\SQLEXPRESS" to your server name

e.g. "MY-SERVER\SQLEXPRESS"

Answer 3

I found the following techniques helpful:

1. Make sure your database engine is configured to accept remote connections:

   • Start > All Programs > SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration
   • Click on Surface Area Configuration for Services and Connections
   • Select the instance that is having a problem > Database Engine > Remote Connections
   • Enable local and remote connections
   • Restart instance

2. You may need to create an exception on the firewall for the SQL Server instance and port you are using:

   • Start > Run > Firewall.cpl
   • Click on exceptions tab
   • Add sqlservr.exe (typically located in C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Bin, check your installs for the actual folder path) and port (default is 1433)
   • Check your connection string as well

   • From FIX : ERROR : Could not open a connection to SQL Server:

3. Check if your SQL server services is up and running properly:

   • Go to All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager > SQL Server Services
   • Check to make sure SQL Server service status is Running.

   In addition, ensure that your remote server is in the same network. Run sqlcmd -L to ascertain if your server is included in your network list.

4. Enable TCP/IP in SQL Server Configuration

   When two or more SQL Servers are connected across network they do all communication using TCP/IP. The default port of SQL Server installation is 1433. This port can be changed through SQL Server Configuration Manager. TCP/IP should be enabled for SQL Server to be connected.

   • Go to All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager >> Select TCP/IP
   • Right Click on TCP/IP >> Click on Enable

   You must restart SQL Server Services for all the changes to take effect. Right click and go to menu properties to select location where default port of SQL Server can be changed.

Answer 4

Why this error is so boresome and noisy, just because it can occur in varied situation.

I have done all approchs above here, and still being sucked. So make sure u have done the same as me before browsing downward.

Maybe I am not able to fix ur situation instantly, but I can point out a direction or thinking to u(The one who finally slide down here). I have started to ponder the error of my running program occurring after I made sure that the instance name is clearly right and set my database to allow remote control following the methods above. After then, I suspected something wrong happening in my code snippet of SQL connection.

Solution of my problem:

• Check my sqlconnection function

• Click to see its configuration

• New a connection

• Select ur server name

It works for me with pondering what exactly happen in the process of connection.Hope my thinking will lead u to kill ur error.

Answer 5

When I experienced this error in Visual Studio,

“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)”

...it was during the execution of the following C# code, which was attempting to obtain my SQL Server data to display it in a grid. The break occurred exactly on the line that says connect.Open():

        using (var connect = Connections.mySqlConnection)
        {
            const string query = "SELECT Name, Birthdate, Narrative FROM Friends";
            using (var command = new SqlCommand(query, connect))
            {
                connect.Open();
                using (var dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        // blah
                    }
                }
            }
        }

It was inexplicable because the SQL query was very simple, I had the right connection string, and the database server was available. I decided to run the actual SQL query manually myself in SQL Management Studio and it ran just fine and yielded several records. But one thing stood out in the query results: there was some improperly encoded HTML text inside a varchar(max) type field within the Friends table (specifically, some encoded comment symbols of the sort <!-- lodged within the "Narrative" column's data). The suspect data row looked like this:

Name    Birthdate    Narrative
====    =========    ============== 
Fred    21-Oct-79    <!--HTML Comment -->Once upon a time...

Notice the encoded HTML symbol "<", which stood for a "<" character. Somehow that made its way into the database and my C# code could not pick it up! It failed everytime right at the connect.Open() line! After I manually edited that one row of data in the database table Friends and put in the decoded "<" character instead, everything worked! Here's what that row should have looked like:

Name    Birthdate    Narrative
====    =========    ============== 
Fred    21-Oct-79    <!--HTML Comment -->Once upon a time...

I edited the one bad row I had by using this simple UPDATE statement below. But if you had several offending rows of encoded HTML, you might need a more elaborate UPDATE statement that uses the REPLACE function:

UPDATE Friends SET Narrative = '<!--HTML Comment -->Once upon a time...' WHERE Narrative LIKE '&lt%'

So, the moral of the story is (at least in my case), sanitize your HTML content before storing it in the database and you won't get this cryptic SQL Server error in the first place! (Uh, properly sanitizing/decoding your HTML content is the subject of another discussion worthy of a separate StackOverflow search if you need more information!)

Answer 6

I resolved this problem by setting the project that makes use of Entity Framework as the start-up project and then run the "update-database" command.