locked
Linked Server issue RRS feed

  • Question

  • Let me give you the full scenario:

    The request:

    Need to create a linked server between SQL1 -> SQL2 and SQL2 - SQL1.

    The way the infrastructure is setup:

    1 Active/Active cluster SQL Server 2008 R2 SP1

    Server1, Server2

    Server1 will be hosting SQL1

    Server2 will be hosting SQL2

    SQL1 is set to list for connections on 1433

    SQL2 is set to list for connections on 1435

    Linked Server Setup:

    LinkedServer created between the SQL instances, RPC and RPCOut enabled

    MSDTC configured allowing inboung/outbound communication on each machine

    'Ad Hoc Distributed Queries' set to 1 on both SQL instances

    SPNs Created:

     MSSQLSvc/SQLVIRTUALNAME01.microsoft.net:1435
     MSSQLSvc/SQLVIRTUALNAME02.microsoft.net:1435
     MSSQLSvc/SQLNODENAME01.microsoft.net:1435
     MSSQLSvc/SQLNODENAME02.microsoft.net:1435


     MSSQLSvc/SQLVIRTUALNAME01:1435
     MSSQLSvc/SQLVIRTUALNAME02:1435
     MSSQLSvc/SQLNODENAME01:1435
     MSSQLSvc/SQLNODENAME02:1435


     MSSQLSvc/SQLVIRTUALNAME01.microsoft.net:1433
     MSSQLSvc/SQLNODENAME02.microsoft.net:1433
     MSSQLSvc/SQLVIRTUALNAME02.microsoft.net:1433
     MSSQLSvc/SQLNODENAME01.microsoft.net:1433


     MSSQLSvc/SQLVIRTUALNAME02:1433
     MSSQLSvc/SQLNODENAME02:1433
     MSSQLSvc/SQLVIRTUALNAME01:1433
     MSSQLSvc/SQLNODENAME01:1433

    The issue:

    When SQL1 is failover to Server1, and SQL2 is failover to Server2 and I run the query below from SQL1, I receive the following error message:

    begin distributed transaction
    select top 10 * from [FTLPOBUGSQLCL02\INSTA].master.sys.objects

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

    When SQL1 is failover to Server1, and SQL2 is failover to Server1 and I run the query below from SQL1, it returns records from the sysdatabases table.

    begin distributed transaction
    select top 10 * from [FTLPOBUGSQLCL02\INSTA].master.sys.objects

    There's something wrong or not configured correctly on my second node, or the SPNs were not setup correctly.

    Are you able to tell from the description above what am I doing wrong here?

    Thanks in advance for the help.


    --- Best Regards, Igor Santos Twitter: @sqlsantos Blog: sqlsantos.wordpress.com

    Wednesday, March 7, 2012 11:21 PM

Answers

  • Hi

    Can you take a look at this article and make sure that SPN's are setup correctly, specifically for the named instances in cluster it didnt look like it is setup correctly.

    This article talks about the whole path of troubleshooting followed and in the end it ended up with SPN not configuring correctly as well.

    Also have a look at this thread for linked server authentication failure

    HTH


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by sqlsantos Thursday, March 8, 2012 11:11 PM
    Thursday, March 8, 2012 9:05 AM