Introduction

One day, I was attempting to connect with MS SQL Server but it didn't succeeded not even after making attempt once more and once more. I ran into error provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server on server. I'm providing you all necessary steps to resolve issue error: 40 - Could not open a connection to SQL Server. To succeed this issue, I would recommend to experience gave all strides one by one until your issue get resolve. This error comes out in many ways so that you need to check out all steps provided in this article until you succeed your issue.  

Detail Error Description:

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)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: 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)
Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1016



Step 1:    First Step check your SQL services are running fine.

Step 2:    Click on “Start Window” and expand “Microsoft SQL Server 2008 R2” and click on “SQL Server Configuration Manager”.

SQL-Server-Configuration-Manager

Step 3:   Now click on left pane “SQL Server Services” and check for “SQL Server (SQLEXPRESS)” running or not, if it is experiencing in green colour then it’s working fine.


SQL Server (SQLEXPRESS)



Step 4:  Now check for “SQL Server (MSSQLSERVER)”, if it's experienceing  be in green color means running fine.

SQL Server (MSSQLSERVER)

Step 5: Now check for “SQL Server Browser” running or not, you've to make sure it's green marked.


SQL Server Browser


Step 6: Now click on “Aliases“ left pane, make assure on right pane that  there should be empty aliases, if requires then should recreate from fresh.

Aliases

Step 7: Now check for SQL Server Default Portal 1433, if you have not already added then follow to open “Ctrl + R”, type “Firewall.cpl” then Firewall will open and Click on “Advanced Settings”.

Advanced Settings


Step 8:
Click on “Inbound Rules” in left pane and click on right pane “New Rule”.

New Rule


Step 9: Then Click on “Protocol and Ports” and click on “Specific local ports” and write SQL default Port No “1433”.

Protocol Ports


Step 10: Now write name on SQL Port Name and click on “Finish” button.

 Add Port Name

Step 11: Now click on “Client Protocols” in left pane, next click on right pane “TCP/IP” and click on “Property” then you check that your default Port “1433” has been populated.

SQL Port 1433



Step 12: Now Open “SQL Server Management Studio” and right click, now property window open and click on “Property”.


SQL Server Management Studio

Step 13: Now click on “Connections” option and Check option “Allow remote connections to this server” and click now on “OK”.


Allow remote connections to this server

Step 14: You have to Ping for your IP Host Address on your command prompt  "cmd" console.

Ping IP Host

Step 15: Check for Firewall blocking SQL Server Port 1433

Step 16: If you have already access to development machine, production server then it'll be helpful greatly. Press (Windows + R) to open Run window to launch program quickly.  Now type “EVENTVWR” and a new window'll be open, now expand left pane, you'll be able to check here “Windows Log” to look into issue very closely and specifically.

Step 17: We can use also Netstat Command to display  how communicating with other computers or networks.

netstat [-a] [-b] [-e] [-f] [-n] [-o] [-p protocol] [-r] [-s] [-t] [-x] [-y] [time_interval] [/?]


Netstat Command


Authentication And Host Name Setting (SQL Server Error 25 And 27)

I am getting few queries routinely regarding authentication and host name resulting issue into SQL error No 25 & 27 to resolve these issues attempt below points and I posted this error SQL Server Error 25 And 87 with more detail in my earlier article.
  • Verify your Authentication whether it's Windows or SQL Server
  • If using local SQL database then you'll able to use . (dot) only instead of server name. 
  • Enter your correct host name (hostname\SQLEXPRESS), myhostname\SQLEXPRESS (in case of SQL Server Express) or (myhostname\mssqlserver). 
  • You'll also attempt alternatively (localhost\SQLEXPRESS) or (localhost\mssqlserver).

Conclusion

I have attempted my best to incorporate all fixing to dispose of this quite common issue of error: 40 – Could not open a connection to SQL. You'll keep posting me up message, if you still continue to face any further issue.

Suggested Links:

Video:Could not open a connection to SQL Server