none
Linked server security

    Pertanyaan

  • 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



    • Diedit oleh MESfred Kamis, 20 April 2017 13.17
    Kamis, 20 April 2017 13.16

Jawaban

  • 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

    • Ditandai sebagai Jawaban oleh MESfred Senin, 02 Juli 2018 12.18
    Senin, 02 Juli 2018 12.18

Semua Balasan

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


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


    • Diedit oleh RamJaddu Kamis, 20 April 2017 13.35
    Kamis, 20 April 2017 13.28
  • 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.

    Kamis, 20 April 2017 13.43
  • No changes to accounts or certificates have been made to the server.

    Fred

    Kamis, 20 April 2017 15.44
  • 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

    Kamis, 20 April 2017 15.51
  • 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.

    Kamis, 20 April 2017 21.47
  • 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

    Rabu, 03 Mei 2017 18.02
  • 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

    • Ditandai sebagai Jawaban oleh MESfred Senin, 02 Juli 2018 12.18
    Senin, 02 Juli 2018 12.18