Linked Server with Windows Authentication Permissions

Question

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

Answer 1

The "Be made using this security context" doesnt support Windows Authentication.

Be made using this security context Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

You need to use this:

When connecting to the local server using Windows Authentication (recommended), select Be made using the login's current security context to connect to the remote server using the same Windows Authentication credentials.

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