Error during data pull from BLOB using SSIS with Azure Feature Pack on Azure VM.

Question

Hello,
I have a problem with pulling data from 3rd party Azure BLOB Storage (located in East US 2) using an SSIS package executed on our production Azure VM (in private network) located in West Europe.
(Interesting fact - everything works fine on my local machine when I'm trying to use the same SSIS package and BLOB)

Storage Account configuration is visible below:

Additionally - in Storage Account IAM I see that, we are assigned to the roles below:

VM Configuration:

Looks like, that we don't have any firewall rules that can block web traffic. I can successfully test the connection between our production VM and this BLOB:

Moreover:
a) I am able to download necessary data using Azure Storage Explorer from this BLOB directly on the problematic VM.
b) I am able to successfully test connection in my Azure Storage Connection Manager configured in SSIS package:

And below is what is visible in CMD after testing connection in SSIS:

When I'm trying to download the same file (that I successfully downloaded previously on VM using Azure Storage Explorer) via SSIS "Azure Blob Download Task" in CMD I see only:

And SSIS execution finished with error " Download task has stopped with exception: The underlying connection was closed: An unexpected error occurred on a send."

I have the same error(but more detailed) when I'm trying to connect to this Azure Storage from Microsoft SQL Server Management Studio:

I thought, that it can be caused by TLS 1.2 version configured on the 3rd party Storage Account, but I tested this case also for my own SA (that is located in the same region as production VM) with configured TLS 1.0 - result was the same.

Below is a list of things that I made before preparation SSIS package to download data from BLOB.

1. I installed both 32-bit JRE 8 and 64-bit JRE 8 (Java Runtime Environment - https://www.java.com/en/download/manual.jsp) on VM and next made sure that in the Registry Editor, 

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment{instaled java version}\JavaHome 

has set the proper path in the "Data" column, to just installed JRE - like below:

 

2. I created a new JAVA_HOME Environment Variable (in the section for System Variable)  and set her value as a path to the folder with the installed previously 64-bit JRE 8

 

3. I installed Azure Feature Pack for SQL Server 2016 (this is my SQL version)

https://www.microsoft.com/en-us/download/details.aspx?id=49492

 

4. To enable the ability to successfully pull data from Azure Data Lake with TLS version 1.2, I created two new REG_DWORD values named SchUseStrongCrypto with data 1 under the following two registry keys:

 

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft.NETFramework\v4.0.30319

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft.NETFramework\v4.0.30319

 

(More details in the section "Use TLS 1.2" -  https://learn.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis?view=sql-server-ver16#use-tls-12) 

 

At this moment, I have installed .Net versions visible below:

Does anyone have any idea how I can fix it?

Thank you

Answer 1

@Szymon Dąbrowski I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Issue:

I have a problem with pulling data from 3rd party Azure BLOB Storage (located in East US 2) using an SSIS package executed on our production Azure VM (in private network) located in West Europe.
(Interesting fact - everything works fine on my local machine when I'm trying to use the same SSIS package and BLOB)

Additionally - in Storage Account IAM I see that, we are assigned to the roles below:

Storage Blob Data Contributor

Storage Blob Data Reader

Looks like, that we don't have any firewall rules that can block web traffic. I can successfully test the connection between our production VM and this BLOB using Test Net Connection on TCP port 443.

Moreover:

a) I am able to download necessary data using Azure Storage Explorer from this BLOB directly on the problematic VM.
b) I am able to successfully test connection in my Azure Storage Connection Manager configured in SSIS package:

When I'm trying to download the same file (that I successfully downloaded previously on VM using Azure Storage Explorer) via SSIS "Azure Blob Download Task" in CMD I see only: Syn_Sent

Solution:

I thought, that it can be caused by TLS 1.2 version configured on the 3rd party Storage Account, but I tested this case also for my own SA (that is located in the same region as production VM) with configured TLS 1.0 - result was the same.

Below is a list of things that I made before preparation SSIS package to download data from BLOB.

1. I installed both 32-bit JRE 8 and 64-bit JRE 8 (Java Runtime Environment - https://www.java.com/en/download/manual.jsp) on VM and next made sure that in the Registry Editor, 

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment{instaled java version}\JavaHome 

has set the proper path in the "Data" column, to just installed JRE - like below:

 

1. I created a new JAVA_HOME Environment Variable (in the section for System Variable)  and set her value as a path to the folder with the installed previously 64-bit JRE 8

 

1. I installed Azure Feature Pack for SQL Server 2016 (this is my SQL version)

https://www.microsoft.com/en-us/download/details.aspx?id=49492

 

1. To enable the ability to successfully pull data from Azure Data Lake with TLS version 1.2, I created two new REG_DWORD values named SchUseStrongCrypto with data 1 under the following two registry keys:

 

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft.NETFramework\v4.0.30319

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft.NETFramework\v4.0.30319

 

(More details in the section "Use TLS 1.2" -  https://learn.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis?view=sql-server-ver16#use-tls-12) 

 

At this moment, I have installed .Net versions visible below:

Additionally, if you need to use TLS 1.2 you have to configure two registry keys on your VM:

Copy

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319

and add new values named SystemDefaultTlsVersions with DWORD value=1
like below:

More details in the link below:
https://support.avigilon.com/s/article/How-to-Enable-TLS-1-2-as-Windows-Default?language=en_US
Print screen from this page to back up purpose:

Additionally, if you need to use TLS 1.2 you have to configure two registry keys on your VM:

Copy

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319

and add new values named SystemDefaultTlsVersions with DWORD value=1
like below:

More details in the link below:
https://support.avigilon.com/s/article/How-to-Enable-TLS-1-2-as-Windows-Default?language=en_US
Print screen from this page to back up purpose:

Please let us know if you have any more questions and we will be glad to assist you further. Thank you!

Remember:

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.