Has anybody managed to successfully deploy Sophos Enterprise 5.5.1 while using the TLS1.2 database connectivity?

Question

I just cant seem to figure it out how to get it to work...? even though Im on server2012 R2 fully updated, with SQL 2012 SP4. No matter what i do the Sophos Installer always says: 
 
 (x) SQL Server instance does not support TLS 1.2 
 (x) There is no certificate installed that can be used with SQL Server 
 I ignored these warnings and Installed SEC5.5.1 anyway as it still works with TLS1.0, but i relly want it to work with TLS1.2, Anybody else have any similar issues? 
 
 Cheers

Grant Joslin · Answer

Hi guys, I'm going to take a different approach at this point. I found the xls matrix really quick and it appears SQL 2016 SP1 is the latest version supported with 5.4.1. So I'm going to upgrade SQL to this version as it should have the added benefit of exposing the "trace" debug in SQL extended events which will show me the SSL handshake (and what version it is, e.g. TLS 1.2). 
 So working with the article below I should already see this information using SQL 2012 SP4, but I can confirm on a few different SQL boxes that I never see this option. But I do see it on an SQL 2016 SP1 box. 
 www.sqlservercentral.com/.../ 
 The SQL instance is on the same box and is just SQL express, so I'll just snapshot the VM before upgrading, and can rollback if any problems are found. I'll report back in once I've completed and check the above work. Ta, Grant

Grant Joslin · Answer

Hi guys, 
 sorry for the delay but this has turned into a bit of an annoyance. Anyway, long story short I upgraded to 2017 without any change in behaviour, I identified the client connections to the SQL box and realised Sophos was using 2 different client libraries (of which one is not TLS 1.2 compliant). So now I am trying to figure out if there is TLS 1.2 support for this driver, or that Sophos needs to update the parts of the application that is using SQL OLE DB connection strings as per this article from Microsoft tech team. 
 https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-ole-db-driver-for-sql-server/ At this point I&rsquo;m not sure as I do not know the exact provider they are using. 
 It's worth noting that this was done in a DEV environment and I ran up a fresh Windows 2016 with SEC 5.5.1 and got exactly the same behaviour. 
 Below are some notes I made to get to this point. 
 -------------------------------------------------------------------------- 
 
 R&D team&rsquo;s answer to support is to follow the articles and to speak to Microsoft and SQL experts: ----------------------------------------- Article ID: 127521 Title: Enterprise Console - Database connection check URL: https://sophos.com/kb/127521 ----------------------------------------- ----------------------------------------- Article ID: 131698 Title: Sophos Enterprise Console - How to create the required certificate package to allow TLS 1.2 connection to the database URL: https://sophos.com/kb/131698 ----------------------------------------- 
 
 Sound familiar? 
 Okay, so from here I decided to upgrade to SQL 2017. 
 Next time to find out exactly what are the connections to the database by application and driver. 
 Sp_who2

SPID

Status

BlkBy

DBName

Command

CPUTime

DiskIO

LastBatch

ProgramName

SPID

REQUESTID

51

sleeping

.

master

AWAITING COMMAND

0

07/06/2018 11:01

Microsoft SQL Server Management Studio

51

0

52

sleeping

.

tempdb

AWAITING COMMAND

637166

25177

07/06/2018 11:06

Microsoft SQL Server Management Studio

52

0

53

sleeping

.

master

AWAITING COMMAND

218

1

07/06/2018 11:01

SQLServerCEIP

53

0

54

SUSPENDED

.

master

SELECT

0

5

07/05/2018 14:56

Microsoft SQL Server Management Studio

54

0

55

sleeping

.

tempdb

AWAITING COMMAND

2481

66

07/06/2018 9:45

Microsoft SQL Server Management Studio

55

0

56

RUNNABLE

.

master

SELECT INTO

110

45

07/06/2018 11:06

Microsoft SQL Server Management Studio - Query

56

0

57

sleeping

.

SOPHOS551

AWAITING COMMAND

0

07/06/2018 11:04

.Net SqlClient Data Provider

57

0

58

SUSPENDED

.

master

SELECT

1516

0

07/05/2018 16:06

SQL Server Profiler - 49bc8e49-f62d-41bd-92c3-2437f88530d2

58

0

59

sleeping

.

master

AWAITING COMMAND

47

0

07/06/2018 10:31

Microsoft SQL Server Management Studio - Query

59

0

60

sleeping

.

SOPHOS551

AWAITING COMMAND

0

07/06/2018 11:05

Sophos Endpoint Management

60

0

61

sleeping

.

SOPHOS551

AWAITING COMMAND

31

3

07/06/2018 11:06

Sophos Endpoint Management

61

0

This shows applications accessing the Sophos DB and if we follow SPID 57 & 60 we see 1 is SQLNCLI and the other is &ldquo;Sophos EndPoint Management&rdquo;. So lets follow those 2 into the activity monitor. 
 Activity monitor reveals the same information ass sp_who2 and now we follow the session ID to find out what drivers are being used by the applications. 
 Using this article we find the driver versions: https://www.mssqltips.com/sqlservertip/2198/determine-which-version-of-sql-server-data-access-driver-is-used-by-an-application/

session_id

protocol_type

driver_version

client_tcp_port

local_tcp_port

52

TSQL

SQL Server 2012/14/16/17

49938

49192

53

TSQL

SQL Server 2012/14/16/17

NULL

54

TSQL

SQL Server 2012/14/16/17

49740

49192

55

TSQL

SQL Server 2012/14/16/17

49723

49192

56

TSQL

SQL Server 2012/14/16/17

49746

49192

57

TSQL

SQL Server 2012/14/16/17

NULL

58

TSQL

SQL Server 2012/14/16/17

NULL

59

TSQL

SQL Server 2012/14/16/17

49758

49192

60

TSQL

SQL Server 2000

52782

49192

61

TSQL

SQL Server 2000

52774

49192

62

TSQL

SQL Server 2000

52780

49192

Now we can see that session ID 57 and 60 are using different client libraries as per this article 
 https://msdn.microsoft.com/en-us/library/dd339982.aspx 
 So now using SQL profiler, the trace reveals that both these session ID&rsquo;s come from the same ClientProcessID 2112 
 
 Now task manager will show us what process 2112 actually is: 
 
 So it appears that MgntSvc.exe or &ldquo;Sophos Management Service&rdquo; is making SQL connections using 2 different client libraries. Of which the &ldquo;SQL 2000&rdquo; driver connection made by the application name of &ldquo;Sophos EndPoint Management &ldquo; does not support TLS 1.2. 
 It would appear that this connection is a SQL OLE DB TLS 1.0 connection as illustrated when using a test connection and generating the same error. 
 
 Do Sophos need to update the connection string as per this article? https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-ole-db-driver-for-sql-server/ At this point I&rsquo;m not sure as I do not know the exact provider they are using, will pose question to tech support.

Grant Joslin · Answer

Hi Dominic, 
 Where have you been all this time! No I did not see that a tool called CheckDBconnection.exe is used to change the SQL provider. I've just gone back to check that article and can now see that they have it in the guts of the tool info. Looks like a real silly place to put it to me as I missed it entirely. Not sure why it is in a list of a parameters for an article called DB connection check. Not sure why there is nothing in the setup to tell you that you need to convert the connection string, or even clear steps in that article outlining; 
 1. upgrade 
 2. db check 
 3. convert connection string 
 Thank you, you've saved me a lot of grief as I knew it had to be that, now that I realise this tool is not to check, but to change the SQL connection string I will go back and do exactly that. And just like that, I've disabled TLS 1.0 & 1.1 after changing the connection string and everything works as expected. Really not a fan of that documentation. Many thanks again!!! 
 Grant