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 https://i.sstatic.net/5Qzrq.png
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, 
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?

  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Mar 27, 2023 at 11:07
  • ok i clarified it
    – Dorian
    Commented Mar 27, 2023 at 11:14


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.