Solve the problem that the SQL Server Manager cannot connect to a remote database

When using SQL Server, many beginners will encounter the problem that SQL Server Management studio cannot connect to a remote database instance. The error is described as follows:

An error has occurred while establishing a connection to the server.

(Provider: Named Pipes provider, error: 40-cocould not open a connection to SQL Server) (Microsoft SQL Server, error: 5)

An error has occurred while establishing a connection to the server. when connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provider: Named Pipes provider, error: 40-cocould not open a connection to SQL Server) (Microsoft SQL Server, error: 1326)

This means that a connection cannot be established between databases because the named pipeline provider has an error. In fact, this is a typical Database Server SETUP problem, which may be encountered in the LAN or WAN. We only need to configure the database server to solve this problem, let's take a look at the specific steps.

 

Make sure that the database service on the server has been started

Start> AllProgram-> Microsoft SQL Server 2008-> configutation tools, open SQL Server Configuration Manager, and click SQL Server services to check whether the database service has been started. if the service is not enabled, start it manually. Of course, you can also choose Start> Control Panel> Administrative Tools> services in Windows to check whether the corresponding database service is enabled. Or if the server and your machine are on the same network, you can run the "sqlcmd-L" command (note that l must be capitalized) to view all the available SQL Server servers in the network.

Enable TCP/IP in SQL Server Configuration

TCP/IP support is required for communication between multiple SQL Server servers over the network. To enable the SQL Server server to be remotely connected, the TCP/IP must be enabled. Follow the steps described above to open SQL Server Configuration Manager, enable SQL SERVER network configuration, select the database you want to set, click TCP/IP, and right-click Enable. If necessary, you can also enable named pipes. Remember, all modifications take effect only after the SQL Server service is restarted!

 

Port Number for enabling SQL Server in Windows Firewall

In many cases, we still cannot establish a remote connection after many settings on the database server itself. In this case, we need to consider whether the firewall is at fault. By default, many port numbers and services are disabled by the firewall and cannot be accessed or executed remotely. The default port number of SQL Server is no exception. We should reset Windows Firewall to add exceptions to SQL Server. Unless manually modified, the default port number of SQL Server is 1433. Add the port number to Windows Firewall. If SQL server has a named instance, you should also add SQL Server Browser to Windows Firewall. (The SQL Server naming example will be introduced later)

Open the windows control panel and choose Windows Firewall> change Settings> exceptions> Add port.

 

Click Add port... in the pop-up dialog box, fill in:

Name: SQL

Port Number: 1433

Protocol: select TCP

 

 

Enable remote connection in SQL Server Manager

This step is usually ignored by the database administrator. If remote connection is not enabled, the database instance can only be connected locally but not remotely. enabling remote connection is equally important. By default, remote connection is disabled. For example, open SQL Server Management studio, right-click the database instance, and select the properties menu.

In the displayed window, select connections on the left and select "Allow Remote connections to this server ".

 

Enable the SQL Server Browser Service

If SQL server is not installed with the default Instance name but a custom Instance name, and the specified TCP/IP Port Number is not configured, as described above, SQL Server still does not support remote connection. However, If you enable the SQL Server Browser service, you can use the dynamic TCP/IP Port Number to remotely connect to the SQL server. Enabling SQL Server Browser is very simple. Similar to enabling SQL Server, right-click SQL Server Browser in SQL Server Configuration Manager and choose enable. Enabling this service will affect all installed SQL Server instances on the server.

 

Create an exception for the sqlbrowser.exe application in the fire wall

As we have mentioned earlier, to support remote connections to custom-named SQL Server instances, you must enable the sqlbrowser service. Windows Firewall may prevent the service from being executed. Therefore, you must add exceptions to the sqlbrowser service in Windows Firewall.

First, find the path to install the sqlbrowser.exe program on the server, such as c: \ Program Files \ Microsoft SQL Server \ 90 \ shared \ sqlbrowser.exe. If you are not sure where SQL Server is installed, search for the file name in windows. As described earlier, add an SQL TCP/IP Port number to the firewall and add firewall exceptions to the sqlbrowser.exe application.

 

Recreate a database alias

It is common to create an SQL Server alias and use it in applications. The database alias can be used to ensure that once the database location changes, such as a new server or IP address, the database connection string in the application does not need to be modified. Otherwise, you have changed the database location, and you have to notify all applications that use the database to modify it.Source codeOr the connection string in the configuration file. This is probably not possible. Therefore, it is wise to use the database alias to configure the connection string. In addition, you can use the same alias to direct to different database instances. When modifying the alias parameter, you can immediately switch between databases. Creating a database alias is very simple. Select aliases in SQL Server Configuration Manager to create a database alias.