Security for Remote Servers
To enable remote procedure calls (RPC) against a remote server, you must set up login mappings on the remote server and possibly on the local server that is running an instance of SQL Server. RPC is disabled by default in SQL Server 2005. This configuration enhances the security of your server by reducing its attackable surface area. Before using RPC you must enable this feature. For more information see sp_configure.
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. For more information, see Linking Servers.
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 specified server to a local login. Remote login mappings can be set up by using the sp_addremotelogin stored procedure on the remote server.
Note
The trusted option of sp_remoteoption is not supported in SQL Server 2005.
Setting Up the Local Server
For SQL Server authenticated local logins, you do not have to set up a login mapping on the local server. SQL Server uses the local login and password to connect to the remote server. For Windows authenticated logins, set 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 by using the sp_addlinkedservlogin stored procedure. This login name and password must match the incoming login and password expected by the remote server, as created by sp_addremotelogin.
Note
When possible, use Windows Authentication.
Remote Server Security Example
Consider these 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 example configures serverSend to perform RPCs against serverReceive.
--Create remote server entry for RPCs
--from serverSend in serverReceive.
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
--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 authenticated login Sales\Mary to a login Sales_Mary. No local mapping is required for Joe, because 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 authenticated login.
--Sales\Mary to Sales_Mary. The password should match the
--password for the login Sales_Mary in serverReceive.
EXEC sp_addlinkedsrvlogin 'serverReceive', false, 'Sales\Mary',
'Sales_Mary', '430[fj%dk'
GO
See Also
Concepts
Other Resources
sp_configure (Transact-SQL)
sp_addremotelogin (Transact-SQL)
sys.sysremotelogins (Transact-SQL)