OData error 404 Not Found 414 URI Too Long using API Key

[Den123]

Hi everyone,

I am encountering an error when it comes to load paginated data with a large amount of rows.

Error 1:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error:[Data Source.NotFound] OData: request failed (404): The remote server returned an error:(404) Not Found. (Not Found). '.

Error 2:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] OData: Request failed: The remote server returned an error: (414) URI Too Long. (URI Too Long). '.

The above errors happen because the url string is too long.

I am using the following connection string:

= OData.Feed("source_url", null, [ApiKeyName="api_key"])

And because of the pagination: `@odata.nextLink` (1000 rows per request) - it keeps adding &api_key={long_api_key} to every url. As a result the final query looks like that "source_url?api_key={long_api_key}&api_key={long_api_key}&api_key={long_api_key}&api_key={long_api_key}&api_key={long_api_key}&api_key={long_api_key}&api_key={long_api_key}&api_key={long_api_key}&api_key={long_api_key}".

Are there any solutions to fix the issue? The following options I see:

1. Send a POST request instead (not sure if it's possible with odata),

2. Check whether the url already contains "api_key" query string, and not to attach (but replace) it if it does.

3. Allow to pass api key in header instead (eg. = OData.Feed("source_url", null, [ApiKeyHeaderName="X-API-KEY"]) (this would be the best option so far).

4. Other OAuth authentication methods (eg. Bearer token).

As a workaround I am currently use: = OData.Feed("source_url", null, [Headers = [#"X-API-KEY" = "{long_api_key}"]]), which solves the issue. But it is not secure because if I save the report to file, it would contain the api key, so everyone can have an access to data. Another workaround would be to remove "api_key" query parameter in the auth handler when processing the request, so the @odata.nextLink would not contain it.

I am not happy with how Auth works in PowerBI :-(.

Any solution for this?

Related issues: 

https://community.fabric.microsoft.com/t5/Desktop/OData-error-with-Dynamics-CRM-Online-Data-Source-N...

https://community.fabric.microsoft.com/t5/Desktop/URI-too-long/m-p/121962

https://stackoverflow.com/questions/4247985/odata-url-length-limitations 

https://github.com/OData/WebApi/issues/2190 

[Anonymous]

Hi  @Den123 ,

In the methods you’ve found, I recommend using the third and fourth ones.

For the ‘api_key,’ use headers for secure transmission.

Web.Contents - PowerQuery M | Microsoft Learn

“If your ‘api_key’ parameter is a constant value, you can also use a token to include it. Every time you need to retrieve the ‘api_key’, this dynamically generated token will be automatically triggered, providing enhanced security. Alternatively, you can consider using environment variables or secure vault services (if available) to store and reference the ‘api_key’.”

Refer to:

Call Power BI Rest API with Dynamic Access Token From Power BI Desktop |Power BI Rest API | Part Two...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Den123]

Thanks for your response Liu, the problem is options 3 and 4 are not implemented in Power BI.