Communicating from a SQL Server 2022 to a SQL Server 2008 over linked server

Question

I'm doing a proof of concept to assess whether we can upgrade some our servers piecemeal to SQL Server 2022.

Unfortunately we still have a SQL Server 2008 which holds an old version of Microsoft Dynamics which we cannot upgrade at this time :(

When trying to connect between SQL Server 2022, we notice that the query won't work as the SNAC has been removed in 2022.

I'm wondering whether there is another way to perform linked server requests across multiple databases and instances.

Has anyone managed to get it to work?

We think SQL Server 2019 --> SQL Server 2008 will work, as would SQL Server 2022 --> SQL Server 2012, but hoping someone has found a way of working around the driver problem, even if it's in the short term.

Anybody have any ideas?

• Thom A: What's wrong with what replaced SQLNCLI, MSOLEDBSQL?
• AlwaysLearning: @ThomA officially MSOLEDBSQL only supports connections back to SQL Server 2012. Ref: Support policies for OLE DB Driver for SQL Server (It might still connect to 2008, it's just not tested nor supported.)
• AlwaysLearning: Tough spot to be in: SQL Server 2008 and R2 ran out of extended support back in 2019.
• siggemannen: You can "probably" reroute your linked server thing through sql server 2019, ie. create linked server between 2008 and 2019, and between 2019 and 2022, and then select "through" it with OPENQUERY, something like: select * from openquery(srv2019,'select * from srv2008.db.schema.table')
• Thom A: True, @AlwaysLearning, though if it does work, you're probably in a better position than using a driver that's been unsupported for ~10 years.

Answer 1

I happen to have access to similar stack due to... reasons, and there doesn't seem to be any problem of accessing the 2008 (nor 2005) server:

EXEC sys.sp_addlinkedserver @server = 'sigge,42008'
go
EXEC sys.sp_addlinkedserver @server = 'sigge,42005'
go
SELECT @@version AS localVersion, oq.*
FROM OPENQUERY([sigge,42008], 'select top 1 @@version AS version, * from master.sys.objects so') oq
union all
SELECT @@version AS localVersion, oq.*
FROM OPENQUERY([sigge,42005], 'select top 1 @@version AS version, * from master.sys.objects so') oq

Outputs:

localVersion	version
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) - redacted	Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) - redacted
Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) - redacted	Microsoft SQL Server 2005 - 9.00.5000.00 (X64)

Although to be fair, perhaps SNAC got installed by some previous version