Enterprise Power BI gateway management goes far beyond clicking "Add data source" in the UI. Learn how to design resilient gateway clusters, automate data source provisioning via the REST API, delegate ownership to business units, and implement credential rotation policies that keep your on-premises connectivity secure without causing refresh failures at scale.

Picture this: it's Monday morning, and your data team arrives to find that the overnight refresh for 47 datasets has failed. The root cause? A single on-premises data gateway running on a VM that someone patched over the weekend, leaving it offline for three hours. Meanwhile, 300 report consumers are filing helpdesk tickets because their dashboards show last Friday's numbers. If you've lived this scenario — or you're trying to prevent it — this lesson is for you.
The on-premises data gateway is the linchpin of enterprise Power BI connectivity. When it's working, nobody notices it. When it fails, everyone does. Most organizations start their gateway journey by spinning up a single gateway on some available server, registering a few data sources, and moving on. That works fine for a team of ten analysts. It becomes a liability the moment your Power BI deployment reaches organizational scale — dozens of workspaces, hundreds of datasets, multiple business units with different security requirements, and an IT team that needs to rotate service account passwords without breaking refresh schedules at 2 a.m.
By the end of this lesson, you'll know how to architect a production-grade gateway infrastructure that handles failure gracefully, delegates data source management to the right people, and maintains security without requiring heroic manual effort. You'll also have concrete procedures for credential rotation that you can drop straight into your runbooks.
What you'll learn:
This lesson assumes you're already comfortable with the fundamentals of on-premises gateway setup. If you need a foundation, read Connecting Power BI to On-Premises Data Sources Using the On-Premises Data Gateway before continuing. You should also understand how Power BI personal gateway vs. on-premises data gateway decisions affect your architecture — this lesson exclusively covers the standard (organizational) on-premises gateway in cluster mode.
You'll need:
MicrosoftPowerBIMgmt module installedBefore you touch the UI or run a single PowerShell command, you need a mental model of what a gateway cluster actually is — because the terminology Power BI uses is slightly misleading to anyone with a networking background.
A gateway cluster in Power BI is not a traditional active-passive failover cluster, and it's not load-balanced in the network-infrastructure sense. It's a logical grouping of independently installed gateway software instances that share a common set of registered data sources and credentials. When a refresh request arrives at the Power BI service, it's routed to one of the available members in the cluster. If that member is offline or unresponsive, the service automatically retries on another member.
This distinction matters for two reasons. First, all members in the cluster need network access to the same data sources — they're not sharing connections, they're independently capable of making those connections. Second, credentials are stored and managed at the cluster level, not on individual machines. This means you don't need to re-enter credentials when you add a new member to an existing cluster.
By default, Power BI uses a random distribution model across cluster members. Each refresh request is assigned to a randomly selected available gateway. There's no sticky session, no affinity, and no queue-depth awareness in the default configuration. For most workloads, this is fine. For latency-sensitive DirectQuery scenarios hitting SQL Server, you may want to think about member sizing more carefully — if one member is on a VM with 4 cores and another has 16, the random distribution could route a heavy query to the underpowered machine half the time.
Key insight
Gateway cluster load distribution is random by default. If your cluster members have significantly different hardware specs, make them equivalent or expect inconsistent query performance for DirectQuery workloads. This is especially important if you're using Composite Models and DirectQuery where every visual interaction generates a live query through the gateway.
For an enterprise deployment, the minimum viable cluster has two members. Three is better. Here's the topology that works well for large organizations with heterogeneous data source types:
Gateway Cluster: PROD-GW-CLUSTER-01
├── PROD-GW-01 (Primary - Windows Server 2022, 16 cores, 32GB RAM)
├── PROD-GW-02 (Secondary - Windows Server 2022, 16 cores, 32GB RAM)
└── PROD-GW-03 (Secondary - Windows Server 2022, 16 cores, 32GB RAM)
Gateway Cluster: PROD-GW-CLUSTER-SAP
├── PROD-GW-SAP-01 (Windows Server 2022, 32 cores, 64GB RAM, SAP drivers installed)
└── PROD-GW-SAP-02 (Windows Server 2022, 32 cores, 64GB RAM, SAP drivers installed)
Gateway Cluster: DEV-GW-CLUSTER-01
└── DEV-GW-01 (Windows Server 2022, 8 cores, 16GB RAM)
Separate clusters for special connector requirements (SAP HANA, Oracle, mainframe ODBC) is a deliberate choice. Members in the same cluster must all support the same data sources — if you need ODBC drivers for a legacy system, you need those drivers on every member. By isolating exotic connectors into their own clusters, you keep your general-purpose cluster lean and avoid driver conflicts.
Warning
Never add a gateway member to a cluster unless it has network access to all data sources registered in that cluster. If you add a new member that can't reach a SQL Server that another member can, some refresh attempts will silently fail when they're routed to the network-restricted member. This is a particularly nasty class of intermittent failures.
Installing the first gateway creates the cluster. Installing subsequent gateways and joining them to that cluster is a distinct workflow — you're not installing the software again, you're registering a new instance to the existing logical group.
Download the gateway installer from the Power BI service (Settings → Download → Data gateway). The installer is consistent across versions; the configuration it performs is what creates or joins a cluster.
During setup, choose On-premises data gateway (recommended) — not the personal mode. Sign in with the service account that will own this gateway cluster. This account becomes the first gateway admin and its identity is baked into the cluster registration. For enterprise deployments, use a dedicated service account rather than a named user's account. When that person leaves the organization, you do not want their departure to disrupt your gateway infrastructure.
After installation, you'll assign a recovery key — treat this like a password manager master key. Store it in your organization's vault (Azure Key Vault, HashiCorp Vault, or at minimum a secured password manager). You'll need it to migrate the gateway to a new machine or restore it after a catastrophic failure.
On the second and subsequent gateway servers, install the same gateway software. When the installer asks whether you want to register a new gateway or migrate/restore, choose Add to an existing gateway cluster. You'll need:
After joining, verify the new member appears in Power BI Service → Settings → Manage gateways. The member should show status "Online" within a few minutes.
Tip
Stagger your gateway maintenance windows across cluster members. If you patch PROD-GW-01 on Tuesday and PROD-GW-02 on Wednesday, the cluster always has at least one online member. Build this into your change management process — a gateway cluster is only as available as your least-available member divided by your total member count.
You can temporarily take a cluster member offline for maintenance without removing it from the cluster. In Manage Gateways, select the member and use the Status control to disable it. All in-flight requests that were routed to that member will fail and retry on another member. Active DirectQuery connections will drop and reconnect. Scheduled refreshes that happen to pick that member mid-maintenance will fail on that attempt and succeed on retry.
A cleaner approach for maintenance windows is to use the REST API to disable the member before starting your maintenance work:
# Install the module if needed
# Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser
Connect-PowerBIServiceAccount
# Get all gateway clusters
$gateways = Invoke-PowerBIRestMethod -Url "gateways" -Method Get |
ConvertFrom-Json
# Find your target cluster
$targetCluster = $gateways.value |
Where-Object { $_.name -eq "PROD-GW-CLUSTER-01" }
Write-Host "Cluster ID: $($targetCluster.id)"
# List all members of this cluster
$membersUrl = "gateways/$($targetCluster.id)/datasources"
$members = Invoke-PowerBIRestMethod -Url $membersUrl -Method Get |
ConvertFrom-Json
$members.value | Select-Object id, gatewayId, datasourceType, connectionDetails
Note
The Power BI REST API for gateway management lives under the /gateways endpoint. The Power BI REST API lesson covers authentication and the full request lifecycle — refer to that if you're new to scripting against the Power BI service.
This is where most organizations hit the wall. When you have 20 data sources, clicking through the Manage Gateways UI is tedious but survivable. When you have 200 data sources across multiple clusters, manual management becomes an operational anti-pattern. Any configuration change — a server name change, a password rotation, a firewall migration — cascades into hours of manual updates.
The solution is treating data source configuration as infrastructure-as-code, using the REST API to create, update, and audit data sources programmatically.
Every data source registered on a gateway has:
Sql, Analysis Services, Oracle, File)When a dataset references a gateway data source, it references the datasource ID. This means you can change credentials on a data source without touching the datasets that use it — the ID remains stable. This is the key insight that makes credential rotation safe.
Here's a production-ready script for creating a SQL Server data source on an existing cluster. This would be part of your infrastructure provisioning pipeline — perhaps triggered from Azure DevOps when a new on-premises database is onboarded.
function New-PowerBIGatewayDataSource {
param(
[Parameter(Mandatory)] [string] $GatewayId,
[Parameter(Mandatory)] [string] $DataSourceName,
[Parameter(Mandatory)] [string] $ServerName,
[Parameter(Mandatory)] [string] $DatabaseName,
[Parameter(Mandatory)] [string] $ServiceAccountUsername,
[Parameter(Mandatory)] [SecureString] $ServiceAccountPassword
)
# Build connection details JSON
$connectionDetails = @{
server = $ServerName
database = $DatabaseName
} | ConvertTo-Json -Compress
# Convert SecureString to plain text for the API payload
# Note: credentials are transmitted over HTTPS and encrypted at rest
$plainPassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto(
[System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($ServiceAccountPassword)
)
# Build credentials object
$credentialDetails = @{
credentialType = "Basic"
basicCredentials = @{
username = $ServiceAccountUsername
password = $plainPassword
}
encryptedConnection = "Encrypted"
encryptionAlgorithm = "RSA-OAEP"
privacyLevel = "Organizational"
useCallerAADIdentity = $false
}
$requestBody = @{
dataSourceType = "Sql"
connectionDetails = $connectionDetails
datasourceName = $DataSourceName
credentialDetails = $credentialDetails
} | ConvertTo-Json -Depth 5
$url = "gateways/$GatewayId/datasources"
try {
$response = Invoke-PowerBIRestMethod -Url $url -Method Post -Body $requestBody
$result = $response | ConvertFrom-Json
Write-Host "✓ Data source '$DataSourceName' created with ID: $($result.id)"
return $result
}
catch {
Write-Error "Failed to create data source: $_"
throw
}
}
# Usage — connect first
Connect-PowerBIServiceAccount -ServicePrincipal `
-Credential (Get-Credential) `
-Tenant "your-tenant-id"
$gatewayId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" # From your inventory
New-PowerBIGatewayDataSource `
-GatewayId $gatewayId `
-DataSourceName "FinanceDW-Production" `
-ServerName "sqlprod01.contoso.local" `
-DatabaseName "FinanceDW" `
-ServiceAccountUsername "CONTOSO\svc_powerbi_finance" `
-ServiceAccountPassword (Read-Host -AsSecureString "Enter password")
Before you can manage data sources at scale, you need visibility into what exists. Run this script regularly — perhaps weekly — and store the output in a SharePoint list or Azure SQL table that your governance team can review.
function Get-AllGatewayDataSources {
# Get all gateways this account can see
$gatewayResponse = Invoke-PowerBIRestMethod -Url "gateways" -Method Get |
ConvertFrom-Json
$inventory = @()
foreach ($gateway in $gatewayResponse.value) {
Write-Host "Scanning gateway: $($gateway.name) ($($gateway.id))"
$dsResponse = Invoke-PowerBIRestMethod `
-Url "gateways/$($gateway.id)/datasources" `
-Method Get | ConvertFrom-Json
foreach ($ds in $dsResponse.value) {
$inventory += [PSCustomObject]@{
GatewayName = $gateway.name
GatewayId = $gateway.id
DataSourceId = $ds.id
DataSourceName = $ds.datasourceName
DataSourceType = $ds.datasourceType
ConnectionDetails = $ds.connectionDetails
CredentialType = $ds.credentialDetails.credentialType
LastUpdated = $ds.credentialDetails.credentialUpdatedOn
}
}
}
return $inventory
}
$inventory = Get-AllGatewayDataSources
# Export to CSV for governance review
$inventory | Export-Csv -Path "gateway-datasource-inventory-$(Get-Date -Format 'yyyy-MM-dd').csv" `
-NoTypeInformation
# Flag data sources where credentials haven't been rotated in 90+ days
$staleThreshold = (Get-Date).AddDays(-90)
$staleDataSources = $inventory | Where-Object {
$_.LastUpdated -and ([datetime]$_.LastUpdated -lt $staleThreshold)
}
if ($staleDataSources) {
Write-Warning "Found $($staleDataSources.Count) data sources with credentials older than 90 days:"
$staleDataSources | Select-Object GatewayName, DataSourceName, LastUpdated | Format-Table
}
This kind of automated inventory is foundational to the credential rotation policy we'll build later. Without knowing the age of every credential in your environment, you're managing blind.
Key insight
The credentialUpdatedOn field in the data source response is your single most important field for credential rotation governance. Build your rotation alerts around this field, not around your change tickets — change tickets get lost, the API timestamp doesn't.
The question of who can create and manage data sources on a gateway cluster is one of the most contentious in enterprise Power BI governance. IT wants control. Business units want autonomy. Security wants auditability. The good news is that Power BI's data source permission model can accommodate all three if you design it deliberately.
Gateway Admin — Can do everything: add/remove data sources, manage cluster members, see all credentials (not the plaintext passwords, but the metadata), and assign users to data sources. This role should be restricted to your gateway operations team and infrastructure admins. In a mature organization, this is a small group — maybe 2-5 people.
Data Source User — Can use an existing data source for refresh. They cannot see credentials, cannot modify connection details, and cannot see other data sources on the same gateway. This is the right role for dataset owners and workspace admins who need to wire up their datasets to the gateway.
Data Source Owner (also called "Can reshare") — A data source user who can also grant other users access to that data source. This is the delegation model that lets you empower business unit data owners without giving them Gateway Admin rights.
Here's the pattern that works well in practice. Your gateway operations team creates and maintains the data source definitions — they control the server names, database names, and credentials. Business unit data owners are granted Data Source Owner rights on the specific sources their teams need. They can then onboard new dataset developers in their business unit without filing tickets with IT.
function Grant-DataSourceAccess {
param(
[Parameter(Mandatory)] [string] $GatewayId,
[Parameter(Mandatory)] [string] $DataSourceId,
[Parameter(Mandatory)] [string] $UserEmailAddress,
[Parameter(Mandatory)] [ValidateSet("Admin", "Read", "ReadReshare")]
[string] $Role
)
$requestBody = @{
emailAddress = $UserEmailAddress
role = $Role
# "ReadReshare" = Data Source Owner who can delegate
# "Read" = Data Source User (refresh only)
# "Admin" = Data Source Admin (avoid granting broadly)
} | ConvertTo-Json
$url = "gateways/$GatewayId/datasources/$DataSourceId/users"
$response = Invoke-PowerBIRestMethod -Url $url -Method Post -Body $requestBody
Write-Host "✓ Granted $Role access to $UserEmailAddress on data source $DataSourceId"
}
# Grant the Finance BI Lead "reshare" rights on the FinanceDW data source
# They can now onboard Finance team dataset developers themselves
Grant-DataSourceAccess `
-GatewayId "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
-DataSourceId "yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy" `
-UserEmailAddress "finance.bi.lead@contoso.com" `
-Role "ReadReshare"
This pattern connects naturally to your broader Power BI governance model. Gateway data source permissions should mirror your workspace permission structure — if someone is a workspace admin for the Finance workspace, they should own the data sources that Finance workspace datasets use.
Warning
Be careful with ReadReshare (Data Source Owner). A person with this right can grant any Power BI user access to that data source — including users outside the intended business unit. If your on-premises data source contains sensitive financial or HR data, ensure that data source owners understand their responsibility and that your tenant settings and sensitivity labels create guardrails around dataset distribution.
Delegate, but verify. Use the REST API to periodically audit who has access to each data source and compare against your expected access list.
function Get-DataSourceUsers {
param(
[string] $GatewayId,
[string] $DataSourceId
)
$url = "gateways/$GatewayId/datasources/$DataSourceId/users"
$response = Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json
return $response.value | Select-Object @(
'emailAddress',
'datasourceAccessRight',
@{Name='PrincipalType'; Expression={$_.principalType}}
)
}
# Run against your full inventory and flag unexpected access
$inventory = Get-AllGatewayDataSources # From earlier script
$accessReport = @()
foreach ($ds in $inventory) {
$users = Get-DataSourceUsers -GatewayId $ds.GatewayId -DataSourceId $ds.DataSourceId
foreach ($user in $users) {
$accessReport += [PSCustomObject]@{
GatewayName = $ds.GatewayName
DataSourceName = $ds.DataSourceName
UserEmail = $user.emailAddress
AccessRight = $user.datasourceAccessRight
PrincipalType = $user.PrincipalType
}
}
}
$accessReport | Export-Csv -Path "gateway-access-report-$(Get-Date -Format 'yyyy-MM-dd').csv" `
-NoTypeInformation
Schedule this script monthly and pipe the output to your identity governance team for review. It takes about ten minutes to review and can catch permission sprawl before it becomes a security finding.
Credential rotation is the operational discipline that most organizations either avoid (too risky) or perform haphazardly (too ad hoc). Both approaches create risk. Avoid it long enough and you'll eventually fail an audit or face a compromised service account. Do it haphazardly and you'll cause refresh failures at 3 a.m. when the wrong password gets updated in the wrong order.
A solid credential rotation policy has three components: a schedule, a procedure, and a fallback plan.
For enterprise Power BI gateway credentials, a 90-day rotation cycle is a reasonable default that balances security requirements with operational overhead. Some regulated industries require 30-60 days. Build your schedule around your organization's password policy, not around convenience.
Critically, your rotation calendar should stagger across data sources. If you have 40 data sources and rotate all of them on the first Monday of the quarter, you're creating a massive blast radius if something goes wrong. Instead, rotate 5-10 data sources per week throughout the rotation window.
Here's the sequence that prevents refresh failures during rotation:
Step 1: Pre-rotation verification. Before changing anything, confirm the current credentials are working. Check the last successful refresh time for datasets using this data source.
Step 2: Update the password in Active Directory (or your identity provider). This is the action that "breaks" the old password. Do this at a low-traffic time — typically between 1 a.m. and 5 a.m.
Step 3: Update the credential in the Power BI gateway using the REST API immediately after the AD change. The window between steps 2 and 3 is your risk window — any refresh that runs during this window will fail. This is why you do it at low-traffic times and why you have this scripted rather than manual.
Step 4: Trigger a test refresh. Don't wait for the next scheduled refresh. Immediately trigger a manual refresh of one dataset using this data source and verify it succeeds.
Step 5: Document the rotation. Update your credential inventory with the new rotation date.
function Update-GatewayDataSourceCredentials {
param(
[Parameter(Mandatory)] [string] $GatewayId,
[Parameter(Mandatory)] [string] $DataSourceId,
[Parameter(Mandatory)] [string] $Username,
[Parameter(Mandatory)] [SecureString] $NewPassword,
[ValidateSet("Windows", "Basic", "OAuth2")]
[string] $CredentialType = "Windows"
)
$plainPassword = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto(
[System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($NewPassword)
)
# Credential type determines the payload structure
$credentialDetails = switch ($CredentialType) {
"Windows" {
@{
credentialType = "Windows"
windowsCredentials = @{
username = $Username
password = $plainPassword
}
encryptedConnection = "Encrypted"
encryptionAlgorithm = "RSA-OAEP"
privacyLevel = "Organizational"
useCallerAADIdentity = $false
}
}
"Basic" {
@{
credentialType = "Basic"
basicCredentials = @{
username = $Username
password = $plainPassword
}
encryptedConnection = "Encrypted"
encryptionAlgorithm = "RSA-OAEP"
privacyLevel = "Organizational"
useCallerAADIdentity = $false
}
}
}
$requestBody = @{
credentialDetails = $credentialDetails
} | ConvertTo-Json -Depth 5
$url = "gateways/$GatewayId/datasources/$DataSourceId"
try {
Invoke-PowerBIRestMethod -Url $url -Method Patch -Body $requestBody
Write-Host "✓ Credentials updated for data source $DataSourceId at $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"
# Log to your audit trail
$auditEntry = [PSCustomObject]@{
Timestamp = Get-Date -Format 'o'
GatewayId = $GatewayId
DataSourceId = $DataSourceId
Username = $Username
Action = "CredentialRotation"
PerformedBy = (Get-PowerBIAccessToken | Select-Object -ExpandProperty userId)
}
$auditEntry | Export-Csv -Path "credential-rotation-audit.csv" -Append -NoTypeInformation
}
catch {
Write-Error "Credential update FAILED for $DataSourceId. Original error: $_"
# At this point, the AD password has changed but the gateway hasn't been updated.
# Send an immediate alert — do NOT exit silently.
Send-MailMessage -To "gateway-ops@contoso.com" `
-Subject "CRITICAL: Gateway credential rotation failed for $DataSourceId" `
-Body "AD password was changed but gateway update failed. All refreshes using data source $DataSourceId will fail until manually corrected. Error: $_" `
-SmtpServer "smtp.contoso.local"
throw
}
}
Warning
The window between changing the Active Directory password and updating the gateway credential is your highest-risk moment. If a dataset refresh runs during this window, it will fail and may trigger failure notifications to dataset owners. Coordinate with your scheduled refresh configuration — pause scheduled refreshes for datasets using this data source before rotating, then re-enable them after a successful test refresh.
For organizations running at scale, the manual "get password from vault, run script" approach doesn't hold up. The right architecture integrates directly with Azure Key Vault, where secrets are rotated by an automated process and the gateway update is triggered by a Key Vault event.
Here's the architecture pattern:
Azure Key Vault
└── Secret: svc_powerbi_finance (auto-rotated by Key Vault rotation policy)
└── On rotation event → triggers Azure Automation Runbook
└── Runbook:
1. Retrieves new secret value from Key Vault
2. Updates Active Directory password (via AD connector)
3. Calls Update-GatewayDataSourceCredentials
4. Triggers test refresh
5. Sends success/failure notification
This pattern eliminates the human-in-the-loop for routine rotations, reduces the risk window to seconds (the runbook executes immediately on the Key Vault rotation event), and creates a full audit trail in Key Vault and Azure Monitor.
The runbook itself uses the same PowerShell functions shown above — the difference is that the $NewPassword parameter is populated from Get-AzKeyVaultSecret rather than from a human-typed value.
Reactive gateway monitoring — waiting for refresh failures before investigating — is the wrong model. By the time a refresh fails, users are already impacted. You want to detect gateway health degradation before it causes visible failures.
Gateway member status — Each member reports Online or Offline. Any member that goes Offline unexpectedly should trigger an immediate alert, even if the cluster is still functional (the remaining members carry the load, but your redundancy is now reduced).
CPU and memory on gateway machines — The gateway software is a CPU-intensive process, especially during concurrent refresh operations. High CPU (consistently above 80%) during refresh windows indicates you need to add cluster members or reschedule refreshes to spread load. Use Windows Performance Monitor or Azure Monitor with the Log Analytics agent.
Mashup Engine pool exhaustion — The gateway uses a pool of mashup engine processes to handle parallel queries. When the pool is exhausted, queries queue and eventually time out. Look for event log warnings with event ID 10100 from the Microsoft.Mashup.Container.Loader source.
Latency between gateway and data source — The gateway logs the round-trip time for data source queries. Sudden increases in this latency indicate network issues between the gateway server and the on-premises database — not a Power BI problem, but one that manifests as slow refresh times.
Tip
Enable gateway machine logging to Azure Monitor Logs. The gateway writes detailed diagnostic logs to %ProgramData%\Microsoft\On-premises data gateway\GatewayLogs. With the Log Analytics agent configured, these logs flow into your Azure workspace where you can build dashboards and alerts. This integrates naturally with the monitoring Power BI performance approach for end-to-end visibility.
There's something satisfying about monitoring your Power BI infrastructure with Power BI itself. Use the gateway's built-in performance counters to build a simple health dashboard:
# Export recent gateway activity for a health dashboard dataset
function Get-GatewayDataSourceStatus {
param([string] $GatewayId)
$url = "gateways/$GatewayId/datasources"
$response = Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json
return $response.value | ForEach-Object {
[PSCustomObject]@{
DataSourceId = $_.id
DataSourceName = $_.datasourceName
DataSourceType = $_.datasourceType
ConnectionStatus = $_.connectionDetails
CredentialType = $_.credentialDetails.credentialType
LastCredUpdate = $_.credentialDetails.credentialUpdatedOn
DaysSinceRotation = if ($_.credentialDetails.credentialUpdatedOn) {
([datetime]::Now - [datetime]$_.credentialDetails.credentialUpdatedOn).Days
} else { $null }
}
}
}
Feed this output into a simple Power BI report with conditional formatting: green for data sources rotated within 60 days, yellow for 61-90 days, red for over 90 days. Your gateway operations team gets a visual runway on upcoming rotation work rather than a surprise audit finding.
In this exercise, you'll simulate a production credential rotation event using a test gateway cluster. If you don't have a full on-premises environment, you can follow along using a gateway connected to a local SQL Server instance.
Setup: Ensure you have a gateway cluster with at least one data source registered using Windows authentication (a SQL Server data source works well for this exercise).
Part 1: Inventory your data sources
Connect-PowerBIServiceAccountGet-AllGatewayDataSources function from earlier in this lessonLastUpdated timestamp for the credentialPart 2: Document the before state
Using the Power BI service, navigate to a dataset that uses your test data source and trigger a manual refresh. Confirm it succeeds and note the timestamp.
Part 3: Simulate credential rotation
Update-GatewayDataSourceCredentials targeting your test data sourcePart 4: Verify the after state
Get-AllGatewayDataSources and confirm the LastUpdated timestamp has changedPart 5: Access control audit
Get-DataSourceUsers for your test data sourceGrant-DataSourceAccess to add a test user with Read rightsGet-DataSourceUsers again and confirm the new user appearsDELETE REST call to gateways/{gatewayId}/datasources/{datasourceId}/users/{userId}Mistake 1: Gateway cluster members can't see each other's data sources after adding a new member
This isn't actually a problem — cluster members don't need to see each other. Data sources are registered at the cluster level in the Power BI service, not on individual machines. If a data source isn't appearing for a newly added member, verify the member shows as Online in Manage Gateways and check that the network firewall allows the gateway machine to reach the data source.
Mistake 2: Credential update via REST API succeeds but refresh still fails
Check the credential type. If your data source was originally configured with Windows authentication and you're sending a Basic credentials payload (or vice versa), the API may accept the update but the gateway will reject the credentials when attempting a connection. Always match your credential type to the original data source configuration.
Mistake 3: Gateway goes Offline after a Windows Update restart but doesn't come back automatically
The gateway service (On-premises data gateway service) may be configured for Manual start rather than Automatic. On every cluster member, verify the service is set to Automatic (Delayed Start) in Windows Services. This should be part of your server build checklist.
Mistake 4: Different datasets see inconsistent data, refreshing fine some runs and failing others
This is the classic symptom of mismatched cluster members — specifically, one member that can reach the data source and one that can't, with random routing hitting both. Pull the gateway diagnostic logs from %ProgramData%\Microsoft\On-premises data gateway\GatewayLogs on each member and compare the connection attempt timestamps against your refresh failure timestamps. The member that's failing will show explicit TCP connection errors.
Mistake 5: Service account locked out during rotation
If your AD has an account lockout policy (it should), changing the AD password before the gateway credential causes a brief window where the old password fails. If a refresh runs during that window, it counts as a failed authentication attempt. If you have multiple datasets using the same data source refreshing on tight schedules, you can accumulate enough failed attempts to trigger lockout. The solution is either: (a) temporarily disable the data source during rotation, or (b) confirm your lockout threshold is high enough to survive one failed attempt during the rotation window.
Note
Service account lockouts from Power BI refresh failures are surprisingly common and often difficult to diagnose because the lockout event shows up in AD but the cause (a Power BI refresh) isn't immediately obvious. If you're seeing unexplained account lockouts on your Power BI service accounts, check the gateway diagnostic logs for connection errors around the same timestamps as the lockout events.
Mistake 6: Attempting to use a personal Microsoft account as a gateway service account
The gateway software will accept a personal Microsoft account during installation, but this creates a support nightmare. When that person leaves, you can't transfer gateway ownership. Personal accounts also don't support service principal authentication patterns. Always use a dedicated organizational account (svc-pbi-gateway@contoso.com style) with a non-expiring password policy (but with manual rotation on a schedule — non-expiring doesn't mean never change).
You've now covered the full lifecycle of enterprise gateway management: designing a cluster topology for availability and performance, automating data source provisioning and access control via the REST API, delegating data source ownership responsibly, and implementing credential rotation that's both systematic and safe.
The key principles to carry forward:
For your next steps, consider connecting this gateway management foundation to your broader deployment pipeline. If you're using Power BI Deployment Pipelines to promote datasets from Dev through Test to Production, the gateway data source binding needs to change at each stage — your automation scripts can handle that binding update as part of the deployment pipeline execution. Similarly, if you're working with Power BI Dataflows for centralized ETL, those dataflows also bind to gateway data sources and benefit from the same credential rotation and delegation patterns described here.
Finally, if your governance requirements include data source lineage — knowing exactly which workspaces, datasets, and dataflows depend on a particular gateway data source — explore workspace-level lineage and impact analysis. That capability lets you answer the critical question before any infrastructure change: "if I take this data source offline, what breaks?"
Enterprise Power BI