If you're learning Power BI, one of the first practical skills you need is connecting it to a database. Most tutorials stop after showing a simple SQL Server connection, but real projects are rarely that simple. Sometimes the database is running on your own machine. Other times it lives in the cloud and requires SSL certificates before Power BI will even talk to it.
In this guide, I'll walk through both scenarios:
Connecting Power BI to a local SQL Server.
Connecting Power BI to an Aiven PostgreSQL database using SSL, with DBeaver helping us verify the connection first.
The goal is not just to make the connection work, but to understand why each step matters so you can troubleshoot issues later.
Before You Start
You should have:
Power BI Desktop installed.
PostreSQL Server installed locally.
DBeaver installed.
- An Aiven PostgreSQL service powered on and running.
- The SSL certificate file downloaded from Aiven, usually named
ca.pem.
DBeaver is useful because it lets you confirm that the database itself is reachable before involving Power BI. If DBeaver cannot connect, Power BI almost certainly won't either.
Part 1: Connecting Power BI to a Local SQL Server
Let's start with the easier case.
Step 1: Find Your SQL Server Name
Open SQL Server Management Studio (SSMS) if you have it installed, or check the SQL Server services list.
Common server names look like:
localhostlocalhost\SQLEXPRESSDESKTOP-ABC123\SQLEXPRESS
If you're using SQL Server Express, the
\SQLEXPRESSpart is often required.
Step 2: Open Power BI
Launch Power BI Desktop.
Select Get Data → SQL Server.
Step 3: Enter Connection Details
Fill in:
| Field | Example |
|---|---|
| Server | 127.0.0.1:0000 (localhost IP Adress:port_number) |
| Database | postres in my case or leave blank to see all databases, or enter a specific database name. |
Choose Import mode for beginners. It loads data into Power BI and is easier to work with than DirectQuery.
Click OK.
Step 4: Authenticate
Power BI will ask how to log in.
For a local development machine, Windows authentication is usually the simplest option because it uses your current Windows account.
If your SQL Server uses SQL logins instead, choose Database and enter the username and password.
Step 5: Select Tables
After connecting, Power BI shows the available tables and views.
Select the tables you need and click Load.
At this point, your local SQL Server is connected and ready for reporting.
Part 2: Verify Aiven PostgreSQL in DBeaver First
Cloud databases introduce one extra requirement: SSL encryption.
Aiven provides PostgreSQL over an encrypted connection, which means Power BI needs the same certificate information that DBeaver uses.
Step 1: Create the Connection in DBeaver
Open DBeaver.
Click New Database Connection.
Choose PostgreSQL.
Enter the connection details from your Aiven dashboard:
| Field | Example |
|---|---|
| Host | pg-12345.aivencloud.com |
| Port | 12345 |
| Database | defaultdb |
| User | Your Aiven username |
| Password | Your Aiven password |
Step 2: Configure SSL
From the Aiven dashboard, download the CA certificate
On your windows PC launch Microsoft Management Console by typing mmc in the search console, and add the certificate snap-in by clicking on File --> Add/Remove Snap-in.
Restart Power BI if you had it open to ensure the SSL certificate is loaded.
Part 3: Connect Power BI to Aiven PostgreSQL with SSL
Step 1: Install the PostgreSQL Connector if Needed
Recent versions of Power BI already include it. If Power BI prompts you to install the PostgreSQL driver, follow the prompt and restart Power BI.
Step 2: Start the Connection
- Select Get Data → PostgreSQL database.
- Enter the server as
host:portfrom the aiven connection information section.
Example
pg-12345.aivencloud.com:12345
Enter the database name, such as defaultdb.
Step 3: Configure SSL in Power BI
When the authentication window appears:
Choose Database authentication.
Enter your Aiven username and password.
Expand Advanced options if available.
Set SSL Mode to
verify-full.Browse to the same
ca.pemcertificate used in DBeaver.
Click Connect.
Step 4: Load the Data
Power BI will display the PostgreSQL schemas and tables.
Select the tables you need and click Load.
The first connection may take a little longer because Power BI is validating the SSL certificate.
Why Use DBeaver Before Power BI?
Beginners often try to troubleshoot everything inside Power BI, which can be frustrating because the error messages are not always specific.
DBeaver gives clearer feedback:
If the host or port is wrong, DBeaver tells you immediately.
If the password is incorrect, you'll see an authentication error.
If the SSL certificate is missing or invalid, DBeaver points directly to the SSL problem.
Once DBeaver connects successfully, Power BI usually becomes a straightforward configuration task.
Common Mistakes Beginners Run Into
Using the wrong SQL Server instance name
localhost and localhost\SQLEXPRESS are not the same thing. If one fails, check which instance is actually running. If localhost is not working, use the localhost IP address 127.0.0.1
Forgetting the port number for PostgreSQL
Aiven does not always use the default PostgreSQL port 5432. Use the exact port shown in the Aiven dashboard.
Skipping SSL configuration
Aiven requires encrypted connections. If you omit the certificate or use the wrong SSL mode, Power BI will refuse the connection.
Mixing up authentication methods
Local SQL Server often uses Windows authentication, while Aiven PostgreSQL uses database username and password authentication. Choosing the wrong method leads to login failures even when the credentials are correct.
Key Takeaways
By the end of this setup, you should be able to connect Power BI to two very different database environments:
Local SQL Server using Windows or SQL authentication.
Aiven PostgreSQL using SSL certificates and database credentials.
The local SQL Server connection is mostly about identifying the correct server instance. The Aiven PostgreSQL connection is mostly about getting SSL configured correctly. DBeaver acts as a useful checkpoint before you bring Power BI into the picture.
Once both connections are working, the experience inside Power BI is surprisingly similar. You choose tables, load the data, and start building reports. The difficult part is usually the first successful connection. After that, adding new tables or refreshing data becomes much easier because the connection settings are already saved.
If you're practicing, I recommend testing the local SQL Server connection first, then moving to Aiven PostgreSQL. It builds confidence because you learn the Power BI workflow before dealing with SSL and cloud networking details.
Top comments (0)