SQL Server does not allow remote connection

For the database system that has just been installed, if it is installed by default, it is likely that an error is reported during remote connection. The common error is: "When you connect to SQL Server 2005, this failure may occur if SQL server does not allow remote connection under the default settings.

For the database system that has just been installed, if it is installed by default, it is likely that an error is reported during remote connection. The common error is: "When you connect to SQL Server 2005, this failure may occur if SQL server does not allow remote connection by default. (Provider: named pipeline provides Program , Error: 40-unable to open the connection to SQL Server) ", the problem is summarized as follows:

I understand that SQL Server is a network database and can be easily solved. It is simply divided into the following situations.

◆ 1. The database engine is not started. There are two startup methods:

(1) Start-> Program-> Microsoft SQL Server 2005-> SQL Server 2005 peripheral application configurator. On the displayed page, click "peripheral application configurator for service connection ", on the displayed page, find the database engine and click "service". Check whether the service is started on the right side. If not, click "start" and make sure "Start type" is automatic, do not manually start the instance. Otherwise, the instance must be manually started the next time it is started;

(2) You can choose Start> program> Microsoft SQL Server 2005> Configuration tool> SQL Server Configuration Manager, and select SQL Server (MSSQLServer) in SQL Server 2005 ), click "Start Service" in the toolbar to change the service status to start;

When using the above two methods, sometimes an error may occur during startup, and cannot be started, in this case, check whether the Via in SQL Server 2005 network configuration> MSSQLServer protocol is enabled in "SQL Server 2005 Configuration Manager". If yes, disable it. then, execute the preceding operation.

◆ 2. whether remote connection is allowed. This part can be simply divided into four aspects, enable remote connection on SQL Server, enable SQL Server browsing service, create exceptions for SQL Server 2005 in Windows Firewall, and create exceptions for "sqlbrowser" in Windows Firewall. The following are some specific operations:

Enable remote connection on an sqlserver instance

◆ 1. Point to "start-> Program-> Microsoft SQL Server 2005-> Configuration tool-> SQL Server peripheral application configurator"

◆ 2. On the "SQL Server 2005 peripheral application configurator" page, click "service and connected peripheral application configurator"

◆ 3. Click expand "Database Engine", select "remote connection", and select "local connection and remote connection" on the right ",

Select the Protocol to use (TCP/IP and named pipe service should be enabled here !) Click "application" and you will see the following message:

"The changes made to the connection settings will not take effect until the Database Engine service is restarted .", Click OK to return

◆ 4. Expand "Database Engine", select "service", click "stop" on the right side, and wait until the MSSQLServer service is stopped,

Click Start to restart the MSSQLServer service.

 

Enable sqlserver 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 application configurator" page, click "service and connected peripheral application configurator"

◆ 3. Click to expand "SQL Server Browser", select "service", and select "automatic" in "Startup Type" on the right ",

Click Start and then click OK to return

 

Create an exception for "SQL Server 2005" in Windows Firewall

◆ 1. on the Windows Firewall settings page, select the "exceptions" tab and 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 \ sqlservr.exe ",

Click OK to return

Note: the path may vary depending on SQL Server 2005 installation. Mssql.1 is a placeholder that corresponds to the database instance id.

◆ 4. Repeat steps 1 to 3 for each SQL Server 2005 instance that requires remote access.

Create an exception for "sqlbrowser" in Windows Firewall

◆ 1. on the Windows Firewall settings page, select the "exceptions" tab and click "add program"

◆ 2. In the "add program window", click "Browse"

◆ 3. Then find "C: \ ProgramFiles \ microsoft files \ Microsoft SQL Server \ 90 \ shared \ sqlbrowser.exe ",

 

Click OK to return

Note: the path may vary depending on SQL Server 2005 installation. In use. NET development, you will encounter the problem of using the connection string to connect to the SQL Server 2005 database using the machine name and localhost can be connected, but the use of IP addresses can not be connected, the solution is to enable local and remote connections on the SQL server instance, and use the TCP/IP and named pipeline services when selecting the protocol.