ODataQuery Error in SSIS and Data Factory but not in PBI or Excel



I am not able to connect a data source using OData with SSIS or Data Factory. At the same origin I can connect both excel and PBI. The source is a link to a query that I have published in a Blackbaud CRM https://xxxxxxx.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=4xxxxs&AdHocQueryID=f5c3d3ac-0...


PBI connection:

Origen = OData.Feed("https://xxxxxxxx.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=xxxxxs&AdHocQueryID=f5c3d3ac-...", null, [Implementation="2.0"])
in Origen


The credential is windows authentication usign altenate credentials


Excel connection:

Data Source=https://xxxxxxxxs.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=xxxxxs&AdHocQueryID=f5c3d3ac... to Include=*;Max Received Message Size=4398046511104;Integrated Security=Basic;User ID=XXX\xxxxxxxx;Persist Security Info=false;Base Url=https://xxxxxxxs.sky.blackbaud.com/xxxxxs/ODataQuery.ashx?databasename=xxxxxs&AdHocQueryID=f5c3d3ac-...


Data Factory Connection:

In Data Factory I've tried with windows and Basic Authentication and always the same error



Failed to create OData connection to RequestUrl Fail to get metadata of odata service, please check if service url and credential is correct. Activity ID: a29ac5c2-def7-40e1-b90a-ec5985cd8083.


With SSIS I've tried basic authentication and windows aut (starting SSDT with the same account I connect via Excel) and I always get the same error message 



No se pudo comprobar la conexión


Error en el servidor remoto: (401) No autorizado. (System)

Ubicación del programa:

en System.Net.HttpWebRequest.GetResponse()
en Microsoft.SqlServer.IntegrationServices.OData.UI.ODataConnectionManagerForm.TestConnectionMiddle(Object callback)


Thanks a lot!!!

0 Replies