Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Power BI Desktop connects to a local SQL Server through Get data > SQL Server, using either Import or DirectQuery. For a published report to reach an on-premises SQL Server, configure an on-premises data gateway. Aiven requires a separate decision: identify its database engine first, then verify which Power BI connector and service-side route support that engine. The title alone does not establish whether the Aiven service is PostgreSQL, MySQL, or another database.
Connect Power BI Desktop to a local SQL Server
- In Power BI Desktop, select Get data > SQL Server.
- Enter the SQL Server name and, optionally, a database name. Use the server and database values you intend to configure later in the Power BI service.
- Choose Import or DirectQuery, if available for your connection, and select OK.
- Authenticate with an account that has access to the database, then select the data you need.
Microsoft’s DirectQuery guidance describes the distinction: Import loads a copy of the data into the Power BI model, while DirectQuery sends queries to the source as report users interact with it.
Choose Import or DirectQuery
| Mode | Data behavior | Considerations |
|---|---|---|
| Import | Loads a copy into the Power BI model. | Changes at the source are reflected only after the model is refreshed. Consider it when refresh timing is acceptable and imported data suits the model. |
| DirectQuery | Queries the source as report interactions occur. | Consider it when keeping data at the source matters and the database and report workload can support interactive queries. Performance and feature limitations apply, and results depend on the source and configuration. |
Check the connector’s supported capabilities and limitations before choosing a mode. Neither option is universally better: the right choice depends on freshness needs, source capacity, and report behavior.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Make an on-premises SQL Server available to the Power BI service
Desktop may connect to a local SQL Server even when the Power BI service cannot reach that server’s network. For the documented on-premises SQL Server workflow, Microsoft uses an on-premises data gateway to provide service access and support refresh.
#1 Best Overall
- Install or use an on-premises data gateway on a machine that can reach the SQL Server, and ensure the gateway is online.
- In the Power BI service, add a SQL Server data source to the gateway and enter its server and database details.
- Configure credentials for the data source using an account that has the necessary database access.
- Publish the model, then map it to the matching gateway data source. For an Import model that needs scheduled updates, configure its refresh schedule.
- Check gateway status and refresh history if service access or refresh fails. Keep the gateway on a supported version and follow Microsoft’s maintenance guidance.
The published model’s server and database values must match the gateway data source values. Microsoft notes that the mapping depends on these names; a difference such as a hostname versus an IP address, or a mismatched SQL Server instance name, can prevent the model from linking to the configured source. See Microsoft’s guidance on connecting to on-premises SQL Server data and managing a SQL Server gateway data source.
Identify the Aiven database before configuring Power BI
Aiven is a cloud database platform, not one database engine. Start in the Aiven Console by confirming the exact product and engine. The host, port, database name, username, password, TLS settings, and compatible Power BI connector can differ by engine. Do not assume that Power BI’s SQL Server connector is appropriate for an Aiven service.
The Aiven documentation cited here describes client connections for PostgreSQL and MySQL, but does not establish a Power BI-specific workflow for either. Before building a report, verify that the selected Power BI connector supports the confirmed engine and determine its requirements for Desktop connection, published-model credentials, refresh, and gateway or other service connectivity. Microsoft’s DirectQuery documentation says sources other than the specifically named cloud services require an on-premises data gateway for the documented DirectQuery service scenario. Because the Aiven engine and connector route are unspecified here, that general guidance does not establish the gateway requirement for a particular Aiven setup; check the requirements for the exact connector.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteUse the engine-specific Aiven connection details and TLS settings
Aiven for PostgreSQL
Retrieve the PostgreSQL service’s connection URI or parameters from its service overview in the Aiven Console. Aiven’s PostgreSQL connection examples use TLS with sslmode=require. This encrypts traffic but does not verify the server certificate. For certificate verification, Aiven documents using the project CA certificate with verify-ca or verify-full, when supported by the client. See its TLS/SSL certificate guidance. Confirm that the Power BI connector you plan to use supports the required parameters and certificate handling.
Aiven for MySQL
Get the host, port, database, user, and password for the MySQL service from its Aiven Console details. Aiven’s MySQL Workbench connection instructions recommend SSL and explain client certificate settings. Those instructions describe MySQL Workbench, not Power BI; verify how the intended Power BI connector handles MySQL authentication and TLS rather than copying PostgreSQL parameter names or steps.
Quick Recap
Best Value
Troubleshoot the connection by layer
- Desktop cannot connect to local SQL Server: Recheck the server and optional database entries, network reachability, and the credentials’ database access.
- The model publishes but cannot reach local SQL Server: Check that the gateway is online, its SQL Server data source has valid credentials, and its server and database names match the published model exactly.
- Scheduled Import refresh fails: Review the gateway status and refresh history, then check the gateway source credentials and mapping.
- Aiven connection setup is unclear: Confirm the service engine and consult that engine’s connection details. Then check the chosen Power BI connector’s supported authentication, TLS, and service-refresh requirements; Aiven’s client examples alone do not specify a Power BI configuration.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
