Security for Remote Servers

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

Setting Up the Remote Server

Remote login mappings need to 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

In SQL Server version 7.0, the internal login name used for Microsoft Windows NT® authenticated logins is different from previous releases. Therefore, the following steps need to be performed to create remote server connections for Windows NT authenticated logins. A local login mapping can be set up on a local server that defines what login and password would be used by SQL Server when it makes an RPC connection to a remote server. For SQL Server authenticated logins, it is not necessary to create any local login mappings for executing a stored procedure against a remote server. For Windows NT authenticated logins, it is necessary to create a mapping to a login name and password. This login name and password should match the incoming login and password expected by the remote server. Use the sp_addlinkedsrvlogin stored procedure to create local login mappings.

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 authenticated login in serverReceive called Joe.

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

--Create remote server entry for RPCs from serverSend.

EXEC sp_addserver 'serverSend'

GO

--Create remote login mapping for login 'Sales_Mary' from serverSend

--to Alice.

EXEC sp_addremotelogin 'serverSend', 'Alice', 'Sales_Mary'

GO

--Set trusted option on for this mapping to disable password checking

--for Sales_Mary from serverSend.

EXEC sp_remoteoption 'serverSend', 'Alice', 'Sales_Mary', trusted, true

GO

--Create remote login mapping for login Joe from serverReceive to same login;

--assumes same password for Joe in both servers.

EXEC sp_addremotelogin 'serverSend', 'Joe', 'Joe'

GO

On serverSend, a local login mapping is created for a Windows NT 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:

--Create a remote server entry for RPCs from serverReceive.

EXEC sp_addserver 'serverReceive'

GO

--Create a local login mapping for the Windows NT authenticated login.

--Sales\Mary to Sales_Mary.

EXEC sp_addlinkedsrvlogin 'serverReceive', false, 'Sales\Mary',

   'Sales_Mary,' NULL

GO

See Also