CDS OData integration. Invalid credentials

[Anonymous]

Good day!

We have accounting system (1C https://1c.ru/eng/title.htm). It have posibility of OData integration (on web server Apachi). In test enviroment on PowerApps Plan2(Trial version), CDS, we try to get data from OData url throught local Gateway (we have tested it. It works for Sharepoint or files with our domain credential). After populate url and credential on next, we get error(it is translate from russian): Invalid credentials. (session ID: 929cc5aa-f99a-4688-bfb0-d3f92ba33996). Then we try do the same for odata.org example https://services.odata.org/V4/TripPinServiceRW without authorization, it work excellent.

Our data:

URL: http://localIP/buh_sharepoint/odata/standard.odata/Catalog_OS (Yes, it is works wrought IE on gateway server, after credential step).

Credential method: Basic or Windows

OData response: (1C public this like Odata format)

<?xml version="1.0" encoding="UTF-8"?><feed xmlns="http://www.w3.org/2005/Atom"
xmlns:at="http://purl.org/atompub/tombstones/1.0"
xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
xml:base="http://InternalIP/buh_sharepoint/odata/standard.odata/">
<entry>
<id>http://internal_ip/buh_sharepoint/odata/standard.odata/Catalog_OS(guid'eef7d89d-c3a9-11e4-80bd-2c44fd8868ab')</id>
<category term="StandardODATA.Catalog_OS"
scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<title type="text"/>
<updated>2018-10-16T08:42:11</updated>
<author/>
<summary/>
<link rel="edit"
href="Catalog_OS(guid'eef7d89d-c3a9-11e4-80bd-2c44fd8868ab')"
title="edit-link"/>
<content type="application/xml"><m:properties xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<d:Ref_Key>eef7d89d-c3a9-11e4-80bd-2c44fd8868ab</d:Ref_Key>
<d:DataVersion>AAAAAAAAB9E=</d:DataVersion>
<d:DeletionMark>false</d:DeletionMark>
<d:Code>ОФ001642 </d:Code>
<d:Description>Принтер матричный Epson L</d:Description>
<d:Name>Принтер матричный Epson LX-350 </d:Name>
<d:MOL>Ли Андрей Леонидович0000000000</d:MOL>
<d:CodeDoc>ОФ001642 </d:CodeDoc>
<d:INV_Number>ОФ001642 </d:INV_Number>
<d:Price>36089.29</d:Price>
<d:Date_Uchet>0001-01-01T00:00:00</d:Date_Uchet>
</m:properties>
</content>
</entry>
.... next 2200 pices of entry
</feed>

How can I integrate PowerApps or Azure with my accounting system throught local gateway.

P.S. Accounting system have MS SQL DB, and yes I can connect to it, throught local gateway, but on load data step I have other error - "a" it is not a function 😞

[xinguo]

Hello agemlex,

Could you take the detail trace from your local gateway when you repro this? If you don't want to share the trace in public, you can send it to me at xinguo@microsoft.com.

Thanks

Xin Guo

[Anonymous]

Hello xinguo!

I think, this is it. It is for Basic auth method. For Windows auth method the same error. This login and password work for Internet Explorer. 

In russian: 

Microsoft.Data.Mashup.MashupCredentialException (0x80004005): Учетные данные, указанные для источника OData, недействительны.

Translated:

Microsoft.Data.Mashup.MashupCredentialException (0x80004005): The credentials specified for the OData source are invalid.

GatewayInfo20181017.000000001:

DM.EnterpriseGateway Information: 0 : 2018-10-18T02:26:24.0587391Z DM.EnterpriseGateway	f3a44444-126f-4526-9cb0-7dd2f8b6223e	a4eaf601-2c13-4f93-aa08-ba8fb50a7dae	MGPP	00000000-0000-0000-0000-000000000000	A08BF3B6 [DM.GatewayCore] Deserialized TestGatewayConnectionRequest, executing
DM.EnterpriseGateway Information: 0 : 2018-10-18T02:26:35.9444729Z DM.EnterpriseGateway	9e161785-f41e-4bd0-9c6a-7b8f9ca508b1	97cc86f2-c74a-4807-ad5e-6837349934bc	MGPP	00000000-0000-0000-0000-000000000000	E8162E5D [DM.Pipeline.Common] Pool cleaner connections removed: 0, count: 0, buckets: 0
...
DM.GatewayCore] Deserialized OpenConnectionRequest, executing
DM.EnterpriseGateway Warning: 0 : 2018-10-18T02:32:15.4343982Z DM.EnterpriseGateway	26eb4751-14da-4d3a-b3e7-e06f463a4b08	00000000-0000-0000-0000-000000000000	MGGC	00000000-0000-0000-0000-000000000000	2E3B8309 [DM.Pipeline.Common] Failed to read GatewayCore property: EnableFastCombine, Ex: Ссылка на объект не указывает на экземпляр объекта.
DM.EnterpriseGateway Information: 0 : 2018-10-18T02:32:15.4500250Z DM.EnterpriseGateway	6453a0cc-3b13-4685-b448-18e96950d7ac	00000000-0000-0000-0000-000000000000	MGCC	00000000-0000-0000-0000-000000000000	013F65DB [DM.GatewayCore] Db pool opening raw database connection to [Microsoft.Data.Mashup: <pi>Microsoft.Data.Mashup:TRUNCATED:[Anonymous] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2018-10-18T02:32:16.2625501Z DM.EnterpriseGateway	2fc9ad0f-a430-4595-aa4b-f0628811e1cf	00000000-0000-0000-0000-000000000000	MGCC	00000000-0000-0000-0000-000000000000	013F65DB [DM.GatewayCore] Db pool opening raw database connection to [Microsoft.Data.Mashup: <pi>Microsoft.Data.Mashup:TRUNCATED:[Anonymous] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2018-10-18T02:32:16.2625501Z DM.EnterpriseGateway	b721ddad-0d86-4c44-b21f-f8fa638c8ade	00000000-0000-0000-0000-000000000000	MGCC	00000000-0000-0000-0000-000000000000	013F65DB [DM.GatewayCore] Db pool opening raw database connection to [Microsoft.Data.Mashup: <pi>Microsoft.Data.Mashup:TRUNCATED:[Anonymous] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2018-10-18T02:32:16.2625501Z DM.EnterpriseGateway	2b82b9dc-88de-480e-8d4e-1d323834b7f3	00000000-0000-0000-0000-000000000000	MGCC	00000000-0000-0000-0000-000000000000	013F65DB [DM.GatewayCore] Db pool opening raw database connection to [Microsoft.Data.Mashup: <pi>Microsoft.Data.Mashup:TRUNCATED:[Anonymous] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2018-10-18T02:32:16.4969366Z DM.EnterpriseGateway	9dc1c375-0c76-497d-be45-38722edac990	00000000-0000-0000-0000-000000000000	MGPP	00000000-0000-0000-0000-000000000000	21D28673 [DM.GatewayCore] Deserialized ExecuteQueryRequest, executing
DM.EnterpriseGateway Warning: 0 : 2018-10-18T02:32:16.8563209Z DM.EnterpriseGateway	aeb393b5-65f9-480c-a0be-405ff736d49c	00000000-0000-0000-0000-000000000000	MGEQ	00000000-0000-0000-0000-000000000000	AF2C88C8 [DM.GatewayCore] Swallowing exception during Ado query execution attempt and triggering a retry: Microsoft.Data.Mashup.MashupCredentialException (0x80004005): Учетные данные, указанные для источника OData, недействительны. (Источник в http://InternalIP/buh_sharepoint/odata/standard.odata/Catalog_OS.)
   в Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass4`1.<StartEvaluationAndGetResultSource>b__2()
   в Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
   в Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
   в Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
   в Microsoft.PowerBI.DataMovement.Pipeline.MashupCommon.MashupUtils.ExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest)
   в Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_2.<<ExecuteAdoQuery>b__1>d.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- Конец трассировка стека из предыдущего расположения, где возникло исключение ---
   в System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   в System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   в Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d.MoveNext()
DM.EnterpriseGateway Information: 0 : 2018-10-18T02:32:16.8563209Z DM.EnterpriseGateway	6cafd767-c267-4c14-842e-7d96986669dc	00000000-0000-0000-0000-000000000000	MGCC	00000000-0000-0000-0000-000000000000	013F65DB [DM.GatewayCore] Db pool opening raw database connection to [Microsoft.Data.Mashup: <pi>Microsoft.Data.Mashup:TRUNCATED:[Anonymous] Encrypted Credential information omitted</pi>]
DM.EnterpriseGateway Information: 0 : 2018-10-18T02:32:36.0288264Z DM.EnterpriseGateway	9e161785-f41e-4bd0-9c6a-7b8f9ca508b1	97cc86f2-c74a-4807-ad5e-6837349934bc	MGPP	00000000-0000-0000-0000-000000000000	E8162E5D [DM.Pipeline.Common] Pool cleaner connections removed: 0, count: 0, buckets: 0
...

[Anonymous]

I have tryed to do a http get request for same url. But in this trying, I do request throght external IP without on-premise gate.

Authorization Basic: {user: ***** password: ****}

From external PC, Internet Explorer this url works correctly, but throught user connection (configured from powerapps) I get an error:

{
"error": {
"code": 500,
"message": "Expression value is invalid. The template field is required.",
"source": "europe-001.azure-apim.net",
"path": "choose[2]\\when[1]",
"clientRequestId": "bc0ed7e4-e84f-4386-847c-9e42-c303bedd"
}
}

Maybe in Odata connector, the error the same?

[Anonymous]

One more experement. I have tryed do custom connector by http get request for odata url https://services.odata.org/V4/TripPinServiceRW without authorization at all.

Response:
{
"statusCode": 404,
"message": "Resource not found"
}

What is it? Why not found.

I have checked:
1. Do request from URL - OK
2. Do response structure from json response from IE - OK
3. Authorization, None - OK
4. Have I saved connector after connection creating? Yes - OK

[Anonymous]

In previous post, I do incorrect connection. This is why I get the error.

After correction, for OData source (without authorization I get the same error, like in my accounting system connection):

{
"error": {
"code": 500,
"message": "Expression value is invalid. The template field is required.",
"source": "europe-001.azure-apim.net",
"path": "choose[2]\\when[1]",
"clientRequestId": "72b11545-548e-4587-8ba8-2860-152495cd"
}
}

Please, can you to try do custom connector for url https://services.odata.org/V4/TripPinServiceRW .

It could be to reveal the error nature (I think I do incorrect connector).

[xinguo]

Looks like this issue is different from the gateway issue.

Could you please give us detail repro steps on this OData issue?

[Anonymous]

[Anonymous]

OData XML response:

{
"odata.metadata": "http://192.168.*.*/buh_sharepoint/odata/standard.odata/$metadata#Catalog_OS",
"value": [{
"Ref_Key": "eef7d89d-c3a9-11e4-80bd-2c44fd8868ab",
"DataVersion": "AAAAAAAAF3E=",
"DeletionMark": false,
"Code": "ОФ001642 ",
"Description": "Принтер матричный Epson L",
"Name": "Принтер матричный Epson LX-350 ",
"MOL": "Ли Андрей Леонидович0000000000",
"CodeDoc": "ОФ001642 ",
"INV_Number": "ОФ001642 ",
"Price": 36089.29,
"Date_Uchet": "0001-01-01T00:00:00"
}
.... next 2200 pices of entry 
]
}

[xinguo]

Thanks agemlex. Could you create another thread for this issue:

{
"error": {
"code": 500,
"message": "Expression value is invalid. The template field is required.",
"source": "europe-001.azure-apim.net",
"path": "choose[2]\\when[1]",
"clientRequestId": "72b11545-548e-4587-8ba8-2860-152495cd"
}
}

It looks like new issue from the gateway one. Could you also add the detail repo steps for this one at the new thread?