Linked servers and Network Logins

  • Hi all,
     
    ** This is slowly driving me insane **
     
    I am setting up the Simple Log Shipper from the SQL Resource Kit - which works.
    I have the two servers - A and B in this case. The account running SQL Server and the agent is the same for both servers - a domain user - 'MyDomain\MySqlAcct'
     
    I set up a linked server on 'A' for server 'B' (A being the primary server, logshipping to B). (It's also set up from B -> A)
     
    Now, when I execute a job I created it will run the procedures fine. The job actually calls a SP on Server B, so I know that the linked servers for this set up are working!
     
    Even if I create a job that just has "SELECT * FROM LinkedServerB.SomeDB.dbo.RandomTable" that will run fine!
     
    Now, that is all running using the 'MyDomain\MySqlAcct' account.
     
    However, if I connect in QA to server A with my own domain login (MyDomain\Krissy) from my own machine, then try run something like:
     
    SELECT * FROM LinkedServerB.SomeDB.dbo.RandomTable
     
    I get the following error:
     
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
     
    This also happens when I try it the other way around.
    SELECT * FROM LinkedServerA.SomeOtherDB.dbo.AnotherRandomTable
     
    How can I get the linked server to work for my domain login? I have tried a few things but just can't get it to work!
     
    I don't get how it can work for the other domain account that is running SQL Server.
     
    TIA!
     
    Krissy
  • At the linked server properties

    you can set how your account will be "transferred" across the network to the linked server.

    Perhaps you can try with using the current login security context or a fixed mapping.

  • I have set for this "Be made using the login's current security context".
     
    I have also set under the Local Login section with my domain username and ticked 'Impersonate'.
     
    But I still get the problem

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply