Establishing 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.

Linked server login mappings can be added using sp_addlinkedsrvlogin and removed using sp_droplinkedsrvlogin. A linked server login mapping establishes a remote login and remote password for a given linked server and local login. When SQL Server connects to a linked server in order to execute a distributed query or a stored procedure, it looks for any login mappings for the current login that is executing the query of the procedure. If there is one, it sends the corresponding remote login and password while connecting to the linked server.

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.

For example, a mapping for a linked server, S1, has been set up for 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.

The default mapping for a linked server configuration is to emulate the current security credentials of the login. This type of mapping is known as self mapping. When a linked server is added using sp_addlinkedserver, a default self mapping is added for all local logins.

If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, then self mapping will not work for Windows Authenticated logins. Therefore, you need to set up a local login mapping from a Windows Authenticated login to a specific login on the linked server. In this case, the remote login will be a SQL Server Authenticated login if the linked server is an instance of SQL Server.

If security account delegation is available and the linked server supports Windows Authentication, then the self mapping for the Windows Authenticated logins will be supported. For more information about security account delegation, see Security Account Delegation.

Distributed queries are subject to the permissions granted to the remote login by the linked server 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

Transact-SQL