0

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?

1
  • Please add your Linked Server DDL and test with mydomain\testaccount logged on locally on Server A and logged on to some other machine (if that is required). – David Browne - Microsoft Nov 24 '20 at 14:29
2

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,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.