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?
