Ошибка network related or instance specific error - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

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    &lt;!--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!)

Источник

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

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

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.

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

Opening services by typing “services.msc” in the RUN box.

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

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.
In the case of the “SQL Express”, default instance will be “SQLEXPRESS” unless it is renamed by someone during installation.
Check if the instance you are trying to connect has the same name as given in the services.
Also, confirm if the status of the instance is “RUNNING”.
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.
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”.

Check if the “SQL Server Browser service” is already running.

Step 2. Obtain the IP address of the computer.

To do this follow these steps.

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

Get 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

Using “SQL Server Management Studio” (SSMS) connect to the instance of SQL server
From “object explorer” expand “Management”, expand “SQL server log” and click on the current log on which you have to apply filter.
To apply filer click apply filter and type ” server is listening on” in Message contains text box. Click apply filter and press ok.
Applying filter ” server is listening on”
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).
For more than one instance TCP port will be different for each instance.
Message showing server is listening on IPv4 and port 1433
If it is not a case then click “All programs”, point to MS SQL server configuration tools, “SQL server configuration management” , and right-click “TCPIP” 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.

Click on “Start menu” and then point to “All programs”
Point towards the “SQL Server 2008 R2”
Point towards “Configuration tools”, and after this click “SQL Server Configuration Manager”.
Expand “SQL Server Network Configuration”.
Select “protocols for MSSQL server”. Click on “TCPIP” in the right panel.
Opening “Protocol Tab”
In the tab “protocol” set enable as “yes”.
Choose the “IP Address tab” from the window and set “TCP Port” equal to “1433″ in the “IP All” entry.
Set port no in “IP Address tab”
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.

Click “Start” and start typing “Firewall.cpl” in the run box.
Opening “Firewall.cpl”
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.
Click on “Advanced Settings”
Click on advanced settings option to open firewall rules
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.
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.
Selecting New Rule from the “Actions” window.
Select “Port” under “Rule Type” and press “Next” button
Selecting “port” option
Now select “Specific local ports” and set it to 1433
set “specific local port” to 1433
Now select “Allow the connection” in the “Action” dialog and press the Next button
selecting “Allow the connection”
Give the rule a “title” on this stage and press the “Finish” button.
Give a title to the rule
Select “Custom rule” from “New rule” tab
Select “Custom rule” from “New rule” tab
Click “customize”
Click “customize”
Select “Database Engine Instance Service” from the “Customize Service Settings” under “Apply to this service” and click the “OK” button
Select “Database Engine Instance Service” from the “Customize Service Settings” under “Apply to this service” and click the “OK” button
Give the rule a name and click finish
Give a title to the new rule
Also add “sqlservr.exe” typically located in “C:Program Files (x86)Microsoft SQL ServerMSSQL.xMSSQLBin” (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.

Locally connecting to SQL server while using express edition

Sadia Majeed

Sadia is an Enthusiastic and experienced professional with technical expertise in multiple tools and technologies including database, data ware housing, business intelligence and data science.

Источник

Remove From My Forums

Question
I’ve just installed sql server 2008 r2 and i restored my DB, and i receive this error:

I have got the above 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)ExecuteNonQuery requires an open and available Connection. The connection’s current state
is closed.

Please Help Me.

Thanks,

Harish
- Edited by
  
  Thursday, December 13, 2012 1:52 PM

Answers

If you are running Visual Studio on your local machine, we can forget all about firewalls and enabling of TCP/IP. That matters when you connect from a different machine.

So let’s focus on how you connect from Visual Studio? Can you include a screenshot.

There is some puzzling information in your post. I assume that your machine name is CEO-PC and not CEO-PC/SQLSERVER? When you connect to a named instance, you should use a backslash () as the separator, not a forward slash. Furthermore,
the default name for an instance of Express Edition is SQLEXPRESS, and this is confirmed by your output from SQL Server Configuration Manager.

If you are attempting to connect from Visual Studio with CEO-PC/SQLSERVER, try CEO-PCSQLEXPRESS.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Marked as answer by
  Allen Li — MSFT
  Wednesday, December 19, 2012 1:41 AM
I am connecting with DATA SOURCE as CEO-PC/SQLEXPRESS. This is my connection string.

Data Source=CEO-PC/SQLEXPRESS;Initial Catalog=Test;Integrated Security=True;

Tilt the slash.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Marked as answer by
  Allen Li — MSFT
  Wednesday, December 19, 2012 1:41 AM

Источник

In this article, we will be discussing about a popular error message, that is: “A network-related or instance-specific error occurred while establishing a connection to SQL Server”

Under certain circumstances, there are cases where you might try to connect to a SQL Server instance and get the error message: “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) (Microsoft SQL Server, Error: -1)”

Sadia Majeed

Sadia is an Enthusiastic and experienced professional with technical expertise in multiple tools and technologies including database, data ware housing, business intelligence and data science.

Источник

Remove From My Forums

Question
I’ve just installed sql server 2008 r2 and i restored my DB, and i receive this error:

I have got the above 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)ExecuteNonQuery requires an open and available Connection. The connection’s current state
is closed.

Please Help Me.

Thanks,

Harish
- Edited by
  
  Thursday, December 13, 2012 1:52 PM

Answers

If you are running Visual Studio on your local machine, we can forget all about firewalls and enabling of TCP/IP. That matters when you connect from a different machine.

So let’s focus on how you connect from Visual Studio? Can you include a screenshot.

There is some puzzling information in your post. I assume that your machine name is CEO-PC and not CEO-PC/SQLSERVER? When you connect to a named instance, you should use a backslash () as the separator, not a forward slash. Furthermore,
the default name for an instance of Express Edition is SQLEXPRESS, and this is confirmed by your output from SQL Server Configuration Manager.

If you are attempting to connect from Visual Studio with CEO-PC/SQLSERVER, try CEO-PCSQLEXPRESS.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Marked as answer by
  Allen Li — MSFT
  Wednesday, December 19, 2012 1:41 AM
I am connecting with DATA SOURCE as CEO-PC/SQLEXPRESS. This is my connection string.

Data Source=CEO-PC/SQLEXPRESS;Initial Catalog=Test;Integrated Security=True;

Tilt the slash.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Marked as answer by
  Allen Li — MSFT
  Wednesday, December 19, 2012 1:41 AM

Источник

In this article, we will be discussing about a popular error message, that is: “A network-related or instance-specific error occurred while establishing a connection to SQL Server”

This is the standard error message you get when your client does not “see” the SQL Server instance you are trying to connect to. However, there is more than one possible reasons for getting this message.

Possible Reasons for the network-related or instance-specific error

Here’s a list of possible reasons:

[Reason 1] You may have misspelled the instance name or IP 🙂
- How to further investigate:
  From the client, try to ping the network name for the SQL Server instance and see if you get a response.
  - If it is a default instance (i.e. SQLInstance1.contoso.local) then ping “SQLInstance1.contoso.local”
  - If it is a named instance (i.e. VirtualName1.contoso.localSQLInstance1) then ping “VirtualName1.contoso.local”
    > If ping does not reply, make sure that you are using the correct network name.
    > If you still cannot connect to SQL Server, proceed and check the rest of possible reasons.

[Reason 2] If the SQL Server instance is located on a different domain than your client, you may have not included the fully qualified domain name (FQDN) in the SQL Server instance name (i.e. SQLInstance1 vs SQLInstance1.contoso.local) – this assumes that the two domains are trusted between them
- How to further investigate:
  Again, just like the actions in possible reason 1, try to ping the instance name from the client using the instance’s FQDN.
  > If you still cannot connect to SQL Server, proceed and check the rest of possible reasons.

[Reason 3] If there is a firewall in your network, you might not be allowed to access the SQL Server instance through the network.
- How to further investigate:
  If you know for example that you want to connect to the SQL Server instance “SQLInstance1.contoso.local” on port 52500, then you can try from a command prompt on the client, to run the command: telnet SQLInstance1.contoso.local 52500
  If the above telnet command reports that “Could not open connection to the host, on port 52500: Connect failed” (or something similar), it means that there is no network communication between the client and the SQL Server instance at the designated port number. In this case, you should report it to your Network administrator.

[Reason 4] If you are trying to connect to a named instance, make sure that the SQL Server Browser service is running.
- How to further investigate:
  Remotely connect on the SQL Server machine (or via remote administration) and check that the “SQL Server Browser” service is up and running.

[Reason 5] Make sure that your SQL Server instance allows remote connections.
- How to further investigate:
  Connect remotely on the SQL Server machine, and log in to SQL Server Database Engine via SQL Server Management Studio. Then, right-click on the instance name, select “Properties” and check the “Connections” tab to see if the SQL Server instance allows remote connections. See the below screenshot for example.

Hopefully if you go through the above, you will finally manage to successfully connect to your SQL Server instance.

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Enroll from $12.99

Featured Online Courses:

Boost SQL Server Database Performance with In-Memory OLTP
Essential SQL Server Administration Tips
SQL Server Fundamentals – SQL Database for Beginners
Essential SQL Server Development Tips for SQL Developers
The Philosophy and Fundamentals of Computer Programming
.NET Programming for Beginners – Windows Forms with C#
Introduction to SQL Server Machine Learning Services
Introduction to Azure SQL Database for Beginners
SQL Server 2019: What’s New – New and Enhanced Features
Entity Framework: Getting Started – Complete Beginners Guide
How to Import and Export Data in SQL Server Databases
Learn How to Install and Start Using SQL Server in 30 Mins
A Guide on How to Start and Monetize a Successful Blog

Related Error Messages and ways to Resolve them:

Operating System Error 170 (Requested Resource is in use)
There is no SQL Server Failover Cluster Available to Join
Setup failed to start on the remote machine. Check the Task scheduler event log on the remote machine.
SQL Server 2008 R2 Service Pack Installation Fails – Element not found. (Exception from HRESULT: 0x80070490)
Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=10.0.0.0, …
The operation failed because either the specified cluster node is not the owner of the group, or the node is not a possible owner of the group
There is not enough space on the disk. (mscorlib)
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
… more SQL Server troubleshooting articles

Check out our latest software releases!

Subscribe to our newsletter and stay up to date!

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.

Rate this article: 1 Star 2 Stars 3 Stars 4 Stars 5 Stars (3 votes, average: 5.00 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit «Cookie Settings» to provide a controlled consent. Read More

Источник

When you are trying to connect to a SQL Server using Microsoft SQL Server Management Studio, you may see the following message:

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 the SQL Server is configured
to allow remote connections. (provided: Named Pipes Provider, error: 40- Could not open a connection to the SQL Server) (Microsoft SQL Server, Error: 2).

While the message tells you to check some of the items that can cause this issue, it fails to mention that if your SQL Server Service is not started, then you cannot connect. One of the first things that you should check is that the SQL Server (MSSQLSERVER)
is started. You can go to the Services Console (services.msc) and look for SQL Server (MSSQLSERVER) to see that it is started. If not, then start the service.

You could also do this through an
elevated command prompt by typing net start mssqlserver.

If the service is started and you still cannot connect, you should check all the connection points. For detailed steps on troubleshooting this issue, see
Resolving SQL Connection
Errors Step-by-Step by Pinal Dave.

Additional Resources

SQL Events and Errors

Источник

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

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

Step 2. Obtain the IP address of the computer.

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

Method 2: Enabling protocols for port 1433

Method 3: Create a Firewall exception

Method 4: Check Local connection

Sadia Majeed

Question

Answers

Sadia Majeed

Question

Answers

Possible Reasons for the network-related or instance-specific error

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Featured Online Courses:

Related Error Messages and ways to Resolve them: