TestKing.com has two SQL Server 2005 computers named SQL1 and SQL2. A user in TestKing.com named Eric writes many ad hoc queries against the company databases.
Eric has access to the Customer database on SQL1. He does not have access to the Sales database on SQL2. You need to ensure that Eric can write queries that join information from both servers. What should you do first?
A.
Create a linked server on SQL1 to SQL2. Configure the linked server to use impersonation.
B.
Create a linked server on SQL1 to SQL2. Configure the linked server to use mapped logins.
C.
Instruct Eric to write the queries on SQL2 by using the OPENQUERY statement and specifying SQL1 as the server name.
D.
Instruct Eric to specify SQL Server object names by using four-part notation.
Explanation:
You need to define a linked server for each external data source you want to access and then configure the security context under which your distributed queries will run. You can configure the linked server to use one of the following 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.