Configuration description of SQL2005 and sql2008 allowing remote connection (with configuration diagram)

Time:2021-3-9

SQL Server 2005 allows remote connection configuration

You may receive error messages when trying to connect to an instance of Microsoft SQL Server 2005 from a remote computer. This problem can occur when using any program to connect to SQL server. For example, when you connect to SQL server using the sqlcmd utility, you receive the following error message:

Sqlcmd: error: Microsoft SQL Native Client: an error occurred while establishing a connection to the server. When connecting to SQL Server 2005, the default setting is that SQL server does not allow remote connections. This fact may lead to failure.

This problem can occur if SQL Server 2005 is not configured to accept remote connections. By default, SQL Server 2005 Express Edition and SQL Server 2005 Developer Edition do not allow remote connections. To configure SQL Server 2005 to allow remote connections, complete all of the following steps:

  • Enable remote connection on the instance of SQL server to which you want to connect from the remote computer.
  • Open the SQL Server Browser service.
  • Configure the firewall to allow network communication related to SQL server and SQL Server Browser services.

This article describes how to complete each of these steps.

To enable remote connections on an instance of SQL Server 2005 and open the SQL Server Browser service, use the SQL Server 2005 peripheral configurator tool. The peripheral application configurator tool is installed when SQL Server 2005 is installed.

Enable remote connection for SQL Server 2005 Express Edition or SQL Server 2005 Developer Edition

Remote connection must be enabled for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:

1. Click start, point to programs, Microsoft SQL Server 2005, configuration tools, and then click SQL Server peripheral configurator.
2. On the SQL Server 2005 peripheral configurator page, click Service and connected peripheral configurator.
3. On the “peripheral application configurator for services and connections” page, expand “database engine”, click “remote connection” and “local connection and remote connection”, click the appropriate protocol for your environment, and then click “application”.

        be careful: click OK when you receive the following message:
Changes to connection settings will not take effect until the database engine service is restarted.
4. On the “service and connected peripheral application configurator” page, expand “database engine”, click “service” and “stop”, wait for MSSqlServer service to stop, and then click “start” to restart MSSqlServer service.

Enable SQL Server Browser Service

If you are running SQL Server 2005 by using an instance name and do not use a specific TCP / IP port number in the connection string, you must enable the SQL Server Browser service to allow remote connections. For example, SQL Server 2005 express is installed with the default instance name of < Computer Name > \ sqlexpress. No matter how many instances of SQL Server 2005 you are running, you only need to enable the SQL Server Browser service once. To enable the SQL Server Browser service, perform the following steps.

Important note:These steps may increase your security risk. These steps may also make your computer or network more vulnerable to malicious users or malware (such as viruses). The reason why we recommend this process is to make the program run according to the design intent, or to achieve specific program functions. We recommend that you fully consider the risks of implementing this process in your specific environment before making these changes. If you choose to implement this process, take any appropriate additional steps to protect your system. We recommend using this process only when it is really needed.

1. Click start, point to programs, Microsoft SQL Server 2005, configuration tools, and then click SQL Server peripheral configurator.
2. On the SQL Server 2005 peripheral configurator page, click Service and connected peripheral configurator.
3. On the “peripheral application configurator for services and connections” page, click “SQL server browser”, click “automatic” option in “startup type”, and then click “application”.

    be careful: after you click the automatic option, the SQL Server Browser service will start automatically every time you start Microsoft Windows.
4. Click start, and then click OK.
Note: when running SQL Server Browser service on the computer, the computer displays the instance name and connection information of each SQL server instance running on it. If you do not enable the SQL Server Browser service and connect directly to the SQL server instance through the assigned TCP port, you can reduce this risk. This article does not discuss how to directly access the SQL server instance through the TCP port. For more information about the SQL Server Browser service and connecting to an instance of SQL server, see the following topics in SQL Server Books Online:

  • SQL Server Browser Service
  • Connect to SQL Server database engine
  • Client network configuration

Creating exceptions in Windows Firewall

These steps apply to Windows XP Service Pack 2 (SP2) and the version of windows firewall included in Windows Server 2003. If you are using a different firewall system, please refer to the appropriate firewall documentation for more information.

If you run a firewall on a computer running SQL Server 2005, access to external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and SQL Server Browser services can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept a remote connection and an exception for the SQL Server Browser service.

When installing the program files of SQL Server 2005, SQL Server 2005 will use an instance ID as part of the path. To create an exception for each SQL server instance, you must determine the correct instance ID. To get the instance ID, perform the following steps:

1. Click start, point to programs, Microsoft SQL Server 2005, configuration tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click properties.
3. On the SQL server browser properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To open windows firewall, click start, then click Run, and type firewall.cpl , and then click OK.

Creating exceptions for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in windows firewall, perform the following steps:

1. In the windows firewall, click the Exceptions tab, and then click add program.
2. In the add program window, click Browse.
3. Click C: program files: Microsoft SQL Server: mssql. 1: mssql: binn\ sqlservr.exe Executable, click open, and then click OK.

Note: the above path may vary depending on where SQL Server 2005 is installed. The placeholder MSSQL. 1 represents the instance ID you obtained in step 3 of the previous procedure.
4. Repeat steps 1 to 3 for each SQL Server 2005 instance for which you need to create an exception.

Creating exceptions for SQL Server Browser service in Windows Firewall

To create an exception for the SQL Server Browser service in windows firewall, perform the following steps:

1. In the windows firewall, click the Exceptions tab, and then click add program.
2. In the add program window, click Browse.
3. Click C: program files: Microsoft SQL Server: 90: shared\ sqlbrowser.exe Executable, click open, and then click OK.

      be careful:The above path may vary depending on where SQL Server 2005 is installed.

SQL Server 2008 allows remote connection configuration

If you can’t remotely connect to your SQL Server 2008 server through IP address, you can set it by referring to the following contents.
Before making the following settings, you should make sure that your network has been installed and set up, and the server has been connected to the network normally.

1. Click Windows start, select all programs, Microsoft SQL Server 2008, and then click SQL Server Management Studio. In object explorer, right-click your server and execute properties from the shortcut menu

2. In the server properties window, select security on the left and SQL server and windows authentication mode on the right

3. Go back to the object explorer, right-click the login “Sa” under the node “security → login”, and execute “properties” from the shortcut menu

4. First, in the login properties window, select “general” on the left and enter the password on the right

5. Then select “status” on the left and “grant” and “enable” on the right of the login properties window

6. Click start of windows, select all programs, Microsoft SQL Server 2008, configuration tools, and then click SQL Server Configuration Manager. On the left side of the window, select the protocol of MSSqlServer, and on the right side, double-click TCP / IP

7: In the TCP / IP properties window, select the “IP address” tab, and set the IP address and port number. The port number is generally 1433:1

8, then select the node “SQL Server service” on the left side of the window, right-click “SQL Server (MSSqlServer) on the right side, and execute” restart “from the shortcut menu:

9. If the server has enabled the firewall of windows, you must add an exception to allow others

The computer is connected to the SQL Server service of this server.

Take Windows XP and Windows Server 2003 as examples. If you are using Windows 7 / Vista or Windows Server 2008, please refer to: setting the firewall of windows vsita / 7 / 2008

Add port exception

In the windows firewall, select the Exceptions tab, click add port, enter the port number 1433, and then click OK

Add program exception

In the windows firewall, select the “exceptions” tab, click “add program”, in the “add program” window, click “Browse”, and select “C: / / program files / Microsoft SQL Server / mssql10. MSSqlServer / MSSQL / binn”\ sqlservr.exe ”Executable, and then click OK:

Tip: the above executable path may vary according to the installation location of SQL Server 2008.

After the above settings, other computers can connect to this server through the IP address. For the connection method, please refer to: connecting to SQL Server 2008

In particular, when connecting to a remote SQL server server through IP address, you should select the option “allow password to be saved”:

The above is the database configuration allows all the content of remote connection, I hope to help you realize SQL2005, sql2008 allow remote connection