"We got the gateway installed, added the data source — and Power BI still refused to refresh. The error said 'invalid credentials'. We'd been using the Windows admin account." — Power BI Advanced training, Week 4 troubleshooting session

When Power BI Service needs to query an on-premises SQL Server through the data gateway, it authenticates using credentials stored in the gateway's data source settings. Using a Windows admin account or your personal domain login is a common anti-pattern that causes refresh failures, lock-outs, and security audit flags. The right approach is a dedicated SQL login with minimal permissions — created specifically for the gateway, nothing more.

This post walks through creating that SQL login in SSMS, then registering it in Power BI Service, with a security rationale for each step.

🗺️
Think of it like a delivery entrance

A large office building has a main entrance for employees and a separate, locked delivery entrance for couriers. The delivery entrance has its own key — it only opens the loading bay, not the executive floors. The courier doesn't need access to the CEO's office; they just need to drop packages at the dock. Your Power BI gateway user is that courier: a dedicated SQL login that can read data from the relevant database, with no other access.

Why a Dedicated SQL Login?

Approach Problem Risk
Use domain admin / Windows auth Password rotation breaks gateway; admin credentials stored in Power BI Service High — broad access, audit violation
Use personal SQL login (e.g., raushan_admin) Breaks when employee leaves; login often has excess permissions Medium — account lifecycle issue
Dedicated pbi_gateway_user (read-only) None — purpose-built, minimal privilege, independent lifecycle Low — correct approach

Step 1 — Create the SQL Login in SSMS

Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance. Then follow these steps:

1️⃣
Navigate to Security → Logins
In Object Explorer, expand your server instance → expand Security → expand Logins → right-click Logins → select New Login.
2️⃣
Set Login Name and Authentication
On the General tab: select SQL Server Authentication. Enter a login name such as pbi_gateway_user. Set a strong password. For production: keep "Enforce password policy" checked. For dev/test only, you may uncheck it.
3️⃣
Map to Database with db_datareader
On the User Mapping tab: check the target database(s) your Power BI reports use. In the database role membership section, select db_datareader only. This grants SELECT on all user tables and views — nothing more.
4️⃣
Click OK and Note the Credentials
The login is created. Verify it appears under Security → Logins. Note the exact login name and password — you will need both in Step 2 when registering the data source in Power BI Service.

Step 2 — Register Credentials in Power BI Service

Once the SQL login exists in SSMS, register it as the gateway data source credential in Power BI Service:

Step Action
1 Go to app.powerbi.com → Settings (gear icon) → Manage connections and gateways
2 Find your gateway cluster → click the three-dot menu → Add data source
3 Data source type: SQL Server. Enter Server name and Database name exactly as they appear in your Power BI Desktop connection string
4 Authentication method: Basic. Enter the pbi_gateway_user login name and password created in SSMS
5 Click Add → wait for the green "Connection Successful" status indicator before publishing any reports
The server name and database name entered in the gateway data source must exactly match the connection string in the published .pbix file — including whether you use the machine name, FQDN, or IP address. A mismatch causes Power BI Service to not map the dataset to the gateway data source, and scheduled refresh fails silently.

3 Most Common Configuration Mistakes

  • Server name mismatch between Power BI Desktop and the gateway data source: If the dataset was published with connection string CORP-SQL01SQLEXPRESS but the gateway data source is registered as 192.168.1.50, Power BI will not match them. Use the identical server name format in both places — machine name, FQDN, or IP, pick one and be consistent.
  • Granting db_owner instead of db_datareader: Some admins assign db_owner "just to avoid permission issues". This gives the gateway user full write and schema-change access to the database — a serious security risk that will fail most enterprise security audits. db_datareader is sufficient for all standard Power BI read and refresh scenarios.
  • Not testing the data source before publishing reports: Always click the test button after adding the gateway data source in Power BI Service. A green "Connection Successful" message confirms the gateway can reach SQL Server with those credentials — before you discover a problem during a scheduled overnight refresh.

Quick Knowledge Check

Q1. When configuring a data source on the Power BI on-premises data gateway, which SQL Server role gives the gateway user the minimum permissions required for report refresh?

Show Answer

db_datareader. This role grants SELECT permission on all user tables and views in the mapped database — exactly what Power BI needs to execute queries and refresh data. It grants no write, schema-change, or administrative access.

Q2. A Power BI dataset was published with the connection string CORP-SQLPROD. The gateway data source was configured with the server name 10.0.0.5. What will happen at scheduled refresh?

  • A) The refresh will succeed — Power BI resolves the hostname to the IP automatically
  • B) The refresh will fail — Power BI cannot match the dataset to the gateway data source due to the name mismatch
  • C) Power BI will prompt the user to select a gateway data source manually
  • D) The refresh will succeed only the first time, then fail on subsequent attempts
Show Answer

B. Power BI Service matches datasets to gateway data sources by exact server and database name string. CORP-SQLPROD and 10.0.0.5 are different strings — even if they resolve to the same machine — so the dataset will not be mapped to the gateway data source and refresh will fail with a "data source not found" error.

Q3. Why should you use SQL Server Authentication (username + password) rather than Windows Authentication for the Power BI gateway data source credential?

Show Answer

Power BI Service runs in the cloud as a service principal, not as a domain-joined Windows user. Windows Authentication (Kerberos/NTLM) relies on domain membership that cloud services do not have. SQL Server Authentication with a dedicated login lets Power BI authenticate independently of the Windows domain, works reliably across cloud-to-on-premises connectivity, and avoids credential delegation complexity.

5 Things to Remember
  • Always use a dedicated SQL login for the gateway — never use a personal account or domain admin. The account should have a single purpose: read access for Power BI.
  • db_datareader is sufficient — Power BI only reads data during refresh. Granting db_owner is a security anti-pattern that will fail enterprise security audits.
  • Server name must match exactly — the connection string in the published dataset and the gateway data source registration must use identical server name format (hostname, FQDN, or IP — pick one and use it consistently).
  • Use SQL Server Authentication, not Windows Auth — cloud services cannot authenticate against Windows domains. SQL Auth with a stored credential is the reliable, supported approach.
  • Test before publishing — the "Connection Successful" indicator in Power BI Service gateway settings confirms end-to-end connectivity. Always test before scheduling a refresh or publishing reports to users.
📚 Resources