Domain.com has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins.
You need to write a distributed query that joins the data on SQL1 with the data on SQL2.
What should you do?
A.
Ensure that both SQL1 and SQL2 use the same login name as the security context for each server.
B.
Configure SQL2 as a remote server. Write the query on SQL1.
C.
Configure SQL2 as a linked server to impersonate the remote login.
D.
Configure SQL2 as a distributed server. Use pass-through authentication.
Explanation:
When you use linked servers to access external data sources, you should pay special attention to the security context for the external connection. You can configure the linked server to use one of the following three security modes:
Self-mapping – When a linked server is created, this mode is added for all local logins, so SQL Server tries to connect to the external data source using the current user’s login credentials. The same login and password must exist on the remote server. This is the default behaviour.
Delegation – This mode impersonates the Windows local credentials; the connection forwards the credentials of an authenticated Windows user to the linked server. The Windows user account and password must exist on the linked server. Remote Credentials – This mode lets you map local logins to remote logins on the external data source.
Delegation of operating system logins is the securest mechanism.