joy yego

Posted on Jul 10

Securely Connecting Power BI to an Aiven PostgreSQL Database Using SSL Certificates

Learn how to connect Power BI Desktop to an Aiven PostgreSQL database using SSL certificates, troubleshoot common connection issues, and start building dashboards securely.

Cloud-hosted databases have become the backbone of modern analytics, and platforms like Aiven make it incredibly easy to deploy and manage PostgreSQL instances. But if you're connecting Power BI Desktop to an Aiven database for the first time, you might run into SSL certificate errors or wonder why the connection isn't as straightforward as it is with a local database.

I recently faced the same challenge while building a Power BI dashboard, and after a bit of troubleshooting, I discovered that the solution was simply trusting Aiven's SSL certificate through Windows.

In this guide, I'll walk you through the complete process so you can connect securely and start building dashboards without the frustration.

Prerequisites

Before we begin, make sure you have:

Power BI Desktop installed
An active Aiven PostgreSQL service
Your database credentials (Host, Port, Username, Password)
The Aiven CA certificate (ca.pem)
Administrator access to install certificates on Windows

How the Connection Works

Power BI communicates with your PostgreSQL database over an encrypted SSL connection. Before Windows allows that connection, it must trust the certificate used by Aiven.

Once the certificate is installed, Power BI can securely authenticate and retrieve your data.

Architecture Diagram

Power BI Desktop
│
▼
Windows Certificate Store
(Trusted Root Certification Authorities)
│
▼
Encrypted SSL Connection
│
▼
Aiven PostgreSQL Database

Step 1: Download the CA Certificate

Under Connection Information, download the CA Certificate.

The downloaded file is usually named: ca.pem

Save it somewhere convenient.

Step 2: Trust the Certificate in Windows

Before Power BI can connect securely, Windows needs to trust the certificate.

Open the Windows Run dialog (Win + R) and type: certmgr.msc

Navigate to:

Certificates
└── Trusted Root Certification Authorities
    └── Certificates

Right-click Certificates, then select:

All Tasks
    ↓
Import...

The Certificate Import Wizard will open.

Browse to the downloaded certificate and complete the wizard.

Once imported, Windows will trust Aiven's certificate.

Note: Depending on your environment, you may need to convert the certificate from .pem to .cer before importing it.

Step 3: Open Power BI

Launch Power BI Desktop.

Navigate to:

Home → Get Data → PostgreSQL Database

A PostgreSQL connection dialog will appear.

Step 4: Enter Your Connection Details

One mistake many beginners make is copying the entire PostgreSQL connection URL into the Server field.

For example, this will not work:

postgres://avnadmin:password@hostname:12345/defaultdb

Power BI expects only the hostname and port.

Enter the details like this:

Server

your-service.aivencloud.com:12345

Database

defaultdb

Choose your preferred connectivity mode:

Import – Copies the data into Power BI (recommended for most users)
DirectQuery – Queries the database in real time

Then click OK.

Step 5: Authenticate

Power BI will prompt you for your database credentials.

Enter:

Username: avnadmin
Password: Your PostgreSQL password

Click Connect.

Since the certificate has already been trusted by Windows, Power BI can now establish a secure SSL connection.

Step 6: Select Your Tables

If the connection succeeds, the Navigator window will display all available tables.

Choose the tables you want and click:

Load to import the data immediately, or
Transform Data if you want to clean or shape your data first.

I usually recommend Transform Data so you can verify data types and remove unnecessary columns before building your report.

Step 7: Build Your Dashboard

Congratulations!

Your Power BI report is now connected securely to your Aiven PostgreSQL database.

From here, you can create:

Sales dashboards
Customer analytics
Operational reports
Financial dashboards
KPI tracking
Real-time business insights

Every refresh will retrieve the latest data securely from your cloud database.

Common Issues and Their Fixes

SSL Certificate Error

Cause: Windows doesn't trust the certificate.

Solution: Import the Aiven CA certificate into the Trusted Root Certification Authorities store.

Authentication Failed

Cause: Incorrect username or password.

Solution: Verify your PostgreSQL credentials in the Aiven Console.

Could Not Connect to Server

Cause: Incorrect hostname or port.

Solution: Use the Host and Port values from Aiven's Connection Information page—not the PostgreSQL connection URL.

Invalid Server Name

If your Server field looks like this: postgres://avnadmin:password@hostname/defaultdb

Power BI will reject it.

Instead, enter only:
hostname:port

Best Practices

When working with cloud-hosted databases:

Never expose your database password in screenshots.
Avoid sharing full connection URLs publicly.
Keep your CA certificate in a secure location.
Rotate passwords regularly.
Use least-privilege database accounts whenever possible.

Conclusion

Connecting Power BI to an Aiven PostgreSQL database might seem intimidating at first, especially if you're new to SSL certificates. However, once you understand how Windows trusts certificates and how Power BI expects connection details, the process becomes straightforward.

The biggest lesson I learned was not to paste the full PostgreSQL connection URL into the Server field. Instead, use only the hostname and port, install the Aiven CA certificate into Windows' trusted certificate store, and let Power BI handle the rest.

With a secure connection in place, you're free to focus on what really matters—transforming raw data into meaningful insights through interactive dashboards.

If this guide helped you, let me know in the comments! I'd also love to hear about your experience connecting Power BI to cloud databases or any tips you've discovered along the way.

Happy dashboard building!

DEV Community