Post reply

Linked Server - Fails From Desktop, but Works Remotely

  • May 26, 2009 at 9:02 am

    #221536

    On Server DB2, I'm trying to run:

    select * from DB1.DatabaseA.dbo.TableA

    Logged on my desktop, with a connection open to DB2, I get: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    But if I remote desktop onto DB2 it works fine.

    I'm using the same Windows Account for my desktop and the servers.

    Missing something obvious ?

  • May 26, 2009 at 2:51 pm

    #1000204

    Hi use sql authentication mode and personate user. The users are no trusted Unix and Windows.

  • May 26, 2009 at 2:54 pm

    #1000207

    use sql authentication mode and personate user


    - Pradyothana     http://www.msqlserver.com

  • May 27, 2009 at 6:06 am

    #1000504

    fpereiras (5/26/2009)

    Hi use sql authentication mode and personate user. The users are no trusted Unix and Windows.

    Not quite sure what you mean.

    Windows authentication works if I remote onto 1 server, then use linked server to connect to the other server.

  • May 27, 2009 at 6:44 am

    #1000532

    homebrew01 (5/27/2009)

    fpereiras (5/26/2009)

    Hi use sql authentication mode and personate user. The users are no trusted Unix and Windows.

    Not quite sure what you mean.

    Windows authentication works if I remote onto 1 server, then use linked server to connect to the other server.

    I not understand.

  • May 27, 2009 at 8:22 am

    #1000646

    fpereiras (5/27/2009)

    homebrew01 (5/27/2009)

    fpereiras (5/26/2009)

    Hi use sql authentication mode and personate user. The users are no trusted Unix and Windows.

    Not quite sure what you mean.

    Windows authentication works if I remote onto 1 server, then use linked server to connect to the other server.

    I not understand.

    I guess we don't understand each other ! 🙂

    When I open SSMS on my desktop, I use Windows Authentication, so that the user "Domain\MyName" is used to connect to SQL. I have admin rights everywhere. When I connect a session to Server "DB2", and try to use linked server to query records on server "DB1", I get the error (see original post).

    However, if I remote desktop onto server "DB2", using the same Windows account "Domain\MyName", then open SSMS, connect to DB2, I can query records on "DB1" using Linked Server without a problem.

    Since the authentication is the same, I don't understand why I get different results.

  • May 27, 2009 at 8:34 am

    #1000662

    Lololol

    My inglish is poor.

    Sorry, now i understand.

  • May 27, 2009 at 8:51 am

    #1000693

    I believe it has to do with how the linked server was set up...the default is "no security context", which is not what you want.

    go to your linked server properties; the second tab is "Security"

    change the option button to the selection "Be made using the login's current security context"

    instead of "Be made without a security context"

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 27, 2009 at 9:00 am

    #1000704

    You are running into the double-hop problem. If you require Windows Authentication to work you are going to need to setup and configure kerberos for the service line account(s) for those SQL Servers.

    If not, then you can use SQL Authentication and change the security context to use a specific account or map local users to remote users.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • May 28, 2009 at 10:33 am

    #1001568

    Pradyothana Shastry (5/26/2009)

    use sql authentication mode and personate user

    Pradyothana,

    Why do you post copied text from other people's posts? Do you mean to comment on them and just hit the "post" button too soon?

    I've seen you do this at least four times, so have to ask: WHY?

  • June 2, 2009 at 3:23 am

    #1003246

    I'm going to echo what Jeffrey said. We've run into this problem ourselves on numerous occassions and it's because the linked server is configured to use the "login's current security context". If you want to configure your linked servers like this and be able to access them from your desktop then you will have to configure kerberos. Brian Kelley did an excellent article on this which should help (http://qa.sqlservercentral.com/articles/Security/65169/[/url])

  • June 2, 2009 at 4:26 am

    #1003285

    Dear All

    I know the concept of Linked server but whenever I start it to do I got fail...

    Can I do it between sql server 2K and 2K5

    Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

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