SSIS package and modern authentication

%3CLINGO-SUB%20id%3D%22lingo-sub-271891%22%20slang%3D%22en-US%22%3ESSIS%20package%20and%20modern%20authentication%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-271891%22%20slang%3D%22en-US%22%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EWe%20are%20currently%20migrated%20from%20Project%20Server%202010%20to%20Project%20Online.%20Part%20of%20this%20migration%2C%20we%20want%20to%20replicate%20Project%20Online%20Reporting%20Data%20to%20a%20local%20SQL%20Server%20database%20in%20order%20to%20reuse%20SSRS%20reports%20already%20developed%20and%20facilitate%20the%20migration.%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EIt%20is%20planned%20to%20follow%20Microsoft%20official%20procedure%20to%20replicate%20data%20using%20the%20OData%20protocol%20and%20SSIS%20package.%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fproject-odata%2Fdn720853(v%3Doffice.15)%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CFONT%20color%3D%22%230000ff%22%20face%3D%22Calibri%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Foffice%2Fproject-odata%2Fdn720853(v%3Doffice.15)%3C%2FFONT%3E%3C%2FA%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EWhile%20testing%20the%20procedure%2C%20we%20were%20unable%20to%20open%20a%20connection%20to%20our%20PWA%20using%20the%20OData%20source%20connection%20manager%20from%20Visual%20Studio%20%E2%80%93%20SQL%20Server%20Data%20Tools.%20%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EWe%20received%20the%20following%20error%20message%3A%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3E%E2%80%9CThe%20remote%20server%20returned%20an%20error%3A%20(401)%20Unauthorized.%20(System)%E2%80%9D%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EAfter%20troubleshooting%2C%20we%20found%20that%20the%20connection%20was%20blocked%20due%20to%20a%20parameter%20in%20our%20tenant.%20The%20parameter%20%E2%80%9CSharePoint%20Admin%20Center%20%26gt%3B%20Device%20Access%20%26gt%3B%20Control%20access%20from%20apps%20that%20don%E2%80%99t%20use%20modern%20authentication%E2%80%9D%20is%20set%20to%20%E2%80%9CBlock%E2%80%9D.%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EChanging%20this%20parameter%2C%20we%20are%20able%20to%20access%20our%20PWA%20without%20any%20issues.%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EHowever%20and%20for%20security%20reason%2C%20we%20don%E2%80%99t%20want%20to%20change%20this%20parameter%20and%20allow%20connections%20using%20non-modern%20authentication.%20Can%20you%20please%20propose%20another%20workaround%3F%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3CP%3E%3CI%3E%3CFONT%20color%3D%22%23000000%22%20face%3D%22Calibri%22%3EThank%20you%20in%20advance%3C%2FFONT%3E%3C%2FI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1470797%22%20slang%3D%22en-US%22%3ERe%3A%20SSIS%20package%20and%20modern%20authentication%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1470797%22%20slang%3D%22en-US%22%3E%3CP%3EDid%20you%20ever%20get%20this%20figured%20out%3F%26nbsp%3B%20I'm%20currently%20trying%20to%20set%20up%20a%20connection%20between%20SSIS%20ODataSource%20and%20our%20SQL%20server%2C%20but%20our%20admin%20is%20wanting%20to%20find%20a%20solution%20with%20modern%20authentication.%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F228443%22%20target%3D%22_blank%22%3E%40Axel%20Lemaitre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

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

2 Replies

Did you ever get this figured out?  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.  @Axel Lemaitre 

hi @donnie027 , According to this article 

https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/odata-connection-manage...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.