To set up a remote serverA remote server configuration allows a client connected to one server running Microsoft® SQL Server™ to execute a stored procedure on another server running 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 the client’s behalf. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client.
Remote server configurations have been superseded by linked server configurations in SQL Server version 7.0. Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers. Support for remote servers is provided for backward compatibility only. If you are interested in setting up a server configuration to execute stored procedures on another server, and do not have existing remote server configurations, use linked servers instead of remote servers.
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 server running 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.
In SQL Server 7.0, only Open Data Services gateways compiled with the version of Open Data Services that ships with SQL Server 7.0 can be used as remote servers. Attempting a remote stored procedure call from SQL Server 7.0 against a gateway compiled with the versions of Open Data Services that shipped with earlier versions of SQL Server results in one of the following errors:
OLE DB provider 'SQLOLEDB reported an error. A provider-specific error occurred.
Could not create a new session on OLE DB provider 'SQLOLEDB' reported an error.
Unable to initialize data source object of OLE DB provider 'SQLOLEDB'.
To set up a remote server
To check and set remote server configuration options
To disable a remote server setup
| Configuring Linked Servers | remote access Option |
| RECONFIGURE | sp_configure |