Troubleshooting upgrade to 2017 instance: double-hop Linked Server Queries Fail

  • Works in SQL Server 2008 R2 but not in new 2017 instance - same configurations username/password on the linked servers
    Instance sql2017 databases db1, db2 linked servers sql2017db1, sql2017db2 (points at self using account with dbo)

    CREATE VIEW db1.schema.view AS SELECT * FROM sql2017db2.db2.schema.table;

    From SSMS window the following query works: (returns >20k rows in <1s)
    SELECT * FROM db1.schema.view; 

    However this one takes over an hour to break: (not enough tempdb space)
    SELECT * FROM sql2017db1.db1.schema.view;

  • Hi Rob,
    have you configured the SPNs for the new SQL Server 2017 instance ? It seems a kerberos configuration issue...
    Try to follow this article: https://sqljana.wordpress.com/2017/06/16/sql-server-curse-of-linked-server-security-and-the-fix-pass-through-authentication/

    HTH
    Danilo

  • This is actually looking like some type of AOG issue at least loosely.
    Others working on this issue have found that by removing the AOG the queries work fine.  Recreate the AOG and it experiences the same issue.  Ticket opened with Microsoft and the current thought is that the second linked server is attempting to connect to the replica for some reason.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply