SSIS package and modern authentication

Question

We are currently migrated from Project Server 2010 to Project Online. Part of this migration, we want to replicate Project Online Reporting Data to a local SQL Server database in order to reuse SSRS reports already developed and facilitate the migration.

It is planned to follow Microsoft official procedure to replicate data using the OData protocol and SSIS package.

https://docs.microsoft.com/en-us/previous-versions/office/project-odata/dn720853(v=office.15)

While testing the procedure, we were unable to open a connection to our PWA using the OData source connection manager from Visual Studio – SQL Server Data Tools.

We received the following error message:

“The remote server returned an error: (401) Unauthorized. (System)”

After troubleshooting, we found that the connection was blocked due to a parameter in our tenant. The parameter “SharePoint Admin Center > Device Access > Control access from apps that don’t use modern authentication” is set to “Block”.

Changing this parameter, we are able to access our PWA without any issues.

However and for security reason, we don’t want to change this parameter and allow connections using non-modern authentication. Can you please propose another workaround?

Thank you in advance

donnie027 · Answer

Did you ever get this figured out?&nbsp; I'm currently trying to set up a connection between SSIS ODataSource and our SQL server, but our admin is wanting to find a solution with modern authentication.&nbsp;&nbsp;Axel_Lemaitre1&nbsp;

tarunraghu55 · Answer

hi&nbsp;donnie027&nbsp;, According to this article&nbsp;https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/odata-connection-manager?view=sql-server-ver15,&nbsp;ODataSource does not support modern authentication. I would suggest using Microsoft Flow in SPO to export Sharepoint List to Excel (shared path) and then import the Excel into SSIS.&nbsp;

saidulu333 · Answer

https://techcommunity.microsoft.com/t5/user/viewprofilepage/user-id/744323
Hi tarunraghu55 ,
Currently we are connecting basic authentication and It's need to change share point connection from basic to modern authentication, You said OData Source doesn't support and suggest as as export SharePoint list to excel.

Could you please provide the details how it works and how is connect that SP list to excel.

Thanks,
Sai

tarunraghu55 · Answer

hiSAIDULU333&nbsp;,&nbsp;&nbsp;Below is the step by step process of how we did it (Read full process before implementing)&nbsp;1) Create a Microsoft Flow in Sharepoint Online Site&nbsp;i) recurrence step is to run this flow every 10 minutesii) get items step is to get data from Sharepoint listiii) With the output of get items we create a CSV tableiv) We delete the CSV file created in the Sharepoint library (Create a dummy file in the library before doing this step , every Sharepoint site should have a Sharepoint Library)&nbsp;v) we create the CSV file in the Sharepoint library using the CSV table step&nbsp;we are doing delete and create as there is no overwrite option in Microsoft Flow&nbsp;2) Setup a VM , in the VM connect to the Sharepoint Online site using a sys account (provide access to sys account as needed)&nbsp;3) Setup One Drive Sync in the VM for the sys account , your One Drive Folder will behave as a shared path which will have the CSV file created by Microsoft Flow (it will be in sync with the Sharepoint site)&nbsp;4) In your SSIS solution we should use Flat File as a source and refer to the CSV file present in One Drive Sharepoint path&nbsp;&nbsp;This process is slightly complicated with a VM setup and Microsoft Flow but I was able to implement this successfully.&nbsp;&nbsp;Let me know if this satisfies your requirement. We can discuss further if any issues.&nbsp;

chris_schulz · Answer

tarunraghu55&nbsp;&nbsp;Hi this solution seems to be for a SharePoint list, not project online data and mentioned in the original post. Can you explain how to retrieve *all* project online data using modern authentication? The article here is outdated:&nbsp;Replicate Project Online Reporting Data to a SQL Server database using the OData protocol | Microsoft Learn

Forum Discussion

SSIS package and modern authentication

5 Replies

Resources