we have two SQL Server 2017 "ServerA" and "ServerB" on Windows Server 2016 in the same domain.
Both SQL Server have a Login defined "mydomain\dbaccount".
On ServerA we have a Login defined "mydomain\testaccount" which can access the Database.
We want to connect with the "mydomain\testaccount" to ServerB via Linked Server. The linked server should connect with the "mydomain\dbaccount" via Windows Authentication.
We cannot provide password in linked server of "mydomain\dbaccount" as we want to connect via Windows Authentication. And the Login on ServerB is defined as Windows Authentication.
We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=true and we are getting the following error:
The OLE DB provider "SQLNCLI11" for linked server "ServerB" reported
an error. Authentication failed. Cannot initialize the data source
object of OLE DB provider "SQLNCLI11" for linked server "ServerB". OLE
DB provider "SQLNCLI11" for linked server "ServerB" returned message
"Invalid authorization specification". (Microsoft SQL Server, Error:
7399)
We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=false and remote user as "mydomain\dbaccount" and we are getting the following error:
Login failed for user 'mydomain\dbaccount'. (Microsoft SQL Server,
Error: 18456)
We tried to setup the Linked Server with the local login as "mydomain\testaccount" and impersonate=true and without remote user and in options we checked "Be made using this security context" and defined remote login as "mydomain\dbaccount" without password and we are getting the following error:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL
Server, Error: 18456)
How can we setup Linked Server with remote Windows Authentication?
Best Answer
No can do, I'm afraid. For a local Win login, you can point to a remote SQL login (including the password), or you can ask for impersonation/delegation meaning that the same win account/login will be used on the other SQL Server.
You can't point to a different win account/login on the remote server,