Multiple choice technology databases

Companyxyz.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?

  1. Ensure that both SQL1 and SQL2 use the same login name as the security context for each

  2. Configure SQL2 as a remote server. Write the query on SQL1.

  3. Configure SQL2 as a linked server to impersonate the remote login.

  4. Configure SQL2 as a distributed server. Use pass-through authentication.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

For distributed queries across SQL Servers with different authentication, configure the remote server as a 'linked server' with impersonation settings. This allows passing security context credentials. Remote servers are deprecated; linked servers are the correct approach.

AI explanation

Since SQL1 and SQL2 use SQL Server Authentication with different logins, a straightforward distributed query would fail because SQL1 can't automatically pass along SQL2's credentials. The fix is to configure SQL2 as a linked server on SQL1 and set it up to impersonate/map to the appropriate remote login (via a linked-server login mapping), so the distributed query authenticates correctly against SQL2. Ensuring identical login names doesn't solve the underlying credential-mapping problem; configuring SQL2 as a 'remote server' is an older/deprecated mechanism not suited to this; 'distributed server' and 'pass-through authentication' aren't real SQL Server linked-server configuration options.

Keep practicing — related questions