I’m trying to figure out how to setup a linked server object from SQLServerA to SQLServerB using Windows Authentication.
I’ve setup many linked servers but have always used a SQL Server login which works fine.
In this scenario, a logon has been created at SQLServerB using Windows Authentication granting access to a specific database.
From SQLServerA, I’m creating a linked server object to SQLServerB. On the Security Page of the linked server configuration, I’m using "Be made using this security context" and enter Remote login:domain\user, With Password: password respectively.
When SQL performs the Test Connection final step, I receive an error indicating “Login failed for user ‘domain\user’ (Microsoft SQL Server, Error: 18456)”.
Is there something special I need to do to create the linked remote server using Windows Authentication as the authorization type instead of a SQL Server login?
If that’s not possible, is there another solution that I can use to query a remote servers other than interactively through SSMS?
My goal is to setup SQL job(s) to import some data from the remote SQL Server.
Thanks
Best Answer
The "Be made using this security context" doesnt support Windows Authentication.
You need to use this:
Or you could use "Not be made" and specify the login with impersonate. Requires that the user exists on both servers aswell.
You can read more here: https://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx