Difference between revisions of "SSIS:Advisory:DQC for SSIS & TLS 1.2"

From Melissa Data Wiki
Jump to navigation Jump to search
Line 2: Line 2:
{{CustomTOC}}
{{CustomTOC}}


When attempting to connect to a TLS 1.2 or higher web service using SSIS 2012 or 2014, you might encounter this error:
==TLS 1.0/1.1 Security Vulnerabilities==
TLS 1.0 and 1.1 are no longer considered to be secure. They do not support modern cryptographic algorithms and are proven to be vulnerable to exploits. Most major technology companies have either already deprecated TLS 1.0 and 1.1


Error verifying service: The request was aborted: Could not create SSL/TLS secure channel.
In order to stay current with industry standards for privacy and security in our audits, we must close vulnerabilities like TLS 1.0 and 1.1 for our web services.


SSIS 2012 and 2014 are set to default to use TLS 1.0, this could cause an issue when connecting to a web service that requires TLS 1.2 or higher.
When attempting to connect to a TLS 1.2 enabled web service or higher using DQC for SSIS you may encounter this error:


Follow the instructions below to enforce TLS 1.2 in your environment.
<pre>
Error verifying service: The request was aborted: Could not create SSL/TLS secure channel.
</pre>


;1.
The reason being is that DQC for SSIS natively runs on TLS 1.0 while the web service requires TLS 1.2. In order to connect, a registry edit must be made for DQC for SSIS to use TLS 1.2 instead.
;For SQL 12
:Microsoft® SQL Server® 2012 SP3 Latest Cumulative Update
:https://www.microsoft.com/en-us/download/details.aspx?id=50733


;For SQL 14
:Microsoft® SQL Server® 2014 SP2 Latest Cumulative Update
:https://www.microsoft.com/en-us/download/details.aspx?id=53592
:If your machine already has the latest updates or is unable to install, continue to the next step.


==Enforcing TLS 1.2==
Follow the instructions below to enforce TLS 1.2 in your environment.


'''2.''' Add new entry as Client to registry as instructed here
<ol>
<li>Find the latest '''Microsoft Cumulative Update''' for the version of SQL Server being used.</li>


:https://docs.microsoft.com/en-us/windows-server/security/tls/tls-registry-settings#tls-12
If your machine already has the latest updates or is unable to install, continue to the next step.


:To enable the TLS 1.2 protocol, create an Enabled entry in either the Client or Server subkey as described in the following table. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 1.
<li>In the '''Registry Editor''', go to the directories mentioned below and add a '''Dword entry''' named <code>SchUseStrongCrypto</code> with a value of <code>1</code> for each directory.</li>


:If the TLS 1.2 entry is not found, skip to the next step.
*.NETFramework Directory


You can override the default behavior in following registry:


'''3.''' In the Registry Editor, go to the directories mentioned below and add a Dword entry name SchUseStrongCrypto with a value of 1 for each directory
<pre>
 
Key : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319
;SSIS 2012
 
:You can override the default behavior in following registry:
<pre>Key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319  
Value: SchUseStrongCrypto
Value: SchUseStrongCrypto
Type: REG_DWORD
Type: REG_DWORD
Data: 1</pre>
Data : 1
</pre>


:;And
And


<pre>Key: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v4.0.30319
<pre>
Key : HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v4.0.30319
Value: SchUseStrongCrypto
Value: SchUseStrongCrypto
Type: REG_DWORD
Type: REG_DWORD
Data: 1</pre>
Data : 1
 
</pre>
;SSIS 2014
:Configure for strong cryptography
:https://docs.microsoft.com/en-us/mem/configmgr/core/plan-design/security/enable-tls-1-2-client
 
 
For '''32-bit applications''' that are running '''on 32-bit OSs''' and for 64-bit applications that are running on 64-bit OSs, update the following subkey values:
<pre>Registry
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v2.0.50727]
      "SystemDefaultTlsVersions" = dword:00000001
      "SchUseStrongCrypto" = dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
      "SystemDefaultTlsVersions" = dword:00000001
      "SchUseStrongCrypto" = dword:00000001</pre>
 
:;Or
 
 
For '''32-bit applications''' that are running on '''64-bit OSs''', update the following subkey values:
<pre>Registry
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v2.0.50727]
      "SystemDefaultTlsVersions" = dword:00000001
      "SchUseStrongCrypto" = dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v4.0.30319]
      "SystemDefaultTlsVersions" = dword:00000001
      "SchUseStrongCrypto" = dword:00000001</pre>


'''Restart''' the machine, '''open''' up a DQC for SSIS project, and perform a '''Test Configuration''' to ensure you are connected successfully.


Restart the machine and open up your SSIS project and perform a Test Configuration to ensure you are connected successfully.
SSIS will now successfully connect to a TLS 1.2 web service.
</ol>


Add either of the 2 entries into the registry that apply to your environment.


SSIS will now successfully connect to TLS 1.2 web service.
==Native Integration==
We are currently working on natively integrating TLS 1.2 with DQC Components for SSIS and future releases with support for TLS 1.2 will be announced on [[SSIS:Data Quality Components]].




[[Category:FAQ]]
[[Category:FAQ]]
[[Category:SSIS:Data Quality Components]]
[[Category:SSIS:Data Quality Components]]

Revision as of 23:20, 8 July 2020

← SSIS:Data Quality Components


TLS 1.0/1.1 Security Vulnerabilities

TLS 1.0 and 1.1 are no longer considered to be secure. They do not support modern cryptographic algorithms and are proven to be vulnerable to exploits. Most major technology companies have either already deprecated TLS 1.0 and 1.1

In order to stay current with industry standards for privacy and security in our audits, we must close vulnerabilities like TLS 1.0 and 1.1 for our web services.

When attempting to connect to a TLS 1.2 enabled web service or higher using DQC for SSIS you may encounter this error: 

Error verifying service: The request was aborted: Could not create SSL/TLS secure channel.

The reason being is that DQC for SSIS natively runs on TLS 1.0 while the web service requires TLS 1.2. In order to connect, a registry edit must be made for DQC for SSIS to use TLS 1.2 instead.


Enforcing TLS 1.2

Follow the instructions below to enforce TLS 1.2 in your environment.

  1. Find the latest Microsoft Cumulative Update for the version of SQL Server being used.
    2. If your machine already has the latest updates or is unable to install, continue to the next step.
  2. In the Registry Editor, go to the directories mentioned below and add a Dword entry named SchUseStrongCrypto with a value of 1 for each directory.
    • .NETFramework Directory
    You can override the default behavior in following registry: 
    Key : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319
Value: SchUseStrongCrypto
Type: REG_DWORD
Data : 1

    And 

    Key : HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\.NETFramework\v4.0.30319
Value: SchUseStrongCrypto
Type: REG_DWORD
Data : 1

    Restart the machine, open up a DQC for SSIS project, and perform a Test Configuration to ensure you are connected successfully.

    SSIS will now successfully connect to a TLS 1.2 web service.


Native Integration

We are currently working on natively integrating TLS 1.2 with DQC Components for SSIS and future releases with support for TLS 1.2 will be announced on SSIS:Data Quality Components.

Retrieved from "http://wiki.melissadata.com/index.php?title=SSIS:Advisory:DQC_for_SSIS_%26_TLS_1.2&oldid=17197"