How to Connect Power BI to SQL Databases (Local & Cloud)

Connecting your data to Power BI is usually the very first step in building any meaningful dashboard. SQL databases, whether located on a local office server or hosted out in the cloud, they remain the industry foundation for this data.

Let's walk through how to set up both local and cloud-based SQL connections in Power BI, step-by-step.

---

Part 1: Connecting Power BI to a Local SQL Server

To pull data from a local SQL Server, you will need to have a server name and database configuration parameters.

Step 1: Open the SQL Server Connector

1. Launch Power BI.
2. On the Home tab ribbon, click on Get Data.
3. Select More... and search postgres to locate it under the Database category.

Step 2: Configure Server Details & Data Connectivity

A dialog box will appear asking for your server credentials and access choices.

• Server: Enter your local server path i.e (localhost:5432)
• Database: Enter your database name (postgres)
• Data Connectivity Mode:
  • Import: Downloads a snapshot of the data into the Power BI file. Best for fast performance and complex modeling.
  • DirectQuery: Queries the data live every time you interact with a visual. Essential for massive datasets or real-time tracking.

Step 3: Provide Credentials

For authentication:

• Select Database to enter your username and password, then click Connect.
• Preview and Load Tables The Navigator window will open. Expand your database instance, check the boxes next to the tables or views you want to use and click Transform Data to clean them inside Power Query.

---

Part 2: Connecting Power BI to a Cloud Database (Aiven)

Cloud solutions like Aiven make it easy to deploy PostgreSQL databases. Because Aiven exposes a public endpoint by default, Power BI can securely stream cloud data.

Step 1: Gather Connection Details from Aiven

Before switching to Power BI, grab your credentials from the Aiven Console.

• Log in to your Aiven Console and select your PostgreSQL service(make sure it's running).

• In the Overview tab, find the Connection information and copy the following fields: Host, Port, User, Password, and Database Name.

Step 2: Install the CA Certificate on Your Windows Machine

Power BI relies on the Windows Certificate Store to validate SSL handshakes. Download and install your ca.pem file as a trusted certificate.

• Click the Windows Start menu, search for Manage user certificates, and open it.
• In the left panel, locate and click on Trusted Root Certification Authorities.
• Right-click on the Certificates folder inside that directory, navigate to All Tasks, and click Import.
• The Certificate Import Wizard will pop up. Click Next, change the file extension filter to "All Files (.)" so you can find your ca.pem file, select it, and complete the installation wizard instructions.

Step 3: Select the PostgreSQL Connector in Power BI

1. Back in Power BI, click Get Data on the Home ribbon.
2. Click More..., choose Database on the left menu, and select PostgreSQL database.
3. Click Connect.

Establish the connection on Power BI

• Server: Combine your host and port separated by a colon (e.g., pg-your-project-name.aivencloud.com:12345).
• Database: Type the exact name of the database you intend to read from (e.g., defaultdb).
• Select either Import, then click OK.

• Authenticate with Database Credentials - Username and Password, then click Connect.
• Select Tables - The Navigator panel will look up your database. Select your dataset, then click Load for a clean dataset or Transform to start the process of cleaning on Power Query

---

Finally

Once these parameters are configured, your relationships can be built directly inside the Power BI Model view. The core difference ultimately boils down to network topology: local connections require safe local-network access paths or gateways, while cloud-hosted services like Aiven rely primarily on public connection strings backed by strong encryption and user credentials.