Webclient.downloadfile fails when trying to download SharePoint Online file run from an SSIS Script (SQL 2016)

Question

Recently we started to experience problems with an SSIS script that evaluates files on an SharePoint Online site and download files Onprem. The Script is run within SSIS on an SQL2016 Server. We are quite certain it is in some way related to TLS1.2. We have with MS support run through SSL and TLS settings on the server and the corresponding .NET frameworks. But we can´t get it to run in the script component. But when testing to execute package in client Visual Studoi develop environment it runs as expected. But not as a deployed package in the SQL Server machine.115060-consoleapponlinedownload.txt

The attached script is a stripdown to bare essentials in the code and when i created a .Net Console App installed it on the Server it works. With the same code.

Any ideas.
Have tried an Odata connection in a SSIS package to download metadata about files present in the sharepoint site doc lib and save it to a CSV file. That works as well on the server.

Answer 1

Hi @Boghossian, John ,

For TLS1.2 related issues with SharePoint Online , you could refer to this article to resolve it: https://learn.microsoft.com/en-us/sharepoint/troubleshoot/administration/authentication-errors-tls12-support

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you. I have found that article and ensured that those Ciphers are enabled and in that specific order. In addition to them several other ciphers are enabled.

The annoying thing is that it works if i create a c# console program with the barebone code. But not as a SSIS Script task. It also works to access SPO via oData Connector in an SSIS package to extract metadata from a SP Doc List. So there is something that is acting up with the script task that i can´t figure out. And i have an ongoing case with MS Support and they are trying to figure it out. I just thought that maybe someone had a clue as to what really is going on.

Answer 3

Hi @Boghossian, John ,

Have you try to enable allow access for Apps that don't use modern authentication under Sharepoint online admin center-> access control?

Answer 4

Thank you for a good tip. But it was on and allow access for non modern auth access