June 21, 2024 at 12:13 pm
Yes I know Linked Servers can be evil... but they can be fine and fair enough for small data sets.
If trying to setup a linked server and using either the MSOLEDBSQL that ships with SQL2019 or the newer one MSOLEDBSQL19 (that by default wants to be encrypted and trust a valid certificate)... Given using a fully qualified domain name, with a destination instance that has a valid certificate, has anyone managed to get the connection to encrypt?
I've tried specifying for the provider string:
Encryption=Mandatory;
Encrypt=Yes;
Encrypt=True;
Use Encryption for Data=true;
If I try to use MSOLEDBSQL19 (with Allow In Process), I can expand the linked server, see the 'catalogs', and tabkes, but can't even query as for some reason it gives a MSDTC error.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'<Linked Server Name>'
, @srvproduct=N''
, @Provider=N'MSOLEDBSQL'
, @datasrc=N'<FQDN Target SQL Server Name>'
, @provstr=N'Encryption=Mandatory;'
SELECT encrypt_option
FROM <Linked Server Name>.master.sys.dm_exec_connections WHERE session_id = @@SPID
June 21, 2024 at 3:21 pm
Our servers force encryption so the following works for me.
USE [master];
GO
EXEC dbo.sp_addlinkedserver
@server = N'<FQDN of Linked Server>'
,@srvproduct=N'SQL Server';
GO
June 24, 2024 at 12:55 pm
True, that's one way to force the option. But if you have an instance with multiple applications, and you want to ensure each works individually before flipping that big switch...
Using OpenQuery, and you get to see things work a little bit..
The below, as expected as the MSOLEDBSQL driver doesn't by default encrypt:
EXEC master.dbo.sp_dropserver @server = N'<Linked Server Name>'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'<Linked Server Name>'
, @srvproduct=N''
, @Provider=N'MSOLEDBSQL'
, @datasrc=N'<FQDN>'
, @catalog=N'master'
, @provstr=N''
SELECT * FROM OPENQUERY([<Linked Server Name>],'SELECT encrypt_option FROM master.sys.dm_exec_connections WHERE session_id = @@SPID')
Result: encrypt_option : false
The below, as expected as the MSOLEDBSQL driver doesn't by default encrypt:
EXEC master.dbo.sp_dropserver @server = N'<Linked Server Name>'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'<Linked Server Name>'
, @srvproduct=N''
, @Provider=N'MSOLEDBSQL'
, @datasrc=N'<FQDN>'
, @catalog=N'master'
, @provstr=N'Encrypt=Yes'
SELECT * FROM OPENQUERY([<Linked Server Name>],'SELECT encrypt_option FROM master.sys.dm_exec_connections WHERE session_id = @@SPID')
Result: encrypt_option : true
The MSOLEDBSQL19 which by default has encrypt on (with Allow In-Process enabled on the provider within SQL):
EXEC master.dbo.sp_dropserver @server = N'<Linked Server Name>'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'<Linked Server Name>'
, @srvproduct=N''
, @Provider=N'MSOLEDBSQL'
, @datasrc=N'<FQDN>'
, @catalog=N'master'
, @provstr=N'Encrypt=Yes'
SELECT * FROM OPENQUERY([<Linked Server Name>],'SELECT encrypt_option FROM master.sys.dm_exec_connections WHERE session_id = @@SPID')
Result: encrypt_option : true
However if you try and use the linked server in four part name, MS DTC comes along and spoils your day:
SELECT encrypt_option
FROM [<Linked Server Name>].master.sys.dm_exec_connections WHERE session_id = @@SPID
Msg 8522, Level 16, State 3, Line 21
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
You think, a-ha.. maybe I'll change a setting on the linked server:
EXEC master.dbo.sp_serveroption @server=N'<Linked Server Name>', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
But you're luck continues.
Msg 8522, Level 16, State 3, Line 21
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.
Curious isn't it?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply