6

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

2
  • Ronnie - Where you get the error "“Login failed for user ‘domain\user'" are you sure that this domain user is defined and setup to 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. Aug 8, 2017 at 19:59
  • Thanks for the reply McDonald - I can connect to the DB instance using SSMS and run queries against the DB that I've been given permission to.
    – ronniej962
    Aug 8, 2017 at 20:06

1 Answer 1

10

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

3
  • Thanks Daniel! I know I tried the other authentication methods but I will read through the document you provided and see how I can make that happen. When you say "Requires that the user exists o both servers" - do you mean at the SQL login level or OS level?
    – ronniej962
    Aug 8, 2017 at 20:13
  • @ronniej962 a SQL Login with Windows Authentication (in your case) and not a local user at the server. :) Just to be sure i tried it myself before posting so you should be good. :) Aug 8, 2017 at 20:14
  • 1
    >>>do you mean at the SQL login level or OS level?<<< Both. If your servers are not members of the same or trusted domain, nothing you can do using Windows Authentication. Except for one case when local Windows accounts are exactly the same on both servers(with the same password). Kerberos delegation is another requirement to accomplish what you want
    – sepupic
    Aug 9, 2017 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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