TLS 1.2 configuration with MS SQL 2014

  • adeel.imtiaz

    SSCommitted

    Points: 1805

    Dear Gurus,

     

    I have MS SQL 2014 Installed with following configurations.

    1. Windows 2016 SS.
    2. MS SQL 2014 + SP3 64 Bit EE.
    3. TLS 1.0

      1. Client : Disable
      2. Server Disable

    4. TLS 1.1

      1. Client : Disable
      2. Server Disable

    5. TLS 1.2

      1. Client : Enable
      2. Server Enable

    6. ODBC Drivers :

      1. 64 Bit: 11(2014.120.6024.00)
      2. MSODBCSQL11.DLL

    Below query works fine if TLS 1.0 is enabled.

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'XML_FILE_CREATING',

    @step_id=2,

    @cmdexec_success_code=0,

    @on_success_action=3,

    @on_fail_action=4,

    @on_fail_step_id=4,

    @retry_attempts=1,

    @retry_interval=1,

    @os_run_priority=0, @subsystem=N'ActiveScripting',

    @command=N'Dim oCmd,cmd,rsSQL,oDom, rsLOC, oDBConnect,cnString,sfile_name

    Set oDom = CreateObject("Microsoft.XMLDOM")

    Set oDBConnect = CreateObject("ADODB.Connection")

    cnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DATABASE_NAME ;Data Source=DATA_SOURCE"

    oDBConnect.open cnString

    Set rsSQL = CreateObject("ADODB.Recordset")

    sQuery = "SELECT file_name,xml_query,xml_path FROM [DH_XML_Query]"

    rsSQL.open sQuery, oDBConnect

    Set rsSQL = oDBConnect.Execute(sQuery)

    Do While Not rsSQL.EOF

    sfile_name = rsSQL("file_name")

    Set oCmd = CreateObject("ADODB.Command")

    oCmd.ActiveConnection = oDBConnect

    oCmd.CommandText = rsSQL("xml_query")

    oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

    oCmd.Properties("Output Encoding") = "utf-8"

    oCmd.Properties("Output Stream") = oDom

    oCmd.Execute , , 1024

    oDom.Save rsSQL("xml_path") & "\" & sfile_name & ".xml"

    Set cmd = CreateObject("ADODB.Command")

    cmd.ActiveConnection = oDBConnect

    cmd.CommandText = "Exec SP_DH_MESSAGE_UPDATE_STATUS @S_value = ''" & sfile_name & " ''"

    cmd.Execute

    rsSQL.MoveNext

    Loop

    Set rsSQL = Nothing

    Set oDom=Nothing

    Set ocmd=Nothing

    Set cmd=Nothing

    Set oDBConnect = Nothing',

    @database_name=N'VBScript',

    @flags=0

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

     

     

    When we Disable TLS 1.0 and Enable TLS 1.2 above query gives us below error with "Provider=SQLOLEDB.1"

    Executed as user: NT Service\SQLAgent$SQL2K14. Error Code: 0  Error Source= Microsoft OLE DB Provider for SQL Server  Error Description: [DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error.    Error on Line 6  .  NOTE: The step was retried the requested number of times (1) without succeeding.  The step failed.

     

    When we change Provider from "Provider=SQLOLEDB.1" TO SQLNCL11 it gives us below error.

     

    Executed as user: NT Service\SQLAgent$SQL2K14. Error Code: 0  Error Source= Microsoft SQL Server Native Client 11.0  Error Description: Command dialect is not supported by this provider.    Error on Line 23  .  NOTE: The step was retried the requested number of times (1) without succeeding.  The step failed.

     

    Your support is required.

     

    Thanks

    Malik Adeel Imtiaz

     

  • e4d4

    SSCertifiable

    Points: 5755

    You need to change driver from OLEDB as it don't have support for TLS1.2, there is a new version of OLEDB msoledbsql

    https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-ole-db-driver-for-sql-server/

    or use other supported drivers:

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

  • adeel.imtiaz

    SSCommitted

    Points: 1805

    Thanks for your reply.

    Please confirm.

    During MS SQL 2014 installation, i observed MS SQL 2012 Native Client also get installed with SQL 2014.

    Its version is 11.4.7462.6

    I have upgraded its version to 11.4.7001.0

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

    Microsoft SQL Server 2012 Native Client - QFE Under --->>>Client component downloads Heading.

     

    Please confirm DO I NEED TO UPGRADE THIS OR SHOULD I REVERT IT and ONLY UPGRADE OLE DB VERSION..

     

    Thanks

    Malik Adeel Imtiaz

     

  • adeel.imtiaz

    SSCommitted

    Points: 1805

    Dear Gurus,

     

    The problem has been resolved after installing {msoledbsql_18.2.2.0_x64.msi}

    and update string with below Provider.

    Provider=SQLNCLI11; Server=myServerName\theInstanceName;Database=myDataBase;

    Trusted_Connection=yes;

    to:

    Provider=MSOLEDBSQL; Server=myServerName\theInstanceName; Database=myDataBase;

    Trusted_Connection=yes;

    Reference: https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-ole-db-driver-for-sql-server/

     

    Now i am facing ANOTHER error.

    Message:

    Executed as user: NT Service\SQLSERVERAGENT. Error Code: 0  Error Source= Microsoft OLE DB Driver for SQL Server  Error Description: Command dialect is not supported by this provider.    Error on Line 23

    Current Dialect Is :Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

    Thanks

    Malik Adeel Imtiaz

  • adeel.imtiaz

    SSCommitted

    Points: 1805

    Can someone please help.

     

    Thanks

    Adeel Imtiaz

  • Sue_H

    SSC Guru

    Points: 90287

    You would need to move away from using MSSQLXML dialect. You can select data using FOR XML to return XML data.

     

    Sue

  • adeel.imtiaz

    SSCommitted

    Points: 1805

    Dear Sue,

    Can you please explain in little bit more detail.

    Thanks

    Malik Adeel Imtiaz

  • Sue_H

    SSC Guru

    Points: 90287

    Change the stored procedure to use FOR XML instead of using this that you have in your code:

    oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

    You can return XML data using FOR XML in queries, stored procedures. You didn't used to be able to do that and had to change your code as you have to get XML data returned. I don't think there are XML dialects for some of latest drivers.

     

    Sue

  • adeel.imtiaz

    SSCommitted

    Points: 1805

    Dear Sue,

    Thank you so much for your reply.

    Please see that we have changed our code, the issue is now we need UTF-8 encoding which is available in SP3 please see below link.

    https://support.microsoft.com/en-us/help/3136780/utf-8-encoding-support-for-the-bcp-utility-and-bulk-insert-transact-sq

     

    Just need to confirm that, it is available in SP4 as the client is currently on SP4 of SQL 2014 ?

     

    Thanks

    Malik Adeel Imtiaz

  • Sue_H

    SSC Guru

    Points: 90287

    Yes, SP4 would include the fixes from previous service packs.

    Sue

  • adeel.imtiaz

    SSCommitted

    Points: 1805

    Hi Sue,

    I can see the UTF patch in SP 2 in below link.

    https://support.microsoft.com/en-us/help/3171021

    2019-05-29_10-45-36

    But have unable to find UTF patch in SP 3 in below link.

    https://support.microsoft.com/en-us/help/4022619/sql-server-2014-service-pack-3-release-information

     

    Regards,

    Malik Adeel Imtiaz

  • Sue_H

    SSC Guru

    Points: 90287

    Service packs are cumulative so they include fixes from previous service packs. Service Pack 4 won't list all the fixes from Service Pack 1, 2 and 3. Just those new fixes since Service Pack 3.

    Sue

  • Sue_H

    SSC Guru

    Points: 90287

    I can't find a current reference in Microsoft's documentation but here is an old one which states:

    Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. You do not have to install a previous service pack before you install the latest service pack.

    ARCHIVED: How to obtain the latest service pack for SQL Server 2008 R2

    Sue

  • adeel.imtiaz

    SSCommitted

    Points: 1805

    Excellent. Thank you so much Sue for great support. Very much appreciated.

     

    Thanks

    Malik Adeel Imtiaz

  • Gelixan

    Newbie

    Points: 2

    I think this very useful.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply