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.

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.

Testing Linked Servers

In SQL Server 2005 Service Pack 2, you can test the ability to connect to a linked server. In Object Explorer, right-click the linked server, and then click Test Connection.

See Also

Concepts

Linking Servers
Configuring Linked Servers for Delegation

Other Resources

sp_addlinkedserver (Transact-SQL)
sp_addlinkedsrvlogin (Transact-SQL)
sp_droplinkedsrvlogin (Transact-SQL)
sp_dropserver (Transact-SQL)
sp_linkedservers (Transact-SQL)
sp_serveroption (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History