Network related or instance specific error occurred while establishing a connection to sql server

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!)

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!)

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

Symptoms

When you repair a SQL Server 2016 installation in the following scenarios:

You sign on to Windows as a user who is not a member of SQL Server Administrators.

The SQL Server service is stopped.

In either of the preceding scenarios, during the repair progress SQL_Telemetry_LoginRepair_Startup_Cpu64, you may receive an error message in a pop-up window that resembles the following:

Microsoft SQL Server 2016 Setup
The following error has occurred:
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) Click ‘Retry’ to retry the failed action, or click ‘Cancel’ to cancel this action and continue setup.

Note If you click Cancel to close the pop-up window, the repair can still finish successfully.

Resolution

The issue was first fixed in the following cumulative update of SQL Server:

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. We recommend that you download and install the latest cumulative updates for SQL Server:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.

Learn about the terminology that Microsoft uses to describe software updates.

Источник

Applies to: В SQL Server

When connecting to a SQL Server instance, you may encounter one or more of the error messages below. This article provides some steps to help you troubleshoot these errors, which are provided in order of the issues from simple to complex.

Error messages

The complete error messages vary depending on the client library that is used in the application and the server environment. You can check the following details to see if you’re encountering one of the following error messages:

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 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)
provider: TCP Provider, error: 0 — No such host is known. (Microsoft SQL Server, Error: 11001)

provider: SQL Network Interfaces, error: 26 — Error Locating Server/Instance Specified 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

Login timeout expired SQL Server Native Client Data Link Error
[Microsoft SQL Server Native Client 10.0]: Login timeout expired
[Microsoft SQL Server Native Client 10.0]: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[Microsoft SQL Server Native Client 10.0]: SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 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: TCP Provider, error: 0
A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
Microsoft SQL Server, Error: 10060

provider: Named Pipes Provider, error: 40 — Could not open a connection to SQL Server 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 network path was not found

[Microsoft][SQL Server Native Client 11.0]TCP Provider: No connection could be made because the target machine actively refused it SQL Server Native Client Data Link Error
[Microsoft][SQL Server Native Client 11.0]TCP Provider: No connection could be made because the target machine actively refused it.
[Microsoft][SQL Server Native Client 11.0]Login timeout expired.
[Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

«SQL Server does not exist or access denied»

This error usually means that the client can’t find the SQL Server instance. This issue occurs when at least one of the following problems exists:

• The name of the computer hosting SQL Server is incorrect.
• The instance doesn’t resolve the correct IP.
• The TCP port number isn’t specified correctly.

For troubleshooting connectivity issues in high availability scenarios, see the following articles:

Gather information for troubleshooting the error

We recommend that you gather the information listed in this section using one of the options below before proceeding with the actual steps to troubleshoot the error.

Option 1: Use the SQL Check tool to gather the required information

If you can sign in locally to the SQL Server computer and have administrator access, use SQLCheck from the Microsoft SQL Networking GitHub repository. This tool provides most of the information required for troubleshooting in one file. Review the tool’s home page for more information on using the tool and the information it gathers. You can also check the recommended prerequisites and checklist page.

Option 2: Collect the data individually using the following procedures

Get the instance name from Configuration Manager

On the server that hosts the SQL Server instance, use SQL Server Configuration Manager to verify the instance name:

Configuration Manager is automatically installed on the computer when SQL Server is installed. Instructions on starting Configuration Manager vary slightly by versions of SQL Server and Windows. For version-specific details, see SQL Server Configuration Manager.

Sign in to the computer hosting the instance of SQL Server.

Start SQL Server Configuration Manager.

In the left pane, select SQL Server Services.

In the right pane, verify the name of the instance of the database engine.

• SQL SERVER (MSSQLSERVER) indicates a default instance of SQL Server. The name of the default instance is .
• SQL SERVER ( ) indicates a named instance of SQL Server. The name of the named instance is .

Get the IP address of the server

You can use the following steps to get the IP address of the computer hosting the instance of SQL Server.

On the Start menu, select Run. In the Run window, type cmd, and then select OK.

In the Command Prompt window, type ipconfig/all and then press Enter. Note down the IPv4 address and the IPv6 address.

SQL Server can connect by using either IP version 4 protocol or IP version 6 protocol. Your network could allow either or both.

Get the TCP port of the instance

In most cases, you connect to the Database Engine on another computer by using the TCP protocol. To get the TCP port of the instance, follow these steps:

Use SQL Server Management Studio on the computer running SQL Server and connect to the instance of SQL Server. In Object Explorer, expand Management, expand SQL Server Logs, and then double-click the current log.

In the Log File Viewer, select Filter on the toolbar. In the Message contains text box, type server is listening on, select Apply filter, and then select OK.

A message like Server is listening on [ ‘any’ 1433] should be listed.

This message indicates that the instance of SQL Server is listening on all IP addresses on this computer (for IP version 4) and TCP port 1433. (TCP port 1433 is usually the port that’s used by the Database Engine or the default instance of SQL Server. Only one instance of SQL Server can use this port. If more than one instance of SQL Server is installed, some instances must use other port numbers.) Note down the port number used by the SQL Server instance that you’re trying to connect to.

• IP address 127.0.0.1 is probably listed. It’s called the loopback adapter address. Only processes on the same computer can use the IP address to connect.
• You can also view the SQL Server error log by using a text editor. By default, the error log is located at Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG and ERRORLOG.n files. For more information, see Viewing the SQL Server error log.

Step 1пјљVerify that the instance is running

Option 1: Using the output file from the SQLCheck tool

Search the output from SQLCheck file for «SQL Server Information».

In the section titled «Services of Interest», find your SQL Server instance under Name and Instance (for named instances) columns and check its status by using Started column. If the value is True, the services are started. Otherwise the service is currently not running.

If the service isn’t running, start the service by using either SQL Server management studio, SQL Server Configuration manager, PowerShell, or Services applet.

Option 2: Use SQL Server Configuration Manager

To verify that the instance is running, select SQL Server Services in SQL Server Configuration Manager and check the symbol by the SQL Server instance.

• A green arrow indicates that an instance is running.
• A red square indicates that an instance is stopped.

If the instance is stopped, right-click the instance and select Start. Then, the server instance starts, and the indicator becomes a green arrow.

Option 3: Use PowerShell commands

You can use the following command in PowerShell to check the status of SQL Server services on the system:

You can use the following command to search the error log file for the specific string «SQL Server is now ready for client connections. This is an informational message; no user action is required.»:

Step 2: Verify that the SQL Server Browser service is running

This step is required only for troubleshooting connectivity issues with named instances.

Option 1: Using the output file from SQLCheck tool

Search the output from SQLCheck file for «SQL Server Information».

In the section titled «Services of Interest», search for SQLBrowser in the Name column and check its status using the Started column. If the value is True, the service is started. Otherwise, the service is currently not running, and you need to start it. For more information, see Start, stop, pause, resume, restart SQL Server services.

Option 2: Use SQL Server Configuration Manager

To connect to a named instance, the SQL Server Browser service must be running. In SQL Server Configuration Manager, locate the SQL Server Browser service and verify that it’s running. If it’s not running, start the service. The SQL Server Browser service isn’t required for default instances.

For more information on using SQL Server Browser service in your environment, see SQL Server Browser service.

For more information on stopping and starting SQL Services, see Start, stop, pause, resume, restart SQL Server services.

If you can’t have the SQL Server Browser service running in your environment, see Connecting to SQL server named instance without SQL Server browser service.

Step 3: Verify the server name in the connection string

You often encounter errors when an incorrect server name is specified in the connection string. Make sure that the server name matches the one that you retrieved in the previous steps.

If you are using the SQLCheck tool, review the NetBios Name/FQDN values in the Computer Information section of the output file.

• For examples on connection strings, see SQL Server Connection Strings.
• For more detailed examples, see Proof of concept connecting to SQL using ADO.NET under Homepage for SQL client programming.

Step 4: Verify the aliases on the client machines

Aliases are often used in client environments when you connect to SQL Server with an alternate name or when there are name resolution issues in the network. They’re created by using SQL Server Configuration Manager or client network utility. An incorrect alias can cause the connections from your applications to connect to the wrong server, resulting in failure. Use the following methods to check for incorrect aliases. You can also use a tool (such as SQLCHECK) on the client machine to check for aliases and various other connectivity-related settings on a client machine.

The following options only apply to the applications that use SQL Server Native Client to connect to SQL Server.

Option 1: Using the output file from the SQLCheck tool

In the SQLCheck output file, search for the string SQL Aliases. (This string will be inside the Client Security and Driver Information section of the file)

Review the entries in the table. If there’s none present, there are no aliases on the computer. If there’s an entry, review the information to ensure the server name and port number are set to the correct values.

Example output:
SQL Aliases:

The above indicates that prodsql is an alias for a SQL Server called prod_sqlserver that is running on port 1430.

Option 2: Check aliases in SQL Server Configuration Manager

1. In SQL Server Configuration Manager, expand SQL Server Native Client Configuration, and select Aliases.
2. Check whether any aliases are defined for the server that you’re trying to connect to. If the aliases exist, follow these steps:
   1. Open the Properties pane of the alias.
   2. Rename the value in the Alias Name field (for example, if your server name is MySQL, rename it as MySQL_test) and retry the connection. If the connection works, your alias is incorrect and may come from an old configuration that is no longer needed. If the connection doesn’t work, rename the alias back to its original name and go to the next step.
   3. Check the connection parameters for the alias and make sure that they’re correct. The following common scenarios can cause connectivity problems:

   Incorrect IP address for the Server field. Make sure that the IP address matches the entry in the SQL Server error log file.

   Incorrect server name in the Server field. For example, your server alias points to the correct server name. However, the connections will fail if the value of the server name parameter is incorrect.

   Incorrect pipe name format (assuming that you use a named pipes alias).

   • When connecting to a default instance named Mydefaultinstance, the pipe name should be \Mydefaultinstancepipesqlquery.
   • When connecting to a named instance MySQLNamed, the pipe name should be \MySQLpipeMSSQL$Namedsqlquery.

Option 3: Check aliases in SQL Server Client Network Utility

1. Open SQL Server Client Network Utility by typing cliconfg.exe in your Run command.
2. Follow step 2 in Option 2: Check aliases in SQL Server Configuration Manager.

Step 5: Verify the firewall configuration

You can verify the firewall configuration depending on the default instance or named instance.

If you are using third party firewalls in your network, the concepts still apply. However, you may have to work with your network administrator or consult the firewall product’s documentation for more information on configuring the firewall to allow necessary ports for communication with SQL Server.

Default instance of SQL Server

A default instance typically runs on port 1433. Some installations also use a non-standard port (other than 1433) to run SQL instances. The firewall may block either port. To check the port number further, follow these steps:

1. Determine the port your SQL instance is running on, see Get the TCP port of the instance.

If your SQL Server is configured to listen on port 1433, make sure that firewalls on the network between the client and the server allow traffic on that port. Review Configure a Windows Firewall for Database Engine Access and work with your network administrator to implement necessary solutions.

If your SQL Server default instance isn’t using 1433, try to append the port number of SQL Server to the server name by using the format ,

and see whether it works. For example, your SQL instance name is MySQLDefaultinstance and it’s running on port 2000. Specify the server name as MySQLServer, 2000 and see whether it works.

If it doesn’t work, it indicates the firewall is blocking the port. You can follow the instructions at Configure a Windows Firewall for Database Engine Access or work with your network administrator to add the port to the firewall exclusion list.

• If it does work, it indicates that the firewall is allowing communication through that port. You need to change your connection string in order to use the port number and your server name in the connection string of your application.

Named instance of SQL Server

If your SQL instance is a named instance, it may be configured to use either dynamic ports or a static port. In either case, the underlying network libraries query the SQL Server Browser service running on your SQL Server machine through UDP port 1434 to enumerate the port number for the named instance. If a firewall between the client and the server blocks this UDP port, the client library can’t determine the port (a requirement for connection) and the connection fails. To check the connection, you can use one of the following methods:

Method 1: Check connection by specifying the port number in your connection string.

Determine the port your SQL instance is running on, see Get the TCP port of the instance.

Try to connect to the named instance by using the port number appended to the server name in the format ,

and see if that works. For example, if your SQL instance name is MySQLNamedinstance and it’s running on port 3000, specify the server name as MySQLNamedinstance,3000.

If it does work, it indicates the firewall is blocking the UDP port 1434 or the instance is hidden from SQL Server Browser.

If it doesn’t work, it indicates one of the following situations:

Either UDP port 1434 is blocked or the static port is blocked, or both. To confirm whether it’s the UDP port or the static port, use Portqry.

The instance is hidden from the SQL Server Browser service.

Method 2: Check the connection by using the PortQryUI tool.

Use the PortQryUI tool with your named instance and observe the resulting output. You may see a message that the UDP port 1434 is filtered. This message indicates that the port is blocked on the network. For instructions on how to use the tool, see Using the PortQryUI Tool with SQL Server.

Determine whether the SQL Server instance is listening on dynamic or static ports. Then use the following method that is relevant to your scenario. If you aren’t sure, see How to check if SQL Server is listening on a dynamic port or static port.

Scenario 1: Dynamic ports. In this case, ensure that the SQL Server Browser service is started and UDP port 1434 isn’t blocked on the firewall between the client and the server. If you can’t do either of these things, you should switch your SQL Server instance to a static port and use the procedure documented in Configure a Server to Listen on a Specific TCP Port.

Scenario 2: Static port configuration. Either SQL Server Browser isn’t running or UDP 1434 can’t be opened on the firewall. In this case, make sure to specify the static port in your connection string and that the firewall doesn’t block the port. For more information, review Configure a Windows Firewall for Database Engine Access.

Step 6: Verify the enabled protocols on SQL Server

In some installations of SQL Server, connections to the Database Engine from another computer aren’t enabled unless an administrator manually enables them. You can use one of the following options to check and enable the necessary protocols to allow remote connections to SQL Server Database Engine.

Option 1: Using the output file from SQLCheck tool

Search the SQLCheck output file for «Details for SQL Server instance» section and locate the information section for your SQL Server instance.

In the section, find the values listed in the following table to determine if the SQL Server protocols are enabled:

Enable required protocols by using SQL Server Configuration Manager or SQL Server PowerShell. For more information, see Enable or Disable a Server Network Protocol.

After enabling a protocol, the Database Engine must be stopped and restarted for the change to take effect.

Option 2: Use SQL Server Configuration Manager

To enable connections from another computer by using the SQL Server Configuration Manager, follow these steps:

Open the SQL Server Configuration Manager.

In the left pane, expand SQL Server Network Configuration, and then select the instance of SQL Server that you want to connect to. The right pane lists the connection protocols available. Shared Memory is normally enabled. It can only be used from the same computer, so most installations leave Shared Memory enabled. To connect to SQL Server from another computer, use TCP/IP. If TCP/IP isn’t enabled, right-click TCP/IP, and then select Enable.

If you change the enabled setting for any protocol, restart the Database Engine. In the left pane, select SQL Server Services. In the right-pane, right-click the instance of the Database Engine, and then select Restart.

Step 7: Test TCP/IP connectivity

Connecting to SQL Server by using TCP/IP requires that Windows establish the connection. You can use the following steps to test TCP connectivity by using the ping tool.

On the Start menu, select Run. In the Run window, type cmd and select OK.

In the Command Prompt window, type ping and the IP address of the computer that’s running SQL Server. For example:

• IPv4: ping 192.168.1.101
• IPv6: ping fe80::d51d:5ab5:6f09:8f48%11

If your network is configured properly, ping returns Reply from followed by some additional information. If ping returns Destination host unreachable or Request timed out , TCP/IP isn’t correctly configured. Errors at this point indicate a problem with the client computer, the server computer, or something about the network such as a router. To troubleshoot network problems, see Advanced troubleshooting for TCP/IP issues.

If the ping test succeeds by using the IP address, test whether the computer name can be resolved to the TCP/IP address. On the client computer, in the Command Prompt window, type ping and the name of the computer that’s running SQL Server. For example, ping newofficepc .

If ping to the IP address succeeds, but ping to the computer name returns Destination host unreachable or Request timed out , you might have old (stale) name resolution information cached on the client computer. Type ipconfig /flushdns to clear the DNS (Dynamic Name Resolution) cache. Then ping the computer by name again. When the DNS cache is empty, the client computer checks the latest information about the IP address for the server computer.

If your network is configured properly, ping returns Reply from followed by some additional information. If you can successfully ping the server computer by IP address but receive an error such as Destination host unreachable or Request timed out when pinging by computer name, then name resolution isn’t correctly configured. For more information, see how to Troubleshoot Basic TCP/IP Problems. Successful name resolution isn’t required to connect to SQL Server. However, if the computer name can’t be resolved to an IP address, connections must be made to specify the IP address. Name resolution can be fixed later.

You can also use either Test-NetConnection or Test-Connection cmdlet to test TCP connectivity according to the PowerShell version that’s installed on the computer. For more information on PowerShell cmdlet, see Cmdlet Overview.

Step 8: Test local connection

Before troubleshooting a connection problem from another computer, test your ability to connect from a client application installed locally on the computer that is running SQL Server. Local connection avoids issues with networks and firewalls.

This procedure requires SQL Server Management Studio. If you don’t have Management Studio installed, see Download SQL Server Management Studio (SSMS).

If you can’t install Management Studio, you can test the connection by using the sqlcmd.exe utility. sqlcmd.exe is installed with the Database Engine. For information about sqlcmd.exe, see sqlcmd Utility.

Sign in to the computer where SQL Server is installed by using a login that can access SQL Server. During installation, SQL Server requires at least one login to be specified as a SQL Server administrator. If you don’t know an administrator, see Connect to SQL Server When System Administrators Are Locked Out.

On the Start page, type SQL Server Management Studio, or on the Start menu of the older versions of Windows, select All Programs, select Microsoft SQL Server, and then select SQL Server Management Studio.

On the Connect drop-down menu, select Database Engine. In the Authentication box, select Windows Authentication. In the Server name box, type one of the following connection types:

When connecting to SQL Server from a client application on the same computer, the shared memory protocol is used. Shared memory is a type of local named pipe, so you sometimes encounter errors related to pipes.

If you receive an error at this point, you must resolve it before proceeding. Your login might not be authorized to connect. Your default database might be missing.

You can’t troubleshoot the problem without enough information because some error messages are passed to the client intentionally. This is a security feature to avoid providing an attacker with information about SQL Server. To view the details about the error, see the SQL Server error log.

If you receive error 18456 Login failed for user, Books Online article MSSQLSERVER_18456 contains additional information about error codes. Aaron Bertrand’s blog also has an extensive list of error codes at Troubleshooting Error 18456 (external link). You can view the error log by using SSMS (if you can connect), in the Management section of the Object Explorer. Otherwise, you can view the error log with the Windows Notepad program. The default location varies with your version and can be changed during setup. The default location for SQL Server 2019 (15.x) is C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLLogERRORLOG.

If you can connect by using shared memory, test connecting by using TCP. You can force a TCP connection by specifying tcp: before the name. Here are the examples:

If you can connect by using shared memory but not TCP, you must fix the TCP problem. The most likely issue is that TCP isn’t enabled. To enable TCP, see Step 6: Verify the enabled protocols on SQL Server.

If your goal is to connect by using an account other than an administrator account, you can begin by connecting as an administrator. Then, try to connect again with the Windows Authentication login or the SQL Server Authentication login that the client application uses.

Step 9: Test remote connection

Once you can connect by using TCP on the same computer, it’s time to try to connect from the client computer. You could use any client application, but to avoid complexity, install the SQL Server Management tools on the client. After installation, try to use SQL Server Management Studio.

Use SQL Server Management Studio on the client computer and try to connect by using the IP address and the TCP port number in the format IP address comma port number. For example, 192.168.1.101,1433 . If this connection fails, you probably have one of the following problems:

ping of the IP address doesn’t work. This indicates a general TCP configuration problem. Go back to the section Step 7: Test TCP/IP connectivity.

SQL Server isn’t listening on the TCP protocol. Go back to the section Step 6: Verify the enabled protocols on SQL Server.

SQL Server is listening on a port other than the port that you specified. Go back to the section Get the TCP port.

The SQL Server TCP port is being blocked by the firewall. Go back to the section step 5: Verify the firewall configuration.

Once you can connect by using the IP address and port number, review the following scenarios:

If you connect to a default instance that is listening on any port other than 1433, you must use either the port number in the connection string or create an alias on the client machine to connect to the default instance. The SQL Server Browser service can’t enumerate ports of the default instance.

If you connect to a named instance, try to connect to the instance in the format IP address backslash instance name. (For example, 192.168.1.101 .) If this action doesn’t work, it means that the port number isn’t being returned to the client. The problem is related to the SQL Server Browser service, which provides the port number of a named instance to the client. Here are the solutions:

• Start the SQL Server Browser service. See the instructions to start browser in SQL Server Configuration Manager.
• The SQL Server Browser service is being blocked by the firewall. Open UDP port 1434 in the firewall. Go back to the section Step 5: Verify the firewall configuration. Make sure that you’re opening a UDP port, not a TCP port.
• The UDP port 1434 information is being blocked by a router. UDP communication (user datagram protocol) isn’t designed to pass through routers and keeps the network from getting filled with low-priority traffic. You can configure your router to forward UDP traffic, or you can provide the port number every time you connect.
• If the client computer is using Windows 7, Windows Server 2008, or a more recent operating system, the client operating system might drop the UDP traffic because the response from the server is returned from a different IP address that was queried. This action is a security feature blocking «loose source mapping.» For more information, see the Multiple Server IP Addresses section of the Books Online article Troubleshooting: Timeout Expired. (This article is from SQL Server 2008 R2, but the principals still apply. You can configure the client to use the correct IP address or provide the port number every time you connect.)

Once you can connect by using the IP address (or IP address and instance name for a named instance), try to connect by using the computer name (or computer name and instance name for a named instance). Put tcp: in front of the computer name to force a TCP/IP connection. For example, for the default instance on a computer named ACCNT27, use tcp:ACCNT27 . For a named instance called PAYROLL, on that computer use tcp:ACCNT27PAYROLL . If you can connect by using the IP address but not by using the computer name, you have a name resolution problem. Go back to the section Step 7: Test TCP/IP connectivity.

Once you can connect by using the computer name forcing TCP, try to connect by using the computer name without forcing TCP. For example, for a default instance, and just use a computer name such as CCNT27. For a named instance, use the computer name and instance name like ACCNT27PAYROLL. If you can connect while forcing TCP, but not without forcing TCP, the client is probably using another protocol such as named pipes. To fix this issue, follow the steps:

1. On the client computer, use SQL Server Configuration Manager. In the left-pane, expand SQL Native Client Configuration, and then select Client Protocols.
2. On the right-pane, make sure that TCP/IP is enabled. If TCP/IP is disabled, right-click TCP/IP and select Enable.
3. Make sure that the protocol order for TCP/IP is a smaller number than the named pipes (or VIA on older versions) protocols. Generally, you should leave shared memory as order 1 and TCP/IP as order 2. Shared memory is only used when the client and SQL Server are running on the same computer. All enabled protocols are tried in order until one succeeds, but shared memory is skipped when the connection isn’t on the same computer.

Источник

Ultimate Solutions to Resolve MS SQL Server Error 2

Have you ever wonder that you are in a hurry and has to make changes in SQL database but, you end up on an error?

Certainly, It is not less than a nightmare.

The similar condition happens when users try to use SQL Server with the help of Microsoft SQL Server Management Studio and get the following 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 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).

It is a disturbing scenario where the user needs to connect with database and get the SQL Server Management error. Without resolving this technical glitch, you cannot use the database in SSMS. Thus considering the users’ situation, we come up with this article. Here, we will describe some workarounds that let you know how to solve Microsoft SQL Server Error 2 step-by-step. Along with this, we will also illustrate the reason behind this SSMS error.

So, let’s get started!!

Microsoft SQL Server Error 2 Could Not Open Connection : Potential Reasons

In the subsequent section, all the factors that are responsible for SSMS Error i.e., A network-related or instance-specific error occurred while establishing a connection to SQL Server discussed below.

1. It might be possible that SQL Server Services are disabled that
   created an issue while connecting to SQL Server Management Studio.
2. Check whether you are using right SQL Server instance name at the
   time of connection.
3. SQL Administrator does not provide permission to Allow remote
   connection.
4. It is also possible that SQL Server Management Studio using the
   incorrect port number. That is why user will get the MS
   SQL Server Error 2.
5. The SQL Server Browser service is not enabled under the SQL Server
   Configuration Manager.
6. It also happens, the port on which SQL Server is running, is blocked
   by system firewall.
7. The Named Pipes protocol is disabled in SQL Server Configuration
   Manager. Originally, the Named Pipes is a type of protocol developed
   for Local Area Networks in order to communicate between two
   processes.

After understanding the factors behind this error, let’s go through the techniques to fix the SQL Server Error 2 of SSMS.

Microsoft SQL Server Error 2 Solutions – 100% Working

It is important to analyze the correct reason behind the SSMS error. After knowing the reason, execute the workaround according to it.

Method #1: Enable the SQL Server Services

Step 1: Initially, press the Windows key along with R key in order to open the Run box.

Step 2: Input the ‘compmgmt.msc’ under the Open box as shown in the screenshot. Then, click on OK to execute it.

Step 3: From Computer Management window, click on Services and Applications >> SQL Server Configuration.

Step 4: Afterward, opt for SQL Server Services from the preview pane.

Step 5: Once you select the option, the 6 different services of SQL Server available on the screen along with its State.

Step 6: At last, you need to start the services.

Method #2: Enable TCP/IP Network Protocol for SQL Server 2014 / 2016 / 2017

Step 1: From Computer Management window, click on Services and Applications >> SQL Server Configuration Manager >> SQL Native Client 11.0 Configuration (32 bit).

Step 2: Under SQL Native Client 11.0 Configuration (32 bit) option, opt for Client Protocols option. By doing this, you will see the three protocols – Shared Memory, TCP/IP, Named Pipes. If anyone is disabled make them enabled. For this, right-click on any protocol and click on Enable.

Step 3: Try to connect with SQL Server via SQL Server Management Studio and see whether the Microsoft SQL Server Error 2 get resolved or not.

Method #3: Provide Permission of Allow Remote Connections

Step 1: Open Microsoft SQL Server Management Studio. Here, we are using SSMS 2014 on Windows 10 OS.

Step 2: Go to the Server Name under the Object Explorer. Afterward, right-click on it and opt for Properties.

Step 3: Under Server Properties window, go to the Connections section and check the box corresponding to Allow remote connections to this server. Then, hit the OK button to resolve SSMS error 2.

Bringing It All Together

If for any reason – users face the Microsoft SQL Server Error 2 Cannot Connect to Local in SSMS then, try the above-mentioned workarounds. Although, many times SQL Server error arises due to corruption in MDF file. In such a situation, try the SysTools SQL Database Recovery Software. Hence, it is a complete guide that let users know how to fix MS SQL Error 2 on the Windows platform.

Frequently Asked Question of Users