Forum Discussion
OData error 404 Not Found 414 URI Too Long using API Key
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/URI-too-long/m-p/121962
https://stackoverflow.com/questions/4247985/odata-url-length-limitations
2 Replies
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:
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.
Thanks for your response Liu, the problem is options 3 and 4 are not implemented in Power BI.
