locked
After removing all SHA1 Ciphers from Windows server 2016, ODBC cannot connect to SQL2016 instance. RRS feed

  • Question

  • As you may know all SHA1 ciphers have been deprecated so we need to know why ODBC is 
    not using a strong Cipher to connect to SQL.  We are able to reproduce the problem in 
    our lab using the customer's Cipher configuration.

    PS C:\Users\Administrator> Get-TlsCipherSuite | ft name

    Name
    ----
    TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384
    TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256
    TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
    TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
    TLS_DHE_RSA_WITH_AES_256_GCM_SHA384
    TLS_DHE_RSA_WITH_AES_128_GCM_SHA256
    TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384
    TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256
    TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384
    TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256
    TLS_RSA_WITH_AES_256_GCM_SHA384
    TLS_RSA_WITH_AES_128_GCM_SHA256
    TLS_RSA_WITH_AES_256_CBC_SHA256
    TLS_RSA_WITH_AES_128_CBC_SHA256
    TLS_DHE_DSS_WITH_AES_256_CBC_SHA256
    TLS_DHE_DSS_WITH_AES_128_CBC_SHA256
    TLS_PSK_WITH_AES_256_GCM_SHA384
    TLS_PSK_WITH_AES_128_GCM_SHA256
    TLS_PSK_WITH_AES_256_CBC_SHA384
    TLS_PSK_WITH_AES_128_CBC_SHA256

    If we add TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA it allows ODBC to connect

    I see following error also from LOGs

    2017-05-17 16:20:32.38 Server      CLR version v4.0.30319 loaded.
    2017-05-17 16:20:32.63 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
    2017-05-17 16:20:32.93 spid5s      Resource governor reconfiguration succeeded.
    2017-05-17 16:20:32.93 spid5s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
    2017-05-17 16:20:32.95 Server      Software Usage Metrics is disabled.
    2017-05-17 16:20:32.95 spid5s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
    2017-05-17 16:20:33.20 spid5s      SQL Trace ID 1 was started by login "sa".
    2017-05-17 16:20:33.23 spid5s      Server name is 'ORF-SQLTEST2016'. This is an informational message only. No user action is required.
    2017-05-17 16:20:33.44 spid16s     A self-generated certificate was successfully loaded for encryption.
    2017-05-17 16:20:33.45 spid16s     Server is listening on [ 'any' <ipv6> 1433].
    2017-05-17 16:20:33.45 spid16s     Server is listening on [ 'any' <ipv4> 1433].
    2017-05-17 16:20:33.45 spid16s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2017-05-17 16:20:33.45 spid16s     Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2017-05-17 16:20:33.45 Server      Server is listening on [ ::1 <ipv6> 1434].
    2017-05-17 16:20:33.45 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2017-05-17 16:20:33.45 Server      Dedicated admin connection support was established for listening locally on port 1434.

    ***********************************************
    2017-05-17 16:20:33.46 spid16s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2017-05-17 16:20:33.46 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
    2017-05-17 16:20:33.55 spid17s     Error: 9954, Severity: 16, State: 1.
    2017-05-17 16:20:33.55 spid17s     SQL Server failed to communicate with filter daemon launch service  (Windows error: Access is denied.
    ). Full-Text filter daemon process failed to start. Full-text search functionality will not be available.

    ************************************************
    2017-05-17 16:20:33.56 spid5s      Starting up database 'msdb'.
    2017-05-17 16:20:33.56 spid6s      Starting up database 'mssqlsystemresource'.
    2017-05-17 16:20:33.59 spid6s      The resource database build version is 13.00.4001. This is an informational message only. No user action is required.
    2017-05-17 16:20:33.62 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ORF-SQLTEST2016. ] for the SQL Server service.
    2017-05-17 16:20:33.64 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ORF-SQLTEST2016.:1433 ] for the SQL Server service.
    2017-05-17 16:20:33.72 spid6s      Starting up database 'model'.
    2017-05-17 16:20:33.95 spid6s      Polybase feature disabled.
    2017-05-17 16:20:33.95 spid6s      Clearing tempdb database.
    2017-05-17 16:20:34.91 spid6s      Starting up database 'tempdb'.
    2017-05-17 16:20:35.32 spid6s      The tempdb database has 4 data file(s).
    2017-05-17 16:20:35.34 spid19s     The Service Broker endpoint is in disabled or stopped state.
    2017-05-17 16:20:35.35 spid19s     The Database Mirroring endpoint is in disabled or stopped state.


    It seems to me that the windows logged in user had issue through the kerberos and SPN service configured to check authentication. Mostly Once we enable the
    SSL certificates exchange is failing. 


    • Edited by aml4848 Wednesday, June 7, 2017 9:53 PM
    Friday, June 2, 2017 6:28 PM

Answers

  • After a month of back and forth with Microsoft support on this issue we finally found the solution and I figured that I would share.

    Our SQL backup program uses ODBC driver OLE DB Provider for SQL server to make connection to SQL instance, however in a pure TLS 1.2 environment Microsoft explains that this ODBC provider has been depreciated.

    The solution for us:  We modified our code to use the recommended SQL Server Native Client 11.0 ODBC provider and the problem was resolved.

    https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server

    FROM MICROSOFT SUPPORT:

    We can use a UDL test to confirm that this is working. ODBC is fine for TLS 1.2. 

    Create the data link (UDL) file

     

    1.        Right-click the Windows desktop, point to New, and then click Text Document. A new file is created by default (New Text Document.txt).
    2.        **If you see the .txt then you can skip this step** Open Windows Explorer, and on the Tools menu, click View then Options. On the View tab, clear the Hide file extensions for known file types check box and then click OK.
    3.        Right-click the Text Document you created in step 1, choose Rename, and then change the name and extension of the file to: test.udl
    4.        A warning might appear, explaining that changing file extensions could cause files to become unusable. Disregard this warning.
    5.        Double-click the (test.udl) file or you can optionally right-click it, and then click Properties. This opens the Data Link Properties dialog box.
    6.        Enter the SQL server’s hostname followed by a backslash (\) in the first box. E.g. Server08\SQLInstance
    7.        Under ‘Enter information to log on to the server’ select one of the radio buttons...
      • ‘Use Windows NT Integrated security’ if the SQL server uses Windows authentication 
      • ‘User a specific user name and password’ if the SQL server uses SQL authentication.
    8.        Click the ‘Test Connection’ button.

    TLS issues often give us an SSL Security Context error when trying to connect from the client to the server using the ODBC for SQL Server. You should then be able to confirm that the SQL Native Client can connect without issues. 

    • Marked as answer by aml4848 Thursday, June 29, 2017 2:15 AM
    Thursday, June 29, 2017 2:15 AM

All replies

  • we need to know why ODBC is not using a strong Cipher to connect to SQL. 
    ....

    2017-05-17 16:20:33.55 spid17s     Error: 9954, Severity: 16, State: 1.
    2017-05-17 16:20:33.55 spid17s     SQL Server failed to communicate with filter daemon launch service  (Windows error: Access is denied.
    ). Full-Text filter daemon process failed to start.

    Hello,

    Your post is a bit confusing and I currently don't see a relation between the two points you are reporting here.

    The filter daemon is part of SQL Server Full Text Index engine and by the error message it seems the used service account don't have enough permissions to start as a service, see Set the Service Account for the Full-text Filter Daemon Launcher for how to fix it.

    And what's exactly your issue with ODBC connections to SQL Server, you didn't provide details about it?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, June 3, 2017 5:45 AM
  • Olaf

    Thank you for your reply.  Perhaps I should go into more detail of what I am troubleshooting and what I am seeing.

    We are using a SQL backup software and part of the backup process is to tell ODBC to establish a connection to SQL server.

    When we break down our logs we can see that the ODBC connection to SQL is failing when we disable all SHA1 Ciphers.

    [14:38:49.690]  [SC2SQL2016-N1] Waiting for SQL Server to respond...
    [14:38:49.691]  [SC2SQL2016-N1] Waiting for SQL Server to respond...
    [14:40:21.396]  [SC2SQL2016-N1] Failed to establish an ODBC connection.
    [14:40:21.397]  [SC2SQL2016-N1] Failed to establish an ODBC connection.
    [14:40:21.398]  [SC2SQL2016-N1] Error: [Microsoft][ODBC SQL Server Driver][Shared Memory]SSL Security error
    [14:40:21.398]  [SC2SQL2016-N1] Error: [Microsoft][ODBC SQL Server Driver][Shared Memory]SSL Security error

    However if we add back this Sha1 Cipher the backup is successful "PS C:\Users\Administrator> enable-TlsCipherSuite -name TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA"

    We tested the connection to SQL via ODBC connect when the Cipher was Enabled/Disabled and ODBC fails to connect on 2 of our servers.  The one I showed above does have the full text index enabled however I see the same result on SQL servers that do not have FTI.

    We also see this in the System event log

    Log Name:      System
    Source:        Schannel
    Date:          6/7/2017 2:38:38 PM
    Event ID:      36874
    Task Category: None
    Level:         Error
    Keywords:      
    User:          SYSTEM
    Computer:      SC2SQL2016-N1
    Description:
    An unknown connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The TLS connection request has failed.
    Event Xml:
    <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
      <System>
        <Provider Name="Schannel" Guid="{1F678132-5938-4686-9FDC-C8FF68F15C85}" />
        <EventID>36874</EventID>
        <Version>0</Version>
        <Level>2</Level>
        <Task>0</Task>
        <Opcode>0</Opcode>
        <Keywords>0x8000000000000000</Keywords>
        <TimeCreated SystemTime="2017-06-07T21:38:38.236047500Z" />
        <EventRecordID>19861</EventRecordID>
        <Correlation ActivityID="{E61CC9DA-DBD6-0001-E9C9-1CE6D6DBD201}" />
        <Execution ProcessID="632" ThreadID="5620" />
        <Channel>System</Channel>
        <Computer>SC2SQL2016-/Computer>
        <Security UserID="S-1-5-18" />
      </System>
      <EventData>
        <Data Name="Protocol">unknown</Data>
      </EventData>
    </Event>

    *********************************************************************************

    Manually testing ODBC

    Microsoft SQL Server ODBC Driver Version 10.00.14393

    Running connectivity tests...

    Attempting connection
    [Microsoft][ODBC SQL Server Driver][Shared Memory]SSL Security error

    TESTS FAILED!

    Enabled SHA1 Cipher

    Microsoft SQL Server ODBC Driver Version 10.00.14393

    Running connectivity tests...

    Attempting connection
    Connection established
    Verifying option settings
    Disconnecting from server

    TESTS COMPLETED SUCCESSFULLY!

    Wednesday, June 7, 2017 9:51 PM
  • After a month of back and forth with Microsoft support on this issue we finally found the solution and I figured that I would share.

    Our SQL backup program uses ODBC driver OLE DB Provider for SQL server to make connection to SQL instance, however in a pure TLS 1.2 environment Microsoft explains that this ODBC provider has been depreciated.

    The solution for us:  We modified our code to use the recommended SQL Server Native Client 11.0 ODBC provider and the problem was resolved.

    https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server

    FROM MICROSOFT SUPPORT:

    We can use a UDL test to confirm that this is working. ODBC is fine for TLS 1.2. 

    Create the data link (UDL) file

     

    1.        Right-click the Windows desktop, point to New, and then click Text Document. A new file is created by default (New Text Document.txt).
    2.        **If you see the .txt then you can skip this step** Open Windows Explorer, and on the Tools menu, click View then Options. On the View tab, clear the Hide file extensions for known file types check box and then click OK.
    3.        Right-click the Text Document you created in step 1, choose Rename, and then change the name and extension of the file to: test.udl
    4.        A warning might appear, explaining that changing file extensions could cause files to become unusable. Disregard this warning.
    5.        Double-click the (test.udl) file or you can optionally right-click it, and then click Properties. This opens the Data Link Properties dialog box.
    6.        Enter the SQL server’s hostname followed by a backslash (\) in the first box. E.g. Server08\SQLInstance
    7.        Under ‘Enter information to log on to the server’ select one of the radio buttons...
      • ‘Use Windows NT Integrated security’ if the SQL server uses Windows authentication 
      • ‘User a specific user name and password’ if the SQL server uses SQL authentication.
    8.        Click the ‘Test Connection’ button.

    TLS issues often give us an SSL Security Context error when trying to connect from the client to the server using the ODBC for SQL Server. You should then be able to confirm that the SQL Native Client can connect without issues. 

    • Marked as answer by aml4848 Thursday, June 29, 2017 2:15 AM
    Thursday, June 29, 2017 2:15 AM