Security for Linked Servers
During a linked server connection (for example, when it processes 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, you must create a login mapping between the linked servers by using SQL Server system stored procedures.
Linked server login mappings can be added by using sp_addlinkedsrvlogin and removed by using sp_droplinkedsrvlogin. A linked server login mapping establishes a remote login and remote password for a specified linked server and local login. When SQL Server connects to a linked server to execute a distributed query or a stored procedure, SQL Server looks for any login mappings for the current login that is executing the query or the procedure. If there is a login mapping, SQL Server sends the corresponding remote login and password when it connects to the linked server.
For example, a mapping for a linked server, S1, has been set up for a local login, U1, to remote login, U2, by using a remote password of 8r4li034j7$. When local login U1 executes a distributed query that accesses a table stored in linked server S1, U2 and 8r4li034j7$ 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 kind of mapping is known as self-mapping. When a linked server is added by using sp_addlinkedserver, a default self-mapping is added for all local logins. If security account delegation is available and the linked server supports Windows Authentication, self-mapping for the Windows authenticated logins is supported.
Note
When possible, use Windows Authentication.
If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication. Therefore, you must set up a local login mapping from a login that uses Windows Authentication to a specific login on the linked server that is not a Windows authenticated login. In this case, the remote login uses SQL Server Authentication if the linked server is an instance of SQL Server.
Distributed queries are subject to the permissions granted to the remote login by the linked server on the remote table. However, SQL Server does not perform any permission validation at compile time. Any permission violations are detected at query execution time as reported by the provider.
Note
To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.
Testing Linked Servers
In Object Explorer, right-click the linked server, and then click Test Connection.