Power BI Gateway allows secure data transfer between on-premises data sources and Power BI services. Setting up Power BI Gateway connections using PowerShell automates the process of managing gateways, adding data sources, and configuring connections efficiently.
Step 1: Prerequisites
1. Install the Power BI PowerShell Module
Ensure you have the Power BI PowerShell module installed. If not, install it using:
Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser -Force
Power BI PowerShell module installed!
2. Sign In to Power BI
Run the following command to authenticate:
Connect-PowerBIServiceAccount
You will be prompted to log in with your Power BI admin account.
Authentication successful!
Step 2: List Existing Gateways
To check if any gateways are already set up:
Get-PowerBIDataGateway
Example Output
Id Name GatewayType
------------------------------------ ---------------------- ------------
abcd1234-5678-90ef-ghij-klmnopqrstuv On-Premises Gateway Personal
efgh5678-1234-ijkl-mnop-qrstuvwxyzab Enterprise Gateway Standard
Existing gateways are listed!
Step 3: Retrieve a Specific Gateway ID
To find details of a specific gateway, use:
$gatewayName = "On-Premises Gateway"
$gateway = Get-PowerBIDataGateway | Where-Object { $_.Name -eq $gatewayName }
$gateway.Id
Gateway ID retrieved successfully!
Step 4: Add a New Data Source to a Gateway
To add a SQL Server data source:
# Define variables
$gatewayId = "abcd1234-5678-90ef-ghij-klmnopqrstuv"
$dataSourceName = "SQL Server Data Source"
$dataSourceType = "Sql"
$serverName = "your-sql-server-name"
$databaseName = "your-database-name"
$username = "your-username"
$password = ConvertTo-SecureString "your-password" -AsPlainText -Force
# Create credential object
$credential = New-Object PSCredential ($username, $password)
# Add data source
Add-PowerBIDataGatewayDataSource -GatewayId $gatewayId -DataSourceName $dataSourceName -DataSourceType $dataSourceType -ConnectionDetails @{
"server" = $serverName
"database" = $databaseName
} -CredentialType Basic -Credential $credential
New data source added to the gateway!
Step 5: List All Data Sources in a Gateway
To check existing data sources:
$gatewayId = "abcd1234-5678-90ef-ghij-klmnopqrstuv"
Get-PowerBIDataGatewayDataSource -GatewayId $gatewayId | Select-Object Id, Name, DataSourceType
Example Output
Id Name DataSourceType
------------------------------------ ------------------------ ---------------
1234abcd-5678-ef90-ghij-klmnopqrstuv SQL Server Data Source Sql
5678efgh-1234-ijkl-mnop-qrstuvwxyzab SharePoint Data Source SharePoint
Data sources listed successfully!
Step 6: Update a Data Source
If credentials or settings need updating:
$dataSourceId = "1234abcd-5678-ef90-ghij-klmnopqrstuv"
# Update credentials
Set-PowerBIDataGatewayDataSource -GatewayId $gatewayId -DataSourceId $dataSourceId -CredentialType Basic -Credential $credential
Data source updated successfully!
Step 7: Delete an Unused Data Source
If a data source is no longer needed, remove it:
Remove-PowerBIDataGatewayDataSource -GatewayId $gatewayId -DataSourceId $dataSourceId -Confirm:$false
Data source deleted successfully!
Step 8: Assign a Gateway to a Dataset
To link a dataset with a gateway connection, run:
$datasetId = "abcd5678-ef90-ghij-klmnopqrstuv"
$bindingId = "binding1234"
New-PowerBIDataGatewayBinding -GatewayId $gatewayId -DatasetId $datasetId -BindingId $bindingId
Dataset successfully linked to the gateway!
Step 9: Monitor Gateway Status
To check the status of a Power BI gateway:
Get-PowerBIDataGateway | Select-Object Id, Name, GatewayStatus
Example Output
Id Name GatewayStatus
------------------------------------ ---------------------- ------------
abcd1234-5678-90ef-ghij-klmnopqrstuv On-Premises Gateway Online
efgh5678-1234-ijkl-mnop-qrstuvwxyzab Enterprise Gateway Offline
Gateway status retrieved successfully!
Step 10: Automate Gateway Monitoring
To automate gateway status checks, create a script:
# Connect to Power BI
Connect-PowerBIServiceAccount
# Check gateway status
$gateways = Get-PowerBIDataGateway
foreach ($gateway in $gateways) {
if ($gateway.GatewayStatus -ne "Online") {
Write-Host " Gateway $($gateway.Name) is offline!"
} else {
Write-Host " Gateway $($gateway.Name) is online!"
}
}
Save this as MonitorGatewayStatus.ps1 and schedule it with Task Scheduler.
Automated gateway monitoring is set up!