TLS 1.2 configuration with MS SQL 2014

Question

TLS 1.2 configuration with MS SQL 2014

adeel.imtiaz

SSCommitted

Points: 1805
More actions
May 20, 2019 at 5:52 am

#3645218
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

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

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

e4d4 SSCertifiable Points: 5777 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

adeel.imtiaz

SSCommitted

Points: 1805

May 22, 2019 at 10:56 am

#3646048

Can someone please help.

Thanks

Adeel Imtiaz

Sue_H SSC Guru Points: 90860 More actions · Answer 5

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 More actions · Answer 6

Dear Sue,

Can you please explain in little bit more detail.

Thanks

Malik Adeel Imtiaz

Sue_H SSC Guru Points: 90860 More actions · Answer 7

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 More actions · Answer 8

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: 90860 More actions · Answer 9

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

Sue

adeel.imtiaz SSCommitted Points: 1805 More actions · Answer 10

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: 90860 More actions · Answer 11

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: 90860 More actions · Answer 12

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 More actions · Answer 13

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

Thanks

Malik Adeel Imtiaz

Gelixan Newbie Points: 2 More actions · Answer 14

Gelixan

Newbie

Points: 2

May 31, 2019 at 11:36 am

#3648930

I think this very useful.