Overview

Member programs and partners can connect Power BI to their Biotics PostgreSQL database for reporting. This works differently depending on whether you're using Power BI Desktop (on your own machine) or the Power BI Service (cloud), and there are a few network and certificate considerations specific to our AWS-hosted databases. This article covers how to set up each type of connection, and how to troubleshoot the errors partners most commonly hit.

Connection Details

  • Host/server: the <instance>-db.natureserve.org alias for your instance (e.g. bioticspilot-db.natureserve.org) — this is a friendly DNS alias for the underlying AWS RDS instance.
  • Port: 5432
  • Database: your Biotics database name (e.g. biotics)
  • Username/password: your Biotics database account credentials (not your Biotics application login — see Accounts below)

Accounts for BI Reporting

  • Every Biotics database account must have a unique email address — we're not able to provision a shared or central "BI reporting" account.
  • Once your program's conversion to Biotics 6 is complete, you can create your own dedicated BI reporting user, as long as it has its own unique email address.
  • This account is subject to the same 60-day password rotation as any other Biotics database account. Rotate it from Personal Settings > Rotate Database Account Password.
  • In the meantime, you can test your connection setup (including a gateway, below) using your own individual Biotics credentials.

Connecting via Power BI Desktop

This is the simplest path and generally works without extra configuration, since the request comes from your own machine/network.

  1. In Power BI Desktop, choose Get Data > PostgreSQL database.
  2. Enter the server (host + port) and database name.
  3. Choose Database authentication and enter your Biotics database username and password.
  4. If you hit a certificate error, see the SSL troubleshooting section below.

Connecting via the Power BI Service (Cloud)

Setting up a connection directly in the Power BI Service (e.g. for scheduled refresh of published reports) requires an extra step, because the request now comes from Microsoft's Azure cloud rather than your own network.

Why this needs a gateway

Our database only accepts connections from whitelisted IP ranges (e.g. your organization's network). Power BI Desktop and tools like pgAdmin work because they connect from your own whitelisted network. But the Power BI Service itself runs in Microsoft's Azure datacenters — those IPs aren't (and realistically can't be) whitelisted, since Azure's range is enormous and constantly changing. A direct cloud connection attempt to our database will time out.

Setting up an On-premises Data Gateway

An On-premises Data Gateway is a small piece of software installed on a machine inside your own network. It acts as a secure relay: the Power BI Service sends the query to the gateway, the gateway forwards it to our database from inside your whitelisted network, and sends the results back to the cloud. Credentials and data never have to be exposed directly to the internet — the gateway keeps an encrypted connection to the Power BI Service.

Setup steps:

  1. Download and install the gateway — from the Microsoft site (search "On-premises data gateway download"). Install it on a machine that's always on and within your network's whitelisted range — not a personal laptop that isn't always connected.
  2. Sign in and register the gateway — sign in with the Power BI account you'll use for these reports, register a new gateway, and give it a recognizable name (e.g. "Parks Canada - Biotics Reporting"). Save the recovery key somewhere safe; it's needed to migrate or restore the gateway later.
  3. Add the Biotics data source to the gateway — in the Power BI Service (app.powerbi.com), go to Settings > Manage connections and gateways > New, and add a PostgreSQL data source with our connection details (server, port 5432, database, and your BI reporting account credentials — see Accounts above).
  4. Use the gateway connection in your reports — when publishing a report/dataset that uses this data source, choose the gateway connection rather than a direct cloud connection. Power BI will route the query through your gateway automatically.

Once this is in place, cloud refreshes and scheduled reports should connect successfully, since the request is coming from your whitelisted network the whole time.

Troubleshooting

Error: "connection attempt failed because the connected party did not properly respond after a period of time" / connection timeout

Cause: The Power BI Service is trying to connect directly from Azure's cloud IPs, which aren't whitelisted. Fix: Set up an On-premises Data Gateway (see above) and route the connection through it instead of connecting directly from the cloud.

Error: "The remote certificate is invalid according to the validation procedure" (with "Use encrypted connection" checked)

Cause: Power BI's PostgreSQL connector (built on the Npgsql/.NET driver) performs strict SSL certificate chain validation by default — it checks that the certificate is signed by a CA in the client's trusted root store, matches the hostname, hasn't expired, and has a complete chain to a trusted root. AWS RDS certificates are signed by Amazon's own CA, which most machines don't trust by default. By contrast, pgAdmin and most libpq-based tools use sslmode=prefer, which encrypts the connection without verifying the signer — so they connect fine against the same certificate. Fix (client-side):

  1. Download the appropriate AWS RDS CA bundle:
  2. Set the sslrootcert connection parameter to point to the downloaded file, e.g. sslrootcert=c:\some\path\us-east-1-bundle.pem.
  3. See AWS's guides for client-specific instructions: Using SSL to encrypt a connection to a DB instance or cluster, Using SSL with a PostgreSQL DB instance.

Note: switching from the friendly -db.natureserve.org alias to the underlying RDS cluster hostname (found via nslookup) does not by itself resolve this — the client still needs to trust AWS's signing CA as above.

Error: "Invalid credentials" (with "Use encrypted connection" unchecked)

Cause: This is usually a red herring, not an actual credentials problem. If pg_hba.conf requires SSL for this user/database (a hostssl rule), an unencrypted connection attempt is rejected at authentication and surfaces as a generic credentials error rather than an "SSL required" message. Fix: Re-check "Use encrypted connection" and resolve the certificate trust issue above, rather than re-entering credentials.

Still stuck?

Confirm with the user:

  • Are they connecting via Power BI Desktop or the Power BI Service (cloud)?
  • Is "Use encrypted connection" checked or unchecked, and what's the exact error text?
  • Have they set up an On-premises Data Gateway if this is a cloud/scheduled-refresh connection?
  • Have they applied the AWS RDS CA bundle if it's a certificate error?

If none of the above resolves it, escalate to confirm server-side certificate/CA configuration (see internal engineering notes on this ticket for the underlying cert investigation).