Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
Azure Synapse Linked Service Error Connecting to Google BigQuery [HY000] (8701)
Christian Struik
0
Reputation points
In Azure Synapse Analytics we are trying to connect to Google Big Query. But when testing the connection the following error occurs:
We are using a Shared Host Integration Runtime (latest version) and configured the Connection as follows:
- Google Big Query service authentication, created a JSON Key File
- Placed the Key File on the SHIR server in the folder C:\GoogleBigdata\keyfile.json
- Used the right project and credentials
- Installed latest Simba ODBC drivers on the SHIR server (SimbaODBCDriverforGoogleBigQuery64_2.5.0.1001)
On the SHIR server also errors occurs, at the same time while testing the Linked Service, these are Perflib and PerfNet:
Any suggestion would be highly appreciated.
Azure Synapse Analytics
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 9%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKM%3C/text%3E%3C/svg%3E)
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator2023-02-20T19:21:21.2033333+00:00 Hi @Christian Struik ,
Welcome to Microsoft Q&A forum and thanks for reaching out here.
As per the error message seems like the connector is unable to make a call to the BigQuery API. There could be many reasons which could be related to configuration or driver related.
- Check if the key file path is correct.
- Check if SHIR process has permission on the file.
This may need a deeper investigation as it involves troubleshooting of SHIR logs as well as Simba driver logs. Hence, we recommend filing a support ticket for deeper investigation. In case if you don't have a support plan, please let me know.
Thank you
-
Christian Struik 0 Reputation points
2023-02-21T15:56:28.43+00:00 Hi @KranthiPakala-MSFT ,
The file path is correct, before this error, we tried placing the key file at a different disk (I: -disk) and uppon reading other blogs and Q&A's it was clear the file needs to be in folder on the C-drive, which it is.
The SHIR process has acces to the location and the file (Full Control)
Uppon testing with the Simba ODBC, the connection succeeded:
In advance I requested a new keyfile, just te be sure.
Kind regards, Christian
-
Christian Struik 0 Reputation points
2023-02-22T00:22:26.0066667+00:00 Hello @KranthiPakala-MSFT
The SHIR has access to the file (Full Control on the folder and file for the SHIR Service)
The path is correct, to be sure I requested to create a new keyfile.,
I tested it with the Simba ODBC driver a this was succesfull:
But still no result in Synapse, via the SHIR (expecting to use the installed Simba ODBC Driver)
The error remains the same, with PerfLib and PerfNet errors in de WIndows Event Viewer.
Kind regards, Christian
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-02-22T23:19:21.95+00:00 @Christian Struik Thanks for sharing additional details, it strange the driver is able to establish connection to the BigQuery endpoint, but it is not working when testing the connection from ADF linked service. This may need deeper investigation to figure out the root cause. Hence would recommend filing a support ticket. In case if you don't have a support plan, please let us know here.
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-02-28T00:33:26.33+00:00 @Christian Struik Following up to see if you have got a chance to file a support ticket for deeper investigation of this issue. If so, could you please share the SR# with us here so that we can track it internally. In case if you don't have a support, plan and are still blocked please let us know, so that we can help create a free support ticket for you.
-
Christian Struik 0 Reputation points
2023-03-09T15:05:00.1366667+00:00 Sorry for the late reply, due to priorities, this was put on hold till today.
We still have the same issue, I will try and create a support ticket for this.We were looking for a workaround, to use the REST linked service instead of the Google BigQuery, but I think this will not fit, because of the keyfile which is mandatory, correct?
The authentication has to be 'service authenciation' due to policy, so user authentication will not sufice.
-
KranthiPakala-MSFT 46,737 Reputation points Microsoft Employee Moderator
2023-03-15T17:51:57.27+00:00 @Christian Struik Thanks for the update. Please do share the support ticket number once it is filed so that we can track it internally.
Thank you
Sign in to comment
Sign in to answer