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.