Fix SQL Server Manager cannot connect to remote database error:1326 error

Fix SQL Server Manager cannot connect to remote database error:1326 error

When we use SQL Server, we encounter an issue where SQL Server Management Studio cannot connect to a remote DB instance, and the error description information is summarized as follows:

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

(provider:named Pipes provider, error:40–could not open a connection to SQL Server) (Microsoft SQL Server, Error:5)

An error had occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure is caused by the fact that under the default settings SQL Server does not allow remote connections. (provider:named Pipes provider, error:40–could not open a connection to SQL Server) (Microsoft SQL Server, error:1326)

The above-mentioned English translation probably says: You cannot establish a connection between databases because of an error in the named pipe provider. In fact, this is a more typical database server Setup problem, can be encountered in the LAN or WAN, we just need to configure the database server to solve this problem, to see the specific steps.

Step one: Ensure that the server-side database service is started

Start All Programs->microsoft SQL Server 2008->configutation Tools, open SQL Server Configuration Manager, click SQL Server Services to see if the database service is started, and if the service is not turned on, start it manually. Of course, you can also check to see if the appropriate database service is started by tapping services, manager tools, Control Panel, Windows. Or if the server is on the same network as your machine, you can also view all available SQL Server servers in the network by command "Sqlcmd-l" (note L to capitalize).

Step two: Enable TCP/IP in SQL Server configuration

TCP/IP support is required for multiple SQL Server servers to communicate with each other over the network, so that the SQL Server server can be connected remotely must ensure that TCP/IP is enabled. Follow the steps that are described earlier, open SQL Server Configuration Manager, open the SQL Server Network configuration, select the database you want to set up, and then click TCP/IP, and then right-enable. If necessary, you can also enable named Pipes. Remember, all modifications must be restarted before the SQL Server service can take effect!

Step three: Open the port number of SQL Server in Windows Firewall

Many times we do not successfully establish a remote connection after the database server itself has been set up many times, it is necessary to consider whether it is a firewall in mischief. By default, many port numbers and services are blocked by firewalls and cannot be accessed or executed remotely, and SQL Server's default port number is no exception. We should reset Windows Firewall to add an exception to SQL Server. Unless it is artificially modified, the port number of SQL Server is 1433 by default, and the port number is added to the Windows Firewall. If a named instance exists in SQL Server, you should also add SQL Server Browser to Windows Firewall. (a named instance of SQL Server will be described later)

Open the Windows Control Panel, select Windows Firewall->change settings->exceptions->add Port

Click Add Port ... In the popup dialog box, fill in the following:

Name:sql

Port number:1433

Protocol:select TCP

Fourth step: Enable remote connections in SQL Server Manager

This step is usually ignored by the database administrator, and it is also important to enable remote connections if the database remote connection is not enabled and the DB instance allows only local connections and cannot be connected remotely. Remote connections are forbidden in the default settings. For example, open SQL Server Management Studio, right-click the DB instance and select the Properties menu.

In the window that opens, select connections on the left, and then tick "Allow remote connections to this server".

Fifth step: Enable the SQL Server Browser service

If SQL Server is not using the default instance name for the installation, but a custom instance name, and no specific TCP/IP port number is configured, then as we described earlier, SQL Server still cannot support remote connections. However, if you enable the SQL Server Browser service, you can make a remote SQL Server connection through the dynamic TCP/IP port number. Enabling the SQL Server Browser service is simple, similar to enabling SQL Server, right-clicking SQL Server Browser in SQL Server Configuration Manager and selecting Enable. Enabling this service will affect all installed instances of SQL Server on the server.

Sixth step: Create an exception for the Sqlbrowser.exe application in the firewall

As we mentioned earlier, custom named SQL Server instances to support remote connections require the SQLBrowser service to be enabled, and Windows Firewall may prevent the service from executing. Therefore, we must add an exception to the SQLBrowser service in Windows Firewall.

First find the path to the server where the Sqlbrowser.exe program is installed, such as C:\Program Files\Microsoft SQL Server\90\shared\sqlbrowser.exe. If you are not sure where SQL Server is installed, you can search for the file name in Windows. Similar to the method we described earlier in adding SQL TCP/IP port numbers to the firewall, add a firewall exception to the Sqlbrowser.exe application.

Finally: Re-create the database alias

It is common to create a SQL Server alias and use it in your application. Using a database alias ensures that the database connection string in the application does not have to be modified once the location of the database has changed, such as when a new server has been replaced, the IP address has changed, and so on. Otherwise, if you change the location of the database, you also need to notify all applications that use the database to modify the connection string in the source code or configuration file, which is probably not possible. Therefore, it is a very wise choice to use a database alias to configure the connection string. In addition, you can use the same alias to point to different DB instances, and when you modify the alias parameters, you can switch between the databases immediately. Creating a database alias is as simple as selecting aliases in SQL Server Configuration Manager to create it.

Original address: http://www.ityoudao.com/Web/Dnzs_621_1180_2.html