Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    London
    Posts
    169

    Unanswered: Linked server authentication failure

    Hi there

    I am having a problem creating a linked server between 2 sql2005 servers with windows authentication

    I get the following error message:

    Msg 18456, Level 14, State 1, Line 1
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

    I have created Server principle names, using SETSPN -A. I have set the servers and my domain account to be trusted for delegation in AD. unfortunately I cannot see the delegation tab in AD, is this because the domain is set to windows 2000 native even though it's on a 2003 box?

    But still no joy, sql server still shows my account connecting as NTLM.

    I cannot use a sql server login...they wont allow it

  2. #2
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    No-one knows a thing about this?


  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How are you creating the linked server?

    Essentially, you have to map the logons between servers (so that the instance you connect to knows what permissions to delegate).
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Just with "be made using the logins current security context" radio box ticked.

    I was under the impression it was impossible to map a domain account to a domain account in a linked server, you can only map to a sql server login, which I cannot use.

  5. #5
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by SQLSlammer
    I was under the impression it was impossible to map a domain account to a domain account in a linked server, you can only map to a sql server login, which I cannot use.
    It is, but it requires that you use Kerberos authentication.

    Read this article: http://www.sqlservercentral.com/arti...ecurity/65169/

  6. #6
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Hi again,

    Both servers are now showing current connections as Kerberos, by running the following query on each server:

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid


    But still I get login failed for annonymous login error when I try to use the linked server?

    Is there anything else I can look into?

  7. #7
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    I think I have fixed it.

    Added some local security policies for the service account such as 'Impersonate client after logon' to the middle tier server.

    I also noticed that named pipes was disabled so I also re-enabled that.

    I found this document very useful for trouble shooting Kerberos delegation


    http://www.microsoft.com/downloads/d...displaylang=en

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •