Linked Server to AG Listener Failing on SELECT

• skreebydba

  SSChasing Mays

  Points: 646

  More actions

  October 17, 2023 at 6:50 pm

  #4309930

  I have an AG running on SQL Server 2019 and am trying to connect to the listener via a linked server from a SQL Server 2012 instance using the MultiSubnetFailover=Yes parm.  According to this link from Microsoft:

  https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/establishing-linked-server-to-sql-server-using-msoledb-driver/ba-p/879998,

  The MultiSubnetFailover=Yes parm is only supported on SQL Server 2016 SP2 CU6 and higher.  However, in testing, I've been able to successfully insert, update, and delete via the linked server.  Select statements are failing with an MSDTC error.

  Additionally, the following OPENROWSET query returns successfully:

  SELECT a.*

  FROM OPENROWSET('MSOLEDBSQL', 'Server=xxxx-ls;MultiSubnetFailover=Yes;Uid=LinkedServerLogin;Pwd=XXXXXXX,

  'SELECT * FROM [TestDb].[dbo].[TestTable]') AS a;

  Has anyone run into this issue?  Or have an idea why selects would be the only operation failing?

   

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  October 18, 2023 at 7:10 pm

  #4310647

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.