How to create a SQL Server linked Server with Local SQL Server Authentication User to a remote Windows Authentication

Question

I need to create a Linked Server from Server A that uses a SQL Server Authentication to a Server B that uses a Windows Authentication.

Is the "impersonation" possible?

If "YES" what options, config should I pick?

enter image description here

• msuzuki: After some Google it looks like this is not possible (itectec.com/database/…).
• Charlieface: Which security context do you want the connection under? Will the user authenticate or the server authenticate? It's very unclear what you are trying to achieve?
• msuzuki: @Charlieface the Linked Server has to go from Server A that has a user registered using SQL Authentication to a user in Server B that uses Windows Authentication. I am trying to impersonate the user that uses Windows Authentication.
• Charlieface: No this cannot be done. You can only use Windows Auth if the login exists on both servers and you are connecting to Server A using that login
• msuzuki: thx @Charlieface you confirmed my suspicion. Do you want to make that as an answer so I can vote?

Answer 1

As documented, this is not possible.

If you do not want to do impersonation (using the locally logged-in user on Server A to authenticate to Server B), you must specify a SQL Server Authentication login, you cannot authenticate using Windows Authentication.

The docs for SSMS say:

Remote User
Use the remote user to map user defined in Local login. The Remote User must be a SQL Server Authentication login on the remote server.

And for sp_addlinkedsrvlogin

[ @rmtuser = ] 'rmtuser'
Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows Authentication, rmtuser is a SQL Server login.