locked
SQL2008 linked server no longer available "NT-AUTHORITY\ANONYMOUS-LOGIN" RRS feed

  • Question

  • Hi all,

    I set up a linked server on Server01 via SSMS-GUI. Test in SSMS console via "exec msdb.sys.sp_testlinkedserver Server02" was successful. Some time later (about 1 hour) the same test fails. Error message: "Message 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY \ ANONYMOUS LOGON'".

    It seems that after setting up the linked server the connection is successfully built up. After a while the connection is cut and not built up again.

    A new installation via TSQL script (exec sp_dropserver ... + exec sp_addlinkedserver ...) is not successful, I get the same error. But when I call the Linked Server Properties in SSMS console and press OK button without changing anything before the connection can be built up - for a short time.

    Why the test is successful and then fails without changing the permissions and for the same user (Admin01)?


    Setup-script for linked server on Server01 (fyi; I set it up successfully via GUI):
    EXEC master.dbo.sp_addlinkedserver @server = N'Server02', @srvproduct=N'SQL Server'
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Server02',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

    Research to date f.i.:
    - http://support.microsoft.com/kb/839569/de  
      But it is not SQL2005, not Server2003 and the security option is not available.
    - http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8ec97763-c978-48dd-a92c-4b47e6a959d7
      But the linked-Server-setup is successful. The first test is successful too. Later, the test will fail only, but without changing the user or privilege, not a reboot or something similar.

    My environment:
    - linked server from SQL-Server 2008 Std. (not R2) zu SQL-Server 2008 Std. (not R2)
    - all Server on Windows Server 2008 Std. (not R2), no Domaincontroller
    - all actions executed as sysadmin-user "Admin01"
    - on all SQL-Server only Windows-Autentification, no SQL-User

    - domain\tuser01 = SQL-Server-Serviceaccount on all SQL-Server
    - SPNs:
    C:\>setspn -l domain\tuser01
    Registrierte Dienstprinzipalnamen (SPN) für CN=tuser01,OU=Infrastruktur,OU=User
    s,OU=MyBusiness,DC=domain,DC=local:
            MSSQLSvc/Server02.domain.local:1433
            MSSQLSvc/Server02.domain.local
            MSSQLSvc/Server01.domain.local:1433
            MSSQLSvc/Server01.domain.local

    C:\>setspn -l domain\server01
    Registrierte Dienstprinzipalnamen (SPN) für CN=Server01,OU=UAT,OU=SQL-Server,OU=
    Servers,OU=MyBusiness,DC=domain,DC=local:
            WSMAN/Server01
            WSMAN/Server01.domain.local
            TERMSRV/Server01
            TERMSRV/Server01.domain.local
            HOST/Server01
            HOST/Server01.domain.local

    C:\>setspn -l domain\server02
    Registrierte Dienstprinzipalnamen (SPN) für CN=Server02,OU=UAT,OU=SQL-Server,OU=
    Servers,OU=MyBusiness,DC=domain,DC=local:
            WSMAN/Server02
            WSMAN/Server02.domain.local
            TERMSRV/Server02
            TERMSRV/Server02.domain.local
            HOST/Server02
            HOST/Server02.domain.local

    My questions are:
    How do I build up again a successfully established connection?
    or
    What should I change in order to use a successfully set up linked server with no other change always successful?

    Thanks for support.

    Lisa.

    Tuesday, July 17, 2012 10:07 AM

Answers

All replies