You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Domain.com makes use of multiple servers in a distributed environment. The Domain.com network contains two SQL Server 2005 database server named Certkiller -DB01 and Certkiller -DB02. Both Certkiller -DB01 and Certkiller -DB02 makes use of SQL Server Authentication and make use of different logins. A Domain.com employee named Kara Lang works in the Research and Development department. You instruct her to write a distributed query that joins the data on Certkiller -DB01 with the data on Certkiller -DB02.
What should she do?
A.
She should run SQL Server Profiler over a Remote Desktop connection to Certkiller -DB01. Store the trace in a file on a separate server.
B.
Certkiller -DB02 needs to be configured as a distributed server.
Thereafter Kara Lang should use pass-through authentication.
C.
Both database servers should use the same login name as the security context.
D.
Kara Lang should configure Certkiller -DB02 as a linked server in order to imitate the remote login.
E.
Kara Lang should configure Certkiller -DB02 as a remote server.
Thereafter she should write the query on Certkiller -DB01.
Explanation:
Special attention needs to be paid on the security context for the external connection when you make use of linked servers to access external data sources. 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.