DEV Community

Dalton Imbiru
Dalton Imbiru

Posted on

How to connect Power BI to SQL Databases

Power BI can connect directly to SQL databases either locally or in the cloud. By connecting Power BI to a SQL database, organizations can analyze live data, automate reporting, and make data-driven decisions without relying on manual data exports.

The following is a guide on how to connect Power BI to both local SQL databases and cloud-based SQL databases.

To connect to a SQL database, ensure you have:

  • Power BI Desktop installed.
  • Access to the SQL database.
  • Database server name.
  • Database name.
  • Username and password (if required).
  • Internet connectivity (for cloud databases).
  • PostgreSQL or SQL Server drivers installed if necessary.

Connecting Power BI to a Local SQL Database

A local SQL database is hosted on your computer or an organisation's internal network.

Step 1: Open Power BI Desktop and get data

If using PostgreSQL:

Launch Power BI Desktop - Home - Get Data - PostgreSQL Database.

image

Before Entering Connection details, go to PostgreSQL Connections and copy and paste the connection details into Power BI. That is, the server, which is the host, followed by a colon then copy and paste the port thereafter.
Copy and paste the Database Name and click ok.

image

After Loading, you will be directed to a navigator page where you will be able to select the data needed and load and transform the data.

image

Use Transform Data if cleaning or transforming data is required before loading.

Connecting Power BI to a Cloud-Based SQL Database

Cloud databases are hosted on remote servers such as:

  • Microsoft Azure SQL Database
  • Amazon RDS
  • Google Cloud SQL
  • Aiven PostgreSQL
  • ElephantSQL
  • Supabase PostgreSQL

The connection process is similar but requires internet access.
Using Aiven PostgreSQL

Before Entering Connection details, go to Aiven and copy and paste the connection details into Power BI. That is, the server, which is the host, followed by a colon then copy and paste the port thereafter.
Copy and paste the Database Name and click ok.

image

After Loading, you will be directed to a navigator page where you will be able to select the data needed and load and transform the data.

image

Use Transform Data if cleaning or transforming data is required before loading.

Importing the Aiven CA Certificate (Windows)

Aiven PostgreSQL uses SSL encryption to secure connections.

Download the CA Certificate

  1. Log in to your Aiven Console.
  2. Open your PostgreSQL service.
  3. Navigate to the Overview or Connection Information section.
  4. Download the CA Certificate.

Download CA Certificate

Open the Certificate Manager

  1. Press the Windows key.
  2. Search for Manage User Certificates.
  3. Open the application.

Manage User Certificates

Import the Certificate

  1. In the Certificate Manager, expand: 
   Trusted Root Certification Authorities
Enter fullscreen mode Exit fullscreen mode
  1. Select: 
   Certificates
Enter fullscreen mode Exit fullscreen mode

  1. Right-click Certificates.

  2. Select:

   All Tasks → Import
Enter fullscreen mode Exit fullscreen mode

  1. Click Next.

  2. Click Browse.

  3. Navigate to your Downloads folder and select the CA Certificate you downloaded from Aiven.

Import Certificate

  1. Click Next.

  2. Ensure the certificate is imported into:

   Trusted Root Certification Authorities
Enter fullscreen mode Exit fullscreen mode

  1. Click Finish.

  2. A confirmation message should appear indicating that the import was successful.

Verify the Installation

After importing the certificate:

  • Restart Power BI Desktop if it was open.
  • Connect to your Aiven PostgreSQL database using the PostgreSQL connector.
  • The SSL certificate should now be trusted, allowing Power BI to establish a secure connection without certificate errors.

Top comments (0)