SQL 2005 does not allow remote connection, which may cause this failure

Time:2020-1-5

(provider: named pipeline provider, error: 40 – unable to open connection to SQL Server)

The database connection statement of the website is: server = 127.0.0.1; uid = Sa; PWD = XXX; database = XXX
After testing, server = 127.0.0.1 is changed to “server =.” or “server = machine name”, which can be connected normally;
Therefore, it can’t be connected only when using IP. After analysis, the solution is as follows:
1. Open SQL Server peripheral application configurator in SQL Server 2005 configuration tool, and click Service and connected peripheral application configurator;
2. In the interface, click “remote connection” in “database engine”, and change the connection mode to “simultaneous use of TCP / IP and name pipes” in “local connection and remote connection”;
3. After modification, restart SQL Server 2005 service through “SQL Server Configuration Manager” in “configuration tool”;
Note: you must restart SQL Server 2005 for the service modification to take effect;

An error occurred while establishing a connection to the server. When connecting to SQL Server 2005, SQL server does not allow remote connection by default
An error occurred while establishing a connection to the server. When connecting to SQL Server 2005, by default, SQL server does not allow remote connections, which may cause this failure. (provider: named pipeline provider, error: 40 – unable to open connection to SQL Server)

Why? I have set the remote connection to TC / IP and nanme in the SQL peripheral configuration settings to allow them at the same time
I’ve tried everything I said on the Internet. Now I have VS2005 installed, but I have SQL server installed
Bonus points if the problem is solved!

Answer:
SQL Server 2005 disallow remote connection workaround

In course design, many students are confronted with this problem. Write the solution here for reference.

For the database system just installed, if it is installed by default, it is likely to report an error in remote connection, usually an error: “when connecting to SQL Server 2005, the SQL server does not allow remote connection under the default settings, which may cause this failure. (provider: named pipeline provider, error: 40 – unable to open the connection to SQL Server) “search MSDN, there is a piece of machine translated article on it, which is difficult for people to understand. Now it is summarized as follows: understand that SQL server is a network database, and it can be solved easily. It is divided into the following centralized situations.

1. The database engine did not start.

There are two ways to start:

(1) Start – > program – > Microsoft SQL Server 2005 – > SQL Server 2005 peripheral application configurator, click “peripheral application configurator of service connection” in the open interface, find database engine in the open interface, click “service”, check whether it has been started on the right side, if not, click “start”, and make sure “start type” is automatic, not manual, otherwise, click Manual start-up is required for secondary start-up;

(2) Open: start – > program – > Microsoft SQL Server 2005 – > configuration tool – > sql server configuration manager, select SQL Server (MSSqlServer) in SQL Server 2005 service, and click the “start service” button in the toolbar to change the service status to start;

When using the above two methods, sometimes there may be errors when starting, and you cannot start. At this time, you need to check whether the via in “SQL Server 2005 network configuration – > MSSQLSERVER Protocol” has been enabled. If it has been enabled, disable it. Then perform the above operation

2. Whether remote connection is allowed.

This part can be simply divided into four aspects: enabling remote connection on SQL server, enabling SQL Server browsing service, creating exceptions for SQL Server 2005 in Windows Firewall and creating exceptions for sqlbrowser in windows firewall. Here are a few specific ways of operation, excerpted from MSDN. I think the bold part of the article should be paid special attention to together.

Enable remote connection on SQL server instance
1. Point to “start – > program – > Microsoft SQL Server 2005 – > configuration tool – > sql server peripheral application configurator”
2. On the SQL Server 2005 peripheral configurator page, click Service and connected peripheral Configurator
3. Then click to expand database engine, select remote connection, and select local connection and remote connection on the right,
Then select the protocol to use. (this place should enable TCP / IP and named pipeline service! )Click apply and you will see the following message:
Changes to connection settings will not take effect until the database engine service is restarted , click OK to return
4. Expand database engine, select services, click stop on the right, and wait until MSSqlServer service stops,
Then click start to restart the MSSqlServer service.

Enable SQL Server Browser Service
1. Point to “start – > program – > Microsoft SQL Server 2005 – > configuration tool – > sql server peripheral application configurator”
2. On the SQL Server 2005 peripheral configurator page, click Service and connected peripheral Configurator
3. Click to expand SQL server browser, select services, and select auto for startup type on the right,
Click start again, click OK to return

Create an exception for SQL Server 2005 in Windows Firewall
1. In the windows firewall settings interface, select the Exceptions tab, and then click add program
2. In the add program window, click Browse
3. Then find “C: / programfiles / Microsoft files / Microsoft SQL Server / MSSQL. 1 / MSSQL / binn”/