How to set BigQuery Proxy in PowerBI without admin privileges on machine

[mehulparmariitr]

I am using Google Bigquery connector on PowerBI to connect to a GCP project, but bigquery APIs are only accessible if we use a proxy to enable private access.

Is there a way to set proxy in Google Bigquery connector. I can change the host file in machine and but it is not allowed as I am not admin. So looking for ways to set proxy in powerBI directly. I tried the env variable(not sure if valid) but its not working -

# Override using an environment variable
CLOUDSDK_API_ENDPOINT_OVERRIDES_BIGQUERY=storage-vialink1.p.googleapis.com

GCloud also I cant install on the machine. What are the ways I can go ahead?

[ms4446]

Unfortunately, the Google BigQuery connector for Power BI does not currently have a built-in option to set up a proxy. However, there are a few alternative approaches you can try:

• Web Proxy Service: Setting up a web proxy service like Cloud Proxy or Squid Proxy on a server you have access to is a viable option. However, configuring Power BI to use this proxy for a specific data source like BigQuery is not straightforward. Power BI's "Global Proxy" settings are generally for web content and might not apply to all data connectors.

• Browser Extension: Browser extensions for proxy settings, such as Proxy SwitchyOmega, are typically effective for web browsers and may not influence how the Power BI desktop application connects to services like BigQuery. This method is more applicable to web-based applications and is unlikely to be effective for Power BI.

• Alternate Authentication Method: Exploring alternative authentication methods like OAuth is useful, but this approach addresses authentication rather than network routing issues. OAuth simplifies the authentication process with BigQuery but does not inherently solve proxy-related connectivity problems.

• Alternative Tools: Using tools like the Google Cloud SDK or bq command-line tool is an alternative, but these require installation, which might not be feasible without admin privileges. Additionally, these tools serve different purposes compared to Power BI and may not be suitable for all use cases.

• Environment Variable: The environment variable CLOUDSDK_API_ENDPOINT_OVERRIDES_BIGQUERY is specific to the Google Cloud SDK and does not apply to the Power BI connector. It is used for setting API endpoint overrides within the SDK.

[mehulparmariitr]

okay so I think there is no solution other then changing the host file, right @ms4446 
Is anything possible if we use Simba ODBC driver instead of Google Bigquery connector?

[ms4446]

Using the Simba ODBC driver for BigQuery might offer more flexibility, especially in terms of network configurations like proxy settings. Here's how it could potentially help in your situation:

1. Custom Proxy Settings: The Simba ODBC driver typically allows for more detailed configuration options compared to the native Power BI connector. This includes the ability to specify proxy settings directly in the driver configuration. You can set the proxy server, port, and potentially other relevant details like username and password for authentication.

2. DSN Configuration: When setting up the Simba ODBC driver, you create a Data Source Name (DSN) which includes all the necessary connection details to BigQuery. Within the DSN configuration, you can specify your proxy settings. This setup is done outside of Power BI, giving you a chance to establish the connection parameters before importing data into Power BI.

3. Better Integration with Corporate Networks: In corporate environments where network traffic must go through a proxy, configuring the ODBC driver with the correct proxy settings can ensure that your queries to BigQuery are routed correctly through your organization's network.

4. Workaround for Administrative Restrictions: If you're unable to change system-wide network settings due to lack of administrative privileges, configuring the proxy settings directly within the ODBC driver might be a viable workaround. This approach doesn't require admin rights to the entire system, just to the ODBC Data Source Administrator tool.

5. Compatibility with Power BI: Once the DSN is configured with the correct proxy settings, you can use it in Power BI by selecting the ODBC option when connecting to data sources. Power BI should be able to use the DSN to connect to BigQuery through the specified proxy.

However, it's important to note a few things:

• Check Corporate Policies: Ensure that using a third-party ODBC driver and configuring it with proxy settings aligns with your organization's IT policies.
• Driver Installation: Installing the Simba ODBC driver might require administrative privileges. If you don't have these privileges, you'll need assistance from your IT department.
• Performance Considerations: The performance and capabilities of the Simba ODBC driver might differ from the native Power BI connector. Test your queries and reports to ensure they meet your performance and functionality requirements.

[mehulparmariitr]

# Override using a property:
gcloud config set api_endpoint_overrides/storage
storage-vialink1.p.googleapis.com

This Gcloud command will also impact SDK only and not Power BI connector @ms4446 ?

[ms4446]

Yes, you are correct. The following command:

gcloud config set api_endpoint_overrides/storage storage-vialink1.p.googleapis.com

is specific to the Google Cloud SDK and will only affect how the SDK interacts with Google Cloud services. This command sets an API endpoint override for the GCS service within the context of the Google Cloud SDK environment. It does not have any impact on external applications or connectors, including the Power BI connector for BigQuery.

The Power BI connector for BigQuery operates independently of the Google Cloud SDK. It uses its own mechanisms for connecting to BigQuery services, which are not influenced by configurations made in the Google Cloud SDK. Therefore, any endpoint overrides or proxy configurations set through the gcloud command line will not affect how Power BI connects to BigQuery.

If you need to configure proxy settings or endpoint overrides for Power BI's connection to BigQuery, you would have to explore options within Power BI itself or through any ODBC/JDBC drivers you might be using, as the Google Cloud SDK settings will not be applicable in this case.

[mehulparmariitr]

okay I tried with ODBC connector and modifying host file. It successfully connects to proxy. But issue is there is no direct query option in ODBC driver. So ultimately we have to use Google BQ connector instead of ODBC. How can we insert a proxy in between powerBI cloud and Google BQ connection? 

One thing I noted when we modify host file on windows machine then Google BQ connector routes to proxy only which is a good thing. Same thing I want between powerBI cloud and Google BQ connection.

[ms4446]

Integrating a proxy between Power BI Service (cloud) and Google BigQuery presents a different set of challenges compared to a desktop environment, primarily because you don't have the same level of control over the network configuration. The modification of the hosts file and the use of ODBC connectors with proxy settings are strategies that apply to local environments and don't directly translate to cloud services. However, there are a few strategies you might consider to achieve a similar effect in a cloud context:

1. Cloud Interconnect Services

Using cloud interconnect services or peering might be a way to control the traffic flow between Power BI Service and Google BigQuery. This involves setting up a dedicated network connection between your cloud provider (where your proxy or network functions are hosted) and Google Cloud. This approach requires significant setup and possibly substantial costs but offers high levels of control over the network path.

2. Virtual Private Cloud (VPC) Peering in Google Cloud

If your proxy solution can be hosted within Google Cloud, you might use VPC Peering to connect your Google Cloud project (hosting BigQuery) with another VPC where your proxy or network functions reside. This setup allows you to route requests from Power BI through your proxy infrastructure before they reach BigQuery. However, this doesn't directly affect how Power BI Service connects to external data sources and might require additional configuration or services to route the traffic appropriately.

3. Custom Data Gateway

For scenarios where direct modification of network paths is needed, a custom setup involving Power BI's On-premises Data Gateway might be considered. Although typically used to connect Power BI Service to on-premises data sources, it's conceivable to configure the data gateway to route through a proxy server for outbound connections. This setup involves:

• Installing the On-premises Data Gateway on a server in your network.

• Configuring this server to use your proxy for all internet connections, effectively routing Power BI Service requests through your proxy when accessing BigQuery.

This approach has limitations and complexities, especially around performance and maintenance, and it somewhat contradicts the gateway's intended use case. It's essential to review Microsoft's documentation and possibly consult with their support to understand the implications fully.

4. API Management Solutions

Another indirect approach involves using API management solutions that can act as a proxy or a gateway for your APIs. By exposing a custom API that Power BI can consume, which in turn queries BigQuery, you have complete control over the routing and can implement any required logic or routing in between. This requires developing and hosting a custom API, which adds complexity and overhead.

5. Direct Support and Feature Requests

Given the limitations and complexities of the above approaches, it's also worth considering reaching out to Microsoft and Google Cloud support. They might offer guidance or solutions based on newer features or upcoming releases that better suit your needs. Additionally, submitting a feature request to Microsoft for proxy support in Power BI Service connections could highlight the demand for such a feature.

Routing Power BI Service through a proxy to connect to Google BigQuery involves complex networking and cloud architecture considerations. Each potential solution has its trade-offs in terms of complexity, cost, and maintenance requirements. In many cases, the best approach depends on your organization's specific needs, technical capabilities, and the strategic importance of the data integration between Power BI and BigQuery. Collaboration with IT, network specialists, and possibly vendor support is crucial to finding a viable and sustainable solution.