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

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

  • In reply to Grant Joslin:

    Hi guys,

    not much to add to the Friday afternoon wrap up, other than I've been told that the incident has gone to GES engineers, who, "are the highest technical tier within support and are responsible for interacting with our development teams".

    Should have some better information next week.

    have a good weekend.

    Grant

  • In reply to Grant Joslin:

    So ?

    Paul Jr 

  • In reply to Big_Buck:

    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’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’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

    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

    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

    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 “Sophos EndPoint Management”. 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

    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

    NULL

    58

    TSQL

    SQL Server 2012/14/16/17

    NULL

    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’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 “Sophos Management Service” is making SQL connections using 2 different client libraries. Of which the “SQL 2000” driver connection made by the application name of “Sophos EndPoint  Management “ 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’m not sure as I do not know the exact provider they are using, will pose question to tech support.

  • In reply to Grant Joslin:

    Have you run the CheckDBconnection.exe -a to setup the SQLNLI connection string

    The tool has two working mode. The default is checking the system for TLS 1.2 compatibility (system check mode). The second performs the database settings modification (apply mode: -a).

     

  • In reply to Dominic Lisi:

    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

  • In reply to Grant Joslin:

    Hello

    There have been many mentions to many articles.  When you write "that article", I am not sure which one you refer to ...

    Paul Jr

  • In reply to Big_Buck:

    Hi Paul,

    This is one below the main culprit for me, as I'm never one to read details parameters of application unless I think I need the functionality. I really did not expect that the convert string would be found in a db check tool.

    -----------------------------------------
    Article ID: 127521
    Title: Enterprise Console - Database connection check
    URL: https://sophos.com/kb/127521 
    -----------------------------------------

    Anyway, time to continue, I've spent way too much time on this already and life's too short.

    Big thanks again to Dominic for showing me what I had missed.

    Best wishes to all,

    Grant

  • In reply to Grant Joslin:

    No problem Grant,

    Agree the single line in the document is not particularly obvious or that it is required to run the connectivity tool in -a mode to migrate the console over to TLS 1.2

    Surely going forward the functionality will be built into the Installer/Console itself, and would be the recommended default on install. 

     

  • In reply to Grant Joslin:

    I have tried that tools many times.  It does just nothing.  The -a switch does nothing more ...

    Not sure I get it ...

    Paul Jr

  • In reply to Big_Buck:

    Hi Paul,

    The goal of the tool is to merely convert the sql connection string used by the Sophos app to a SQL client library that supports TLS 1.2. I kept the output of the process which I'll paste below, and yours should look similar. Double check against mine below.

    C:\sourceRepository\Sophos\Sophos Enterprise Console\sec_551\ServerInstaller\CheckDBConnection>CheckDBconnection.exe -a -c -l
    Sophos Connectivity Verifier
    5.5.1.955

    Copyright 2000-2018 Sophos Limited. All rights reserved.
    Sophos and Sophos Anti-Virus are registered trademarks of Sophos Limited and Sop
    hos Group. All other product and company names mentioned are trademarks or regis
    tered trademarks of their respective owners.

    UpdateConnectionString 64 bit system
    UpdateConnectionString :
    Registry Key: Software\Wow6432Node\Sophos\EE\Management Tools
    Value: DatabaseConnectionMS
    Trust cert: True

    Found: Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=SOPHOS551;Data Source=(hostname)\SOPHOS;

    Testing: Provider=SQLNCLI11;Trusted_Connection=Yes;Database=SOPHOS551;Server=(hostname)\SOPHOS;Encrypt=yes;TrustServerCertificate=true;

    TestDatabaseConnectionWithConnectionString 'Provider=SQLNCLI11;Trusted_Connection=Yes;Database=SOPHOS551;Server=(hostname)\SOPHOS;Encrypt=yes;TrustServerCertificate=true;'

    NewConnStr 'Trusted_Connection=Yes;Database=SOPHOS551;(hostname)\SOPHOS;Encrypt=yes;TrustServerCertificate=true;;'

    Test successfull, writing reg key
    UpdateConnectionString :
    Registry Key: Software\Sophos\ServerSecurity
    Value: DatabaseConnection
    Trust cert: True

    Found: Integrated Security=SSPI;Initial Catalog=SophosSecurity;Data Source=(local)\SOPHOS;

    Testing: Provider=SQLNCLI11; Trusted_Connection=Yes;Database=SophosSecurity;Server=(hostname)\SOPHOS;Encrypt=yes;TrustServerCertificate=true;

    TestDatabaseConnectionWithConnectionString 'Provider=SQLNCLI11; Trusted_Connection=Yes;Database=SophosSecurity;(hostname)\SOPHOS;Encrypt=yes;TrustServerCertificate=true;'

    NewConnStr ' Trusted_Connection=Yes;Database=SophosSecurity;(hostname)\SOPHOS;Encrypt=yes;TrustServerCertificate=true;;'

    Test successfull, writing reg key
    UpdateConnectionString :
    Registry Key: Software\Wow6432Node\Sophos\ServerSecurity
    Value: DatabaseConnection
    Trust cert: True

    Found: Integrated Security=SSPI;Initial Catalog=SophosSecurity;Data Source=(local)\SOPHOS;

    Testing: Provider=SQLNCLI11; Trusted_Connection=Yes;Database=SophosSecurity;Server=(hostname)SOPHOS;Encrypt=yes;TrustServerCertificate=true;

    TestDatabaseConnectionWithConnectionString 'Provider=SQLNCLI11; Trusted_Connection=Yes;Database=SophosSecurity;Server=(hostname)\SOPHOS;Encrypt=yes;TrustServerCertificate=true;'

    NewConnStr ' Trusted_Connection=Yes;Database=SophosSecurity;Server=(hostname)\SOPHOS;Encrypt=yes;TrustServerCertificate=true;;'

    Test successfull, writing reg key
    UpdateConnectionString :
    Registry Key: Software\Sophos\Patch
    Value: SophosPatchConnectionString
    Trust cert: True

    Found: Data Source=(local)\SOPHOS;Initial Catalog=SOPHOSPATCH52;Integrated Security=SSPI;Connect Timeout=60

    Testing: Provider=SQLNCLI11; Server=(hostname)\SOPHOS;Database=SOPHOSPATCH52;Trusted_Connection=Yes;Timeout=60;Encrypt=yes;TrustServerCertificate=true;

    TestDatabaseConnectionWithConnectionString 'Provider=SQLNCLI11; Server=(hostname)\SOPHOS;Database=SOPHOSPATCH52;Trusted_Connection=Yes;Timeout=60;Encrypt=yes;TrustServerCertificate=true;'

    NewConnStr ' Server=(hostname)\SOPHOS;Database=SOPHOSPATCH52;Trusted_Connection=Yes;Timeout=60;Encrypt=yes;TrustServerCertificate=true;;'

    Test successfull, writing reg key
    Database connection settings modified successfully

     

    best wishes,

    Grant