[Solved] SQL Server ‘Network-related or instance-specific error’

“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: 53)”.

The article discusses a thorough list of troubleshooting methods that can be used to connect to the SQL server. First of all, we will discuss problems that arise when you need to connect to the remote server using IP address as this is the most common cause. These steps are written in “SQL Server 2008 R2″ on “Windows 10″, but they can be used on other versions too with minor amendments.

The error usually means that the “SQL server is not found” or “TCP port is either unknown or wrong”, or it can be blocked by the “firewall”.

Method 1: Gather information about the instance of the SQL Server.

In this section, we will discuss ways to check either the instance of the SQL Server is working or not, along with methods to fix it if it is not working.

Step 1. Check if an instance of SQL Server is installed and is working or not

First of all login to the computer hosting the SQL server instance.  Now, follow these steps to open Services in Windows.

1. Click on the “Start menu” and then point to “All programs”.
2. Now point to the SQL Server, and then point to “Configuration tools”
3. Click “SQL Server Configuration Manager”.
4. Now select “SQL Server services” and check in the right pane whether the instance of a database engine is running or not.
5. Moreover, this can be opened directly by typing “services.msc” in the RUN and click OK. The following screen appears.

Now, check if the database engine has been configured to accept remote connections. To check this, follow the following steps.

1.  After services have been opened you can see database engine in the right pane. The “MSSQLSERVER” is a default unnamed instance. A default instance can only be one.
2. In the case of the “SQL Express”, default instance will be “SQLEXPRESS” unless it is renamed by someone during installation.
3. Check if the instance you are trying to connect has the same name as given in the services.
4. Also, confirm if the status of the instance is “RUNNING”.
5. Moreover, if you are trying to connect to named instant, then double-check if “SQL Server Browser service” is already running. Thus you need to check if the “SQL Server Browser service” is started on the server on which SQL Server is installed.
6. In case, the database engine is not running then you need to restart it. So to start the “Database Engine”, in the right pane, right-click on the “Database Engine” (“MSSQLSERVER” default one), and then click “Start”.

Step 2. Obtain the IP address of the computer.

To do this follow these steps.

1. First of all, from the start menu, click “RUN” and type “cmd” and press ok.
2. In command prompt window type “ipconfig” and note down IPV4 and IPV6 addresses. People mostly use IPV4 address.

Step 3. Get the TCP port number used by the SQL server

Follow the following steps to get TCP port number used by the SQL server

1. Using “SQL Server Management Studio” (SSMS) connect to the instance of SQL server
2. From “object explorer” expand “Management”, expand “SQL server log” and click on the current log on which you have to apply filter.
3. To apply filer click apply filter and type ” server is listening on” in Message contains text box. Click apply filter and press ok.

   

4. A message like “server is listening on [‘any’ <ipv4> 1433]” should be shown. The message shows that the SQL Server instance is listening on all computers with IP address IPv4 and TCP port is 1433 (default).
5. For more than one instance TCP port will be different for each instance.

   

6. If it is not a case then click “All programs”, point to MS SQL server configuration tools, “SQL server configuration management” , and right-click “TCP\IP” and click enable and restart SQL server to let changes create impact.

Method 2: Enabling protocols for port 1433

Connecting to the “Database Engine” from another computer is not allowed in many “SQL Server” implementations unless an administrator utilizes “Configuration Manager” to allow it. The following steps should be followed to do this.

1. Click on “Start menu” and then point to “All programs”
2. Point towards the “SQL Server 2008 R2”
3. Point towards “Configuration tools”, and after this click “SQL Server Configuration Manager”.
4. Expand “SQL Server Network Configuration”.
5. Select “protocols for MSSQL server”. Click on “TCP\IP” in the right panel.

   

6. In the tab “protocol” set enable as “yes”.
7. Choose the “IP Address tab” from the window and set “TCP Port” equal to “1433″ in the “IP All” entry.

   

8. Now restart database engine to make changes leave their impact. To do this from the left pane, select SQL server services and then from right pane right-click database engine instance and press “restart”.

Method 3: Create a Firewall exception

Sometimes Windows firewall turns on and blocks links from another computer. To fix it follow these steps.

1. Click “Start” and start typing “Firewall.cpl” in the run box.

   

2. You get the “configuration frame” for Windows Firewall by running the “firewall.cpl” command. You may turn the firewall “on/off” with exceptions and other settings applied here. Check the firewall status and turn it on to activate it if the firewall is off. If you’ve just turned this on, your firewall will block any “SQL Server” connection request to your computer at this point. Through making certain exceptions, you’d need to configure the firewall to allow access to a SQL Server database engine.
3. Click on “Advanced Settings”

   

4. We need to learn about the ports used for “SQL Server” and the “SQL Server Browser” feature when dealing with “SQL Server” firewall configurations. Both are involved in setting up a “firewall” for the “SQL Server” . It would, therefore, be necessary to go separately through both concepts.
5. You may permit or block traffic attempts that meet the requirements in the rule to access the computer. By default  “inbound traffic” is blocked, you need to establish “inbound rule” to allow traffic to reach the computer. Tap the Inbound Rules from the left pane of the “Windows Firewall with Advanced Security” and click the New Rule from the “Actions” window. 

   

6. Select “Port” under “Rule Type” and press “Next” button

   

7. Now select “Specific local ports” and set it to 1433

   

8. Now select “Allow the connection” in the “Action” dialog and press the Next button

   

9. Give the rule a “title” on this stage and press the “Finish” button.

   

10. Select “Custom rule” from “New rule” tab

    

11. Click “customize”

    

12. Select “Database Engine Instance Service” from the “Customize Service Settings” under “Apply to this service” and click the “OK” button

    

13. Give the rule a name and click finish

    

14. Also add “sqlservr.exe” typically located in “C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Bin” (or check your actual folder path) to the path, check your installs for the actual folder path) and port whose default value is “1433”. Also, check your connection string.

Method 4: Check Local connection

One of the reasons for this error is if we provide the wrong server name, this will result in an error. As seen in the figure below provided server name is “DESKTOP-UD88TLT1” whereas accurate server name is “DESKTOP-UD88TLT”. So it will be unable to connect to the server which will result in an error “cannot connect to server”. This is the most basic reason for error, so we should check it first if working locally.

The error arises while locally connecting to SQL server with the wrong server name In case you are using express edition following your server name, add “\SQLEXPRESS” as seen in the figure below.