none
Linked server security

    Question

  • There is a linked server on our development server that connects to the production server.  This linked server used to work but has stopped with the error message "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." using my domain userid.  I am not sure what changed.  My linked server on my local database on my laptop that connects to the production server still works using my domain userid.  Both linked servers are setup with the security option "Be made using the login's current security context."  As mentioned before this used to work.

    The development SQL Server is Microsoft SQL Server Developer Edition (64-bit) Version 10.50.2550.0.

    The development server is Windows Server 2008 R2 Standard 64 bit Service Pack 1.

    Any ideas on what might have changed on the development server?

    Thanks,


    Fred



    • Edited by MESfred Thursday, April 20, 2017 1:17 PM
    Thursday, April 20, 2017 1:16 PM

Answers

  • The problem was created by moving the SQL Server to Azure and not correctly setting up the permissions. This was fixed and a new service account was setup for the linked server. This corrected the problem.

    Fred

    • Marked as answer by MESfred Monday, July 2, 2018 12:18 PM
    Monday, July 2, 2018 12:18 PM

All replies

  • Is development server SQL service account changes or any SSL certificates installed?


    http://uk.linkedin.com/in/ramjaddu


    • Edited by RamJaddu Thursday, April 20, 2017 1:35 PM
    Thursday, April 20, 2017 1:28 PM
  • you need set SPN .

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7e75b7d7-641f-4519-b5ea-9567985a2cce/linked-server-error-login-failed-for-user-nt-authorityanonymous-logon?forum=sqldatabaseengine


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Thursday, April 20, 2017 1:43 PM
  • No changes to accounts or certificates have been made to the server.

    Fred

    Thursday, April 20, 2017 3:44 PM
  • When I test the connection I have connected to the development server using Windows Authentication (which uses my domain userid).  I am testing in SSMS.

    Fred

    Thursday, April 20, 2017 3:51 PM
  • This sounds like a typical double-hop issue. That is, the production server does not trust the development server to vouch for you. This can be due to changes in the Keberos configuration. You should probably discuss this with your network administrator.

    Unrelated to your problem, but:

    The development SQL Server is Microsoft SQL Server Developer Edition (64-bit) Version 10.50.2550.0.

    The development server is Windows Server 2008 R2 Standard 64 bit Service Pack 1.

    These versions are higly outdated and you should install Service Pack 3 to be on a supported version.

    Thursday, April 20, 2017 9:47 PM
  • I found a tool called "Microsoft's Kerberos Configuration Manager for SQL Server" which showed that several SPNs were missing.  I had the network administrator set these.  Now they are showing up in the tool but my linked server still has the same problem.

    Fred

    Wednesday, May 3, 2017 6:02 PM
  • The problem was created by moving the SQL Server to Azure and not correctly setting up the permissions. This was fixed and a new service account was setup for the linked server. This corrected the problem.

    Fred

    • Marked as answer by MESfred Monday, July 2, 2018 12:18 PM
    Monday, July 2, 2018 12:18 PM