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
CONNECT
to the SQL Server instance the linked server points to, and that it has all other applicable permissions needed for the DB operations that will be made via the linked server using? The below answer has some helpful detail as well but I wanted to point out the super obvious just in case but be sure to understand the different configuration option as listed in Daniel's answer below too.