A Power BI On-Premises Data Gateway is the bridge between your on-premise data sources such as SQL Server, PostgreSQL or Oracle and Power BI in the cloud. In this article I show you how to fully automate the gateway installation on a Windows Server, including driver installation for PostgreSQL and Oracle.

Install-PowerBIGateway.ps1
Full PowerShell automation script — MIT licensed, ready to use

View on GitHub →

What is a Power BI On-Premises Data Gateway?

If you want to refresh Power BI reports based on data stored locally (in your data center or on a VM), you need a gateway. The gateway runs as a Windows service on a server and establishes a secure connection to the Power BI cloud without requiring you to open inbound firewall ports.

Typical scenarios where you need a gateway:

  • Data lives in an on-premise SQL Server or Oracle database
  • You run PostgreSQL on AWS RDS (with private connectivity)
  • Reports need to refresh automatically overnight
  • Multiple teams share the same gateway via a gateway cluster

A gateway cluster provides high availability: if one server goes down, the other gateways take over. Always configure at least two gateways per cluster in production environments.

Prerequisites

Make sure you have the following ready before you start:

  • Windows Server 2016 or newer (2019 or 2022 recommended)
  • PowerShell 5.1+ (PowerShell 7 is installed automatically by the script)
  • Internet access (for downloads from Microsoft, Chocolatey and optionally GitHub/NuGet)
  • Administrator rights on the server
  • Azure AD service principal with Power BI Gateway Admin permissions: your TenantID, ApplicationID and ClientSecret
  • Recovery key: at least 8 characters. Store it safely! You will need it for recovery or reinstallation.

Installation steps – fully automated

The installation script runs through seven steps in the correct order. Below I explain each step.

Step 1 – Chocolatey package manager

Chocolatey is the Windows equivalent of apt or brew: a software package manager. The script installs Chocolatey automatically if it is not already present:

Set-ExecutionPolicy Bypass -Scope Process -Force
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
Invoke-Expression ((New-Object Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))

After installation, the PATH variable is reloaded so that choco is immediately available in the current session.

Step 2 – Prerequisites: 7-Zip, .NET Framework 4.8, PowerShell 7

Three prerequisites are installed via Chocolatey:

  • 7-Zip – for extracting archives
  • .NET Framework 4.8 – required by the gateway and various drivers
  • PowerShell 7 – the configuration script (set-datagateway.ps1) runs in PS7 because of the DataGateway module
choco install 7zip dotnetfx powershell-core --yes --no-progress

Step 3 – Import AWS RDS certificates

When the gateway connects to a PostgreSQL database on AWS RDS, the AWS certificate must be trusted. The script downloads the certificate bundle directly from AWS and imports it into the Windows Certificate Store:

$certUrl = "https://truststore.pki.rds.amazonaws.com/eu-west-1/eu-west-1-bundle.p7b"
Invoke-WebRequest -Uri $certUrl -OutFile $certDest -UseBasicParsing
Import-Certificate -FilePath $certDest -CertStoreLocation Cert:LocalMachineRoot

This prevents SSL errors when connecting to RDS instances that use the newer rds-ca-rsa2048-g1 or rds-ca-ecc384-g1 certificates.

Step 4 – Install the Power BI Gateway

The gateway installer is downloaded from Microsoft and installed silently:

Start-Process -FilePath $installerPath `
    -ArgumentList "/quiet /norestart ACCEPTEULA=1" `
    -Wait -PassThru

After installation, the script waits until the PBIEgwService Windows service is actually running (polling at 5-second intervals, maximum 2 minutes). This is important: the gateway service starts asynchronously and is only ready for configuration once the service is active.

Step 5 – Configure the gateway via set-datagateway.ps1

The actual gateway configuration – linking to a tenant, registering in a cluster, setting the recovery key – is handled via Microsoft’s DataGateway PowerShell module. This requires PowerShell 7.

Sensitive values are passed as environment variables so they are never visible as command-line arguments in process listings:

$env:PBI_TENANT_ID     = $TenantID
$env:PBI_CLIENT_ID     = $ApplicationID
$env:PBI_CLIENT_SECRET = $ClientSecret
$env:PBI_CLUSTER_NAME  = $ClusterName
$env:PBI_SERVER_NAME   = $GatewayName
$env:PBI_RECOVERY_KEY  = $RecoveryKey

& $pwsh -File "set-datagateway.ps1"

# Clean up immediately after use
Remove-Item "env:PBI_CLIENT_SECRET" -ErrorAction SilentlyContinue

Step 6 – Npgsql PostgreSQL driver (optional)

The Power BI Gateway requires a specific version of Npgsql registered in the GAC (Global Assembly Cache). The script installs version 4.0.10 via MSI and verifies registration in both the GAC and machine.config:

Start-Process msiexec.exe `
    -ArgumentList "/i `"$npgsqlPath`" /quiet /norestart ADDLOCAL=ALL" `
    -Wait -PassThru

Use the -InstallPostgres switch to enable this step.

Step 7 – Oracle.ManagedDataAccess driver (optional)

For Oracle connections, the script installs the managed driver via NuGet and automatically writes a tnsnames.ora configuration file:

Install-Package Oracle.ManagedDataAccess -Source "nuget.org" -Scope CurrentUser -Force

# Create tnsnames.ora
@"
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = $OracleHost)(PORT = $OraclePort))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = $OracleService)
    )
  )
"@ | Set-Content -Path $tnsFile -Encoding UTF8

[Environment]::SetEnvironmentVariable("TNS_ADMIN", $TnsAdminPath, "Machine")

Use -InstallOracle -OracleHost "your-oracle-host" to enable this step.

Running the script

Start PowerShell as Administrator and run the script:

.Install-PowerBIGateway.ps1 `
    -TenantID      "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -ApplicationID "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx" `
    -ClientSecret  "your-client-secret" `
    -ClusterName   "prd-pbi-cluster" `
    -GatewayName   "prd-pbi-gw01" `
    -RecoveryKey   "Your32CharRecoveryKey!!" `
    -RegionKey     "northeurope" `
    -InstallPostgres

To also install the Oracle driver:

.Install-PowerBIGateway.ps1 `
    -TenantID      "..." `
    -ApplicationID "..." `
    -ClientSecret  "..." `
    -ClusterName   "prd-pbi-cluster" `
    -GatewayName   "prd-pbi-gw01" `
    -RecoveryKey   "..." `
    -InstallOracle `
    -OracleHost    "oracle.yourcompany.local" `
    -OracleService "PROD"

After installation – what to verify

The script prints a summary at the end:

========================================
  Installation complete
========================================

  Gateway version  : 3000.226.8
  Service status   : Running
  PostgreSQL driver: installed
  Oracle driver    : skipped

Then check in the Power BI Admin Portal (app.powerbi.com/admin-portal/gateways) that the gateway is visible and shows a status of “Online”.

Production tips

  • Minimum two gateways – run the script on a second server with the same ClusterName for high availability
  • Store the recovery key safely – use Azure Key Vault or a password manager. Without this key you cannot recover the gateway
  • Disable AutoUpdate in production (-AutoUpdate $false) and schedule updates during a change window
  • Service account – run PBIEgwService under a dedicated service account, not SYSTEM
  • Monitoring – the gateway logs to C:WindowsServiceProfilesPBIEgwServiceAppDataLocalMicrosoftOn-premises data gateway. Connect this to Datadog or Splunk for proactive monitoring

Conclusion

With this script you can deploy a complete Power BI Gateway environment in one go, repeatably and without manual steps. All steps are idempotent: run the script again and it skips existing installations cleanly.