Establishing Security for Remote Servers

SQL Server 2000

New Information - SQL Server 2000 SP3.

Setting up security for executing remote procedure calls (RPC) against a remote server involves setting up login mappings in the remote server and possibly in the local server running an instance of Microsoft® SQL Server™.

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.

Setting Up the Remote Server

Remote login mappings must be set up on the remote server. Using these mappings, the remote server maps the incoming login for an RPC connection from a given server to a local login. Remote login mappings can be set up using the sp_addremotelogin stored procedure on the remote server.

Setting Up the Local Server

Setting up a local login mapping on a local server that defines what login and password are used by an instance of SQL Server when it makes an RPC connection to a remote server.

For logins created by Windows Authentication, you must create a mapping to a login name and password. This login name and password must match the incoming login and password expected by the remote server.

Using the sp_addlinkedsrvlogin stored procedure to create local login mappings.

Note For logins created by SQL Server Authentication, it is not necessary to create any local login mappings for executing a stored procedure against a remote server.

Security Note When possible, use Windows Authentication.

Remote Server Security Example

Consider two SQL Server installations, serverSend and serverReceive. serverReceive is configured to map an incoming login from serverSend, called Sales_Mary, to a SQL Server authenticated login in serverReceive, called Alice. Another incoming login from serverSend, called Joe, is mapped to a SQL Server Athenticated login in serverReceive, called Joe.

The following Transact-SQL code can be executed to configure serverSend to perform RPCs against serverReceive:

On serverSend, a local login mapping is created for a Windows Authenticated login Sales\Mary to a login Sales_Mary. No local mapping is necessary for Joe, as the default is to use the same login name and password, and serverReceive has a mapping for Joe: