remote connection to SQL Server 2008, server-side and client configuration

remote connection to SQL Server 2008, server-side and client configuration

First step (SQL2005, SQL2008):

Start-to-Program-->microsoft SQL Server 2008 (or 2005)-Configuration Tools-->sql Server Configuration Manager-->sql Server network configuration- MSSQLSERVER (this name is based on the specific instance name)-->tcp/ip--> right---enable

Step Two:

SQL2005:

Start-to-Program-->microsoft SQL Server 2005--> Configuration tool-->sql Server 2005 surface Area Configuration and surface Area configuration for services and connections-->database Engine--Remote connection, select Local connection and remote connection and choose both TCP/IP and named pipes.

(Attached: How to configure SQL Server 2005 to allow remote connections: HTTP://SUPPORT.MICROSOFT.COM/KB/914277/ZH-CN)

SQL2008:

Open SQL Server Management studio--> Select the first item in the left [Object Explorer] (primary database Engine)--right----and in the Aspect drop-down list, select the [Surface area configurator]--> Set Remotedacenable to True. (This step is critical)

Express:

If XP has an open firewall, in the exception of the following two programs to add:

C:/Program Files/microsoft SQL Server/mssql.1/mssql/binn/sqlservr.exe,

C:/Program Files/microsoft SQL Server/90/shared/sqlbrowser.exe

Step Three:

Start-to-Program-->microsoft SQL Server 2008 (or 2005)--Configuration Tools-->sql Server Configuration Manager-->sql Server service--right-click SQL Server ( MSSQLSERVER) (note: The specific instance name in parentheses)--reboot

Fourth Step:

Open the SA user on the server side. Starting with Microsoft SQL Server Management Studio, main database engine, security--login name SA, set the password.

Fifth Step:

The client logs on to the server-side database. As shown in the following:

Sixth step:

C # Engineering changes the database connection string. Open C # Engineering, Solution Explorer, App. Config, change. Examples are as follows:

[C-sharp]

connectionstring= "Data source= server ip/instance name; initial catalog= database name; User id= username; password= password "

Seventh Step:

Good luck! Enjoy ...

When a user uses the SQL Server 2008 Remote link, the following dialog box may appear:

A network link error or a specific instance error occurred while linking the SQL Server. The SQL Server does not exist or the link is unsuccessful. Verify that the user name is correct or that the SQL Server has the Remote link feature configured. (Error Discovery Party: Named pipe provider.) Error code:---cannot be linked to SQL Server)

How to deal with this problem?

If the user finds this error alert, it needs to check the configuration of three aspects and make the relevant changes. (The configuration measures shown below are in the SQL 2008 Server template)

(a) The first thing a user needs to do is to check whether a remote link is allowed in the SQL database server. This check can be done in SQL 2008 server by opening the SQL Server 2008 management project (SQL Server Management Studio). The specific actions are:

(1) Right-click the SQL Server 2008 option and select SQL Server Management Studio:

(2) Open Server properties (right-click Connection Name-Properties)

After you select the Allow users to remotely link to this server option, check to see if the issue is excluded. If the remote link is successful, the process is complete. If the user finds that the "Reminder error Dialog" Still pops up, then you need to do the following:

(ii) Configure the appropriate protocol for Microsoft SQL Server (MSSQLServer).

At this point the user is to check the SQL network link configuration. Open the SQL Server Configuration Management option > Open the SQL Server network configuration options for this node > select the Microsoft SQL Server network Protocol option (or the option to configure the network protocol for the SQL Server in the user's SQL Server, different versions of the SQL name may differ):

Determining the TCP/IP protocol in the Microsoft SQL Server Network Options protocol is valid for the server. Check again to see if you can perform the remote link. If the Error alert dialog box still pops up, we need to check the SQL Server firewall option further.

(iii) Check SQL Server firewall settings (Quick steps-Direct firewall off (unsafe))

If the client computer still cannot be linked to the SQL Server remotely after the last two steps, the user needs to reconfigure the SQL Server firewall. When you do this, first locate the port on the SQL Server that supports the TCP/IP protocol. Users can click on "TCP/IP protocol" to select "Properties" when the SQL Server firewall is already running:

As we can see, the TCP/IP protocol supported on this SQL Server is port 1433. The next step is to allow 1433 ports to support the TCP/IP protocol in the configuration of the firewall. If the server is running a Windows 7 operating system with a configuration step (similar to the practice of other Microsoft operating systems), open the Control Panel and select the Windows Firewall option:

After you select Advanced Settings, in the right menu bar, find Windows Firewall with Advanced Security options and open it. When you open it, you will find the "inbound rules" option in the left menu bar. Inboud Open the option and select the new Rule option in the right-hand menu bar:

After opening the new Rule option, configure the internal binding protocol for port 1433 with the new built-in binding Rule Wizard to apply to the TCP/IP protocol. (provided that you need to complete all the steps previously described in this step), the 1433 port configuration for port 1433 is available according to the following diagram:

remote connection to SQL Server 2008, server-side and client configuration