Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to allow remote connection in SQL Server. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. New login

Expand the security entry under the server name, expand the login entry, right-click the login entry, select New login, type the login name, and check SQL Server authentication

two。 The user who maps the new login to the target

Select "user Mapping" on the left side of the interface, check the target database on the top right, and make sure that the database roles of the user are db_owner and public.

3. Configure the server to allow SQL Server authentication

Right-click the SQL server name, select Properties, select Security, and check "SQL Server Authentication and Windows Authentication Mode" on the right.

Restart the server: right-click the SQL server name and select restart.

Part 2: configure the server

If SQL Server 2005 is not configured to accept remote connections, an error may occur while establishing a connection to the server. 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 SQL Server instance to which you want to connect from the remote computer.

Open the SQL Server Browser service.

Configure the firewall to allow network traffic related to SQL Server and SQL Server Browser services.

This article describes how to complete each of these steps.

To enable remote connections and turn on SQL Server Browser services on an SQL Server 2005 instance, use the SQL Server 2005 Peripheral Application Configurator tool. The Surface area Configurator tool is installed when you install SQL Server 2005.

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

Remote connections 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, point to Microsoft SQL Server 2005, point to configuration tools, and then click SQL Server Peripheral Application Configurator.

two。 On the SQL Server 2005 Peripheral Application Configurator page, click Peripheral Application Configurator for Services and connections.

3. On the Peripheral Application Configurator for Services and connections page, expand Database engine, click remote connection, click Local connection and remote connection, click the appropriate protocol for your environment, and then click apply. Note: click OK when you receive the following message:

Changes to connection settings do not take effect until the Database engine service is restarted.

4. On the peripheral application configuration for services and connections page, expand Database engine, click Services, click stop, wait for the MSSQLSERVER service to stop, and then click start to restart the MSSQLSERVER service.

Enable the 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\ SQLEXPRESS. No matter how many SQL Server 2005 instances 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: these steps may increase your security risk. These steps may also make your computer or network more vulnerable to malicious users or malicious software, such as viruses. The reason why we recommend this process in this article is to enable the program to run according to the design intent, or to achieve specific program functions. We recommend that you fully consider the risks associated with 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 that you use this process only if it is really needed.

1. Click start, point to programs, point to Microsoft SQL Server 2005, point to configuration tools, and then click SQL Server Peripheral Application Configurator.

two。 On the SQL Server 2005 Peripheral Application Configurator page, click Peripheral Application Configurator for Services and connections.

3. On the external Application Configurator for Services and connections page, click SQL Server Browser, click the automatic option in Startup Type, and then click apply. Note: after you click the automatic option, the SQL Server Browser service will start automatically each time you start Microsoft Windows.

4. Click start, and then click OK.

Note: when the SQL Server Browser service is running on a computer, the computer displays the instance name and connection information for 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 go directly to the SQL Server instance through the TCP port. For more information about SQL Server Browser services and connecting to SQL Server instances, see the following topics in SQL Server Books online:

SQL Server Browser service

Connect to the SQL Server database engine

Client network configuration

Create an exception in the Windows firewall

These steps apply to the Windows firewall version included in Windows XP Service Pack 2 (SP2) and 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, external connections to SQL Server 2005 will be blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. One exception must be created for each instance of SQL Server 2005 that will accept remote connections and one exception must be created for the SQL Server Browser service. When installing the program files for SQL Server 2005, SQL Server 2005 uses 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, point to Microsoft SQL Server 2005, point to configuration tools, and then click SQL Server configuration Manager.

two。 In the SQL Server configuration Manager, click SQL Server Browser Services 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, navigate to the instance ID in the list of properties, and then click OK.

To open Windows Firewall, click start, click run, type firewall.cpl, and then click OK.

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, perform the following steps:

1. In Windows Firewall, click the exceptions tab, and then click add programs.

two。 In the add programs window, click Browse.

3. Click the 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 that you obtained in step 3 of the previous procedure.

4. Repeat steps 1 through 3 for each instance of SQL Server 2005 for which you need to create an exception.

Create exceptions for SQL Server Browser services in Windows Firewall

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

1. In Windows Firewall, click the exceptions tab, and then click add programs.

two。 In the add programs window, click Browse.

3. Click the C:\ Program Files\ Microsoft SQL Server\ 90\ Shared\ sqlbrowser.exe executable, click Open, and then click OK. Note: the above path may vary depending on where SQL Server 2005 is installed.

The third part configures TCP/IP

(1) start menu "all programs" Microsoft SQL Server 2005 "configuration tool" SQL Server Configuration Manager

(2) SQL Server configuration Manager (Local) "SQL Server 2005 Network configuration" protocol

(3) on the right side of the interface, right-click the "TCP/IP" attribute

(4) Select "IP address" and modify the properties under "IP1" as follows:

a. Clear the IP address attribute b. Set the TCP port to 1433 (or other) c. Set the enabled property to Yes

Finally, choose to determine.

(5) Open the peripheral application configuration and restart the server

Appendix solution to remote connection failure

Phenomenon 1: if SQL 2005 (SQL Server 2005 Express) is installed in the system first, and then other versions are installed, the remote connection will fail.

Resolve:

(1) Uninstall all SQL 2005 instances

Control panel "add or remove programs" Microsoft SQL Server 2005, click "remove", select 1 SQL instance component (do not remove common components) "next" (see figure below)

The above steps can only delete one instance at a time. Repeat these steps until all SQL instances are deleted.

(2) install a new instance of SQL Server 2005

Start the SQL Server 2005 installer (a version other than the Lite version). When asked about the components to install, check "SQL Server Database Services" and click "next" (see figure below), a new instance will be installed.

(3) configure new instance

The new instance installed in step (2) is configured according to the operation instructions in the first and second parts of this article so that it can be connected remotely.

After reading the above, have you mastered how to allow remote connections in SQL Server? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.