Security for Linked Servers

During a linked server connection (for example, when processing a distributed query), the sending server provides a login name and password to connect to the receiving server on its behalf. For this connection to work, create a login mapping between the linked servers, using Microsoft® SQL Server™ stored procedures.

Note The linked server mapping is similar to that used in the SQL Server server-to-server remote procedure call (RPC) mechanism. However, the mapping for RPCs is performed at the receiving server; with linked servers, the mapping is done at the sending server.

Linked server login mappings can be added using sp_addlinkedsrvlogin and removed using sp_droplinkedsrvlogin.

The default mapping for a linked server configuration is to emulate the current security credentials of the login. If no explicit mappings are established for a linked server configuration, the login name and password of the current SQL Server login are passed to the receiving server. When a linked server is added using sp_addlinkedserver, default mapping information is added to SQL Server that allows all users to map to their corresponding user login on the remote server.

If security account delegation is not available on the client or sending server, or the linked server/provider has no concept of Windows NT Authentication Mode, you need to set up a local login mapping from the local login (SQL Server or Windows NT Authentication) to a specific SQL Server login (SQL Server Authentication) for the remote provider on the sending server (the local server) using the sp_addlinkedsrvlogin system stored procedure.

Consider a mapping for a linked server S1 that has been set up from a local login U1 to remote login U2, using a remote password of “my_pwd”. When local login U1 executes a distributed query that accesses a table stored in linked server S1, U2 and “my_pwd” are passed as the user ID and password when SQL Server connects to the linked server S1.

Distributed queries are subject to the permissions enforced by the OLE DB provider on the remote table. While processing a distributed query, SQL Server does not perform any permission validation at compilation time. Any permission violations are detected at query execution time as reported by the provider.

To add a linked server login

See Also