Export (0) Print
Expand All
3 out of 5 rated this helpful - Rate this topic

Configuring Remote Servers

SQL Server 2000

  New Information - SQL Server 2000 SP3.

A remote server configuration allows a client connected to one instance of Microsoft® SQL Server™ to execute a stored procedure on another instance of SQL Server without establishing another connection. The server to which the client is connected accepts the client request and sends the request to the remote server on behalf of the client. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client. When setting up your remote server configuration, you should also think about how to establish security. For more information about security for remote servers, see Establishing Security for Remote Servers.

If you want to set up a server configuration in order to execute stored procedures on another server and do not have existing remote server configurations, use linked servers instead of remote servers. Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.

Note  Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead.

Remote Server Details

Remote servers are set up in pairs. To set up a pair of remote servers, configure both servers to recognize each other as remote servers. Then, verify that configuration options are set properly for both servers so that each instance of SQL Server allows remote users to execute procedure calls. Check the configuration options in the Server Properties dialog box on both the local and the remote servers.

In most cases, you should not need to set configuration options for remote servers; the defaults set on both local and remote computers by SQL Server Setup allow for remote server connections.

For remote server access to work, the remote access configuration option, which controls logins from remote servers, must be set to 1 (the default setting) on both the local and remote computers. If the setting for either server's remote access option has been changed, you must reset the option (for one or both servers) back to 1 to allow remote access. This can be accomplished through either SQL Server Enterprise Manager or the Transact-SQL sp_configure statement.

From the local server, you can disable a remote server configuration to prevent user access to that server.

To set up a remote server

Transact-SQL

SQL-DMO

To check and set remote server configuration options

Enterprise Manager

SQL-DMO

To disable a remote server setup

Enterprise Manager

Transact-SQL

SQL-DMO

See Also

Configuring Linked Servers

RECONFIGURE

remote access Option

sp_configure

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.