Share via

OData source connection manager to PWA (Project Web App) - Project Online URL fails with "(401) Unauthorized"

  • Article

Over this blog, I would like to discuss about one of the interesting issues that we had worked upon recently. Here we are having a SSIS package that uses the OData Source Connection manager to connect to a PWA (Project Web App) – Project Online URL to pull the data and write back to a SQL Server table. This SSIS package was scheduled to run under a SQL Agent Job and while executing the SQL Agent Job, it failed with the following error messages,

 

Error message from the SSIS package execution report:

 

While testing the connection for the OData connection manager of the same package from Visual Studio -SQL Server Data Tools (SSDT), we received the following error message,

 

===================================
Test connection failed
===================================
The remote server returned an error: (401) Unauthorized. (System)
------------------------------
Program Location:
at System.Net.HttpWebRequest.GetResponse()
at Microsoft.SqlServer.IntegrationServices.OData.UI.ODataConnectionManagerForm.TestConnectionMiddle(Object callback)
------------------------------

 

As a part of our troubleshooting activity, we had collected a fiddler traces while reproducing the issue and we were able to see the following.

 

 

After some internal research, we were able to identify the cause of the issue as below.

 

Cause:

Most current Office mobile and desktop applications use modern authentication (which is an implementation of OAuth2), however there are third-party apps, older Office apps & Visual studio applications (SSDT-SSIS) that uses other authentication methods like basic authentication and forms based authentication. Most of the times ADFS claims rules are setup to block non-modern authentication protocols which blocks all access to O365 except browser-based applications.

This is controlled by the following settings.
SharePoint Admin Center  -> Device access -> Control access from apps that don’t use modern authentication -> Block

 

Resolution:

We need to set the control access to be allowed for apps that don’t use modern authentication.
SharePoint Admin Center  -> Device access -> Control access from apps that don’t use modern authentication -> Allow

Post this change, we were able to access the PWA – Project Online URL without any issues.

NOTE: There could be multiple other reasons for the “(401) Unauthorized” error, in this blog, we have tried to address one such reason for this error to be thrown.

 

Additional References:

1. OData Connection Manager: https://msdn.microsoft.com/en-us/library/dn584129(v=sql.110).aspx
2. Using the SSIS OData Source Connector With SharePoint Online Authentication: https://whitepages.unlimitedviz.com/2014/03/using-the-odata-source-connector-with-sharepoint-online-authentication/
3. Control access based on network location or app: https://support.office.com/en-us/article/Control-access-based-on-network-location-or-app-59b83701-cefd-4bf8-b4d1-d4659b60da08
4. Applications and browsers that use conditional access rules in Azure Active Directory: https://docs.microsoft.com/en-us/azure/active-directory/active-directory-conditional-access-supported-apps
5. Block apps that do not use modern authentication (ADAL): https://docs.microsoft.com/en-us/intune-classic/deploy-use/block-apps-with-no-modern-authentication

 

Author:        Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft

Reviewer:    Sarath Babu Chidipothu – Support Escalation Engineer, SQL Server BI Developer team, Microsoft