I have some old SQL Server 2008r2 instances that do not have client certificate set up and force encryption is set to FALSE.
It is on Windows 2008 r2 and really hard to upgrade - for now it must stay as it is.
I have 2 virtual machines that have SQL Server 2019 installed (Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64)).
Both of them have same settings - FORCE ENCRYPTION SET TO TRUE etc
both have same TLS settings
i think not relevant but just in cause
Both have the same linked server definition
EXEC master.dbo.sp_addlinkedserver @server = N'PB01', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PB01',@useself=N'False',@locallogin=NULL,@rmtuser=SOMEUSER',@rmtpassword='SOMEUSERPASS'
EXEC master.dbo.sp_serveroption @server=N'PB01', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01', @optname=N'data access', @optvalue=N'true'
When I'm using this linkedservers to the 2008 r2 instance from both sql 2019 servers do some query that uses this linked PB01 - works FINE
When I run:
SELECT client_net_address,
c.session_id, c.net_transport, c.encrypt_option,
c.auth_scheme, s.host_name, s.program_name,
s.client_interface_name, s.login_name, s.nt_domain,
s.nt_user_name, s.original_login_name, c.connect_time,
s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
I see that from the first 2019 server instance - encrypt_option = true and for second 2019 server instance - encrypt_option = false
So the question is:
Why/how is that possible? Whats more can is I do so from both it is encrypt_option to be = true? i need it to be true from both 2019 servers.
Is there any other thing that can be game changing option here?
