locked
Linked Server using same domain account on different servers RRS feed

  • Question

  • I am trying to create a linked server that will allow a SQL Agent job to transfer data from one SQL Server to another. I am using the same account on each SQL Server. Is there any way to do this without impersonating. This should be straight forward, but it seems not to be. I've tried proxy accounts and impersonate ( am trying to get to a point where I don't have to add passwords all over the place.

    frankm



    • Edited by Wanzejäger Tuesday, February 23, 2016 7:30 PM
    Tuesday, February 23, 2016 5:25 PM

Answers

  • It is unclear what you have done or not.

    But as far as I can see, one of these has to be true:

    1a) SQL Server Agent runs under a domain account, and this domain account is granted access on both the local and the remote server. (Well, it must be sysadmin on the local server). With this setup, you use self-mapping for the linked server. The job must be running as the Agent login itself.

    1b) SQL Server runs under a server SID or other machine local account. In this case the machine account (DOMAIN\SERVER$) for the local machine must be granted access access on the remote server.
    2) You set up login mapping, so whatever user that runs the job maps to an SQL login on the remote server.

    Tuesday, February 23, 2016 10:17 PM