On-Premises Data Gateway: Configuration and Management

On-Premises Data Gateway: Configuration and Management

Introduction

The On-Premises Data Gateway serves as the critical bridge between Microsoft cloud services (Power BI, Power Apps, Power Automate, Azure Logic Apps) and on-premises data sources. For enterprises with hybrid cloud architectures, the gateway is often the linchpin enabling business intelligence and automation while maintaining data sovereignty and compliance requirements.

Despite its importance, gateway misconfigurations lead to refresh failures, performance bottlenecks, security vulnerabilities, and frustrated end users. A poorly deployed gateway can become a single point of failure, bringing down dozens of reports and workflows.

This comprehensive guide covers enterprise-grade gateway deployment from architecture planning through high-availability clustering, performance tuning, monitoring automation, security hardening, Kerberos configuration, troubleshooting, and operational best practices. You'll learn how to build resilient, scalable, and secure gateway infrastructure that supports mission-critical workloads.

Prerequisites

• Windows Server 2016 or later (2019/2022 recommended)
• .NET Framework 4.7.2 or later
• Administrator access to gateway servers
• Power BI Pro or Premium license
• Network connectivity to on-premises data sources
• (Optional) Active Directory access for Kerberos configuration
• (Optional) PowerShell 5.1+ for automation

Gateway Architecture Deep Dive

Component Architecture

┌─────────────────────────────────────────────────────────────────┐
│                    CLOUD SERVICES                               │
├─────────────────────────────────────────────────────────────────┤
│  Power BI Service │ Power Apps │ Power Automate │ Logic Apps   │
└──────────────────┬──────────────────────────────────────────────┘
                   │ HTTPS (Outbound only from gateway)
                   │
┌──────────────────▼──────────────────────────────────────────────┐
│              AZURE SERVICE BUS (Relay)                          │
│  - Queue management                                             │
│  - Message relay between cloud and gateway                      │
│  - No inbound ports required on-premises                        │
└──────────────────┬──────────────────────────────────────────────┘
                   │ Outbound HTTPS (443)
                   │ Azure Service Bus Relay Protocol
┌──────────────────▼──────────────────────────────────────────────┐
│         ON-PREMISES DATA GATEWAY                                │
├─────────────────────────────────────────────────────────────────┤
│  Gateway Windows Service (PBIEgwService.exe)                    │
│  - Polls Azure Service Bus for requests                         │
│  - Executes queries against data sources                        │
│  - Returns results to cloud via Service Bus                     │
│  - Manages connection pooling and caching                       │
│  - Logs to Event Viewer and local files                         │
│                                                                  │
│  Installed Components:                                          │
│  - Gateway Engine                                               │
│  - Mashup Engine (Power Query)                                  │
│  - Data Connector Drivers                                       │
│  - TLS/SSL Certificates                                         │
└──────────────────┬──────────────────────────────────────────────┘
                   │ Native Protocols (SQL, Oracle, SAP, etc.)
                   │
┌──────────────────▼──────────────────────────────────────────────┐
│              ON-PREMISES DATA SOURCES                           │
├─────────────────────────────────────────────────────────────────┤
│  SQL Server │ Oracle │ SAP │ File Shares │ Web APIs │ More     │
└─────────────────────────────────────────────────────────────────┘

Key Architectural Principles

1. Outbound-Only Connections:

• Gateway initiates all connections (outbound to Azure Service Bus)
• No inbound firewall rules required
• Reduced security risk (no DMZ placement needed)

2. Service Bus Relay Pattern:

• Cloud services queue requests in Azure Service Bus
• Gateway polls Service Bus every few seconds
• Requests/responses flow through encrypted relay
• No direct connection between cloud and on-premises network

3. Connection Pooling:

• Gateway maintains connection pools to data sources
• Reuses connections across queries for performance
• Configurable pool sizes and timeouts

4. Multi-Tenancy:

• Single gateway can serve multiple workspaces
• Workspace admins manage data source credentials
• Gateway admins control gateway-level configuration

Enterprise Installation Guide

Pre-Installation Planning

Infrastructure Checklist:

☐ Server Specifications:
  - Dedicated Windows Server (physical or VM)
  - CPU: 8+ cores (for high-throughput scenarios)
  - RAM: 16+ GB (32GB for heavy workloads)
  - Disk: 50+ GB free space (for logs and temp files)
  - Network: Gigabit NIC, low latency to data sources

☐ Network Requirements:
  - Outbound HTTPS (443) to *.servicebus.windows.net
  - Outbound HTTPS (443) to *.powerbi.com
  - Outbound HTTPS (443) to *.analysis.windows.net
  - Access to on-premises data sources (SQL: 1433, Oracle: 1521, etc.)
  - DNS resolution for cloud services

☐ Security & Compliance:
  - Least-privilege service account (domain account preferred)
  - TLS 1.2 enforced
  - Antivirus exclusions configured
  - Firewall rules documented
  - Change management approval obtained

☐ High Availability Planning:
  - Minimum 2 gateway servers for clustering
  - Load balancer (optional, for custom scenarios)
  - Shared configuration storage plan
  - Disaster recovery documentation

Installation Steps

# Step 1: Download latest gateway installer
$gatewayUrl = "https://download.microsoft.com/download/D/A/1/DA1FDDB8-6DA8-4F50-B4D0-18019591E182/GatewayInstall.exe"
$installerPath = "$env:TEMP\GatewayInstall.exe"

Invoke-WebRequest -Uri $gatewayUrl -OutFile $installerPath

# Step 2: Install gateway silently with logging
$logPath = "C:\Logs\GatewayInstall.log"
$installArgs = @(
    "-quiet",
    "-norestart",
    "-log", $logPath
)

Start-Process -FilePath $installerPath -ArgumentList $installArgs -Wait -NoNewWindow

Write-Host "Gateway installed. Log: $logPath"

# Step 3: Verify installation
$gatewayService = Get-Service -Name "PBIEgwService" -ErrorAction SilentlyContinue

if ($gatewayService -and $gatewayService.Status -eq "Running") {
    Write-Host "✅ Gateway service is running" -ForegroundColor Green
} else {
    Write-Host "❌ Gateway service not found or not running" -ForegroundColor Red
}

# Step 4: Check gateway version
$gatewayExe = "C:\Program Files\On-premises data gateway\EnterpriseGatewayConfigurator.exe"
if (Test-Path $gatewayExe) {
    $version = (Get-Item $gatewayExe).VersionInfo.FileVersion
    Write-Host "Gateway version: $version" -ForegroundColor Cyan
}

Post-Installation Configuration

# Configure Windows service recovery options
sc.exe failure "PBIEgwService" reset= 86400 actions= restart/60000/restart/60000/restart/60000

# Set service recovery to restart after 1 minute on failure
# Reset failure count after 24 hours (86400 seconds)

# Configure service to run under domain account (recommended)
$serviceName = "PBIEgwService"
$domainAccount = "DOMAIN\svc_gateway"
$securePassword = ConvertTo-SecureString "P@ssw0rd" -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($domainAccount, $securePassword)

# Note: Use Set-Service or sc.exe config to change service account
# sc.exe config PBIEgwService obj= "DOMAIN\svc_gateway" password= "P@ssw0rd"

Write-Host "Service recovery configured" -ForegroundColor Green

High Availability Clustering

Gateway Cluster Architecture

┌─────────────────────────────────────────────────────────────┐
│                   Azure Service Bus Relay                    │
└──────────────┬──────────────────────────────┬────────────────┘
               │                              │
       ┌───────▼────────┐            ┌───────▼────────┐
       │  Gateway Node 1│            │  Gateway Node 2│
       │   (Primary)    │            │   (Secondary)  │
       └───────┬────────┘            └───────┬────────┘
               │                              │
   ┌───────────▼──────────────────────────────▼───────────┐
   │         On-Premises Data Sources                      │
   │  SQL Server │ Oracle │ SAP │ File Shares │ APIs      │
   └───────────────────────────────────────────────────────┘

Load Distribution:
- Round-robin by default
- Affinity to specific nodes (optional)
- Automatic failover on node failure
- Health checks every 30 seconds

Creating a Gateway Cluster

# Step 1: Install gateway on primary node (Node 1)
# Complete installation and register gateway via UI or PowerShell

# Step 2: Get recovery key from primary gateway
# Recovery key is required to add nodes to cluster
# Stored securely in key vault or password manager

$recoveryKey = "XXXXX-XXXXX-XXXXX-XXXXX-XXXXX-XXXXX-XXXXX-XXXXX"

# Step 3: Install gateway on secondary node (Node 2)
# During configuration, select "Add to an existing cluster"
# Provide recovery key from primary gateway

# Step 4: Verify cluster membership via PowerShell
# Note: This requires gateway PowerShell module
# Install-Module -Name DataGateway

Import-Module DataGateway

# Login to Power BI service
Login-DataGatewayServiceAccount

# List all gateways
$gateways = Get-DataGatewayCluster
$gateways | Format-Table Name, Id, ClusterStatus

# Get specific cluster details
$clusterName = "Production-Gateway-Cluster"
$cluster = Get-DataGatewayCluster -Name $clusterName

Write-Host "Cluster: $($cluster.Name)"
Write-Host "Members: $($cluster.Members.Count)"
foreach ($member in $cluster.Members) {
    Write-Host "  - $($member.MachineName) | Status: $($member.Status) | Version: $($member.Version)"
}

Cluster Management Best Practices

Cluster Configuration:

☑ Node Count:
  ☐ Minimum: 2 nodes (primary + secondary)
  ☐ Recommended: 3 nodes for critical workloads
  ☐ Maximum: 10 nodes (per cluster)
  ☐ All nodes should have identical specs

☑ Load Distribution:
  ☐ Default: Round-robin across all nodes
  ☐ Optional: Affinity to specific nodes for certain data sources
  ☐ Monitor load distribution (CPU, memory, query count per node)

☑ Maintenance Windows:
  ☐ Schedule updates during low-usage periods
  ☐ Update one node at a time
  ☐ Verify node health before updating next node
  ☐ Maintain at least one healthy node during updates

☑ Failover Testing:
  ☐ Quarterly failover drills
  ☐ Stop gateway service on primary node
  ☐ Verify automatic failover to secondary
  ☐ Monitor failover time (typically < 60 seconds)
  ☐ Document failover procedures

Data Source Configuration

Adding Data Sources

# PowerShell: Add SQL Server data source to gateway

Import-Module DataGateway
Login-DataGatewayServiceAccount

$gatewayId = "gateway-guid"
$dataSourceName = "SQL-ProductionDB"

# Create data source
$dataSourceParams = @{
    GatewayId = $gatewayId
    DataSourceType = "SQL"
    DataSourceName = $dataSourceName
    Server = "sql-server.contoso.com"
    Database = "Production"
    CredentialType = "Windows"  # or "Basic" for SQL auth
}

New-DataGatewayDatasource @dataSourceParams

# Set credentials (done via UI or API)
# Requires encrypted credentials payload

Kerberos Authentication Configuration

# Configure Kerberos constrained delegation for SQL Server

# Step 1: Create Service Principal Name (SPN) for SQL Server
# Run on SQL Server or Domain Controller

setspn -A MSSQLSvc/sql-server.contoso.com:1433 DOMAIN\svc_sqlserver
setspn -A MSSQLSvc/sql-server:1433 DOMAIN\svc_sqlserver

# Verify SPNs
setspn -L DOMAIN\svc_sqlserver

# Step 2: Configure constrained delegation for gateway service account
# In Active Directory Users and Computers:
# 1. Find gateway service account (e.g., DOMAIN\svc_gateway)
# 2. Properties → Delegation tab
# 3. Select "Trust this user for delegation to specified services only"
# 4. Select "Use any authentication protocol" (Kerberos)
# 5. Add services: MSSQLSvc/sql-server.contoso.com, MSSQLSvc/sql-server

# Step 3: Configure gateway for Kerberos
# Edit: C:\Program Files\On-premises data gateway\Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config

# Add to <appSettings> section:
# <add key="ADUserNameLookupProperty" value="sAMAccountName"/>
# <add key="ADUserNameReplacementProperty" value="sAMAccountName"/>
# <add key="KerberosSnDebuggingEnabled" value="true"/>

# Step 4: Restart gateway service
Restart-Service -Name "PBIEgwService"

Write-Host "✅ Kerberos configuration complete" -ForegroundColor Green

Kerberos Troubleshooting

# Verify Kerberos ticket acquisition

# Test 1: Check SPN registration
setspn -Q MSSQLSvc/sql-server.contoso.com

# Should return exactly one SPN registered to SQL service account
# Multiple SPNs = conflict = authentication failure

# Test 2: Request Kerberos ticket manually
$sqlServer = "sql-server.contoso.com"
$serviceType = "MSSQLSvc"

# This should not prompt for credentials if Kerberos is working
sqlcmd -S $sqlServer -E -Q "SELECT @@SERVERNAME"

# Test 3: Examine gateway logs for Kerberos errors
$logPath = "C:\Users\svc_gateway\AppData\Local\Microsoft\On-premises data gateway\Gateway*.log"
Get-Content $logPath -Tail 100 | Select-String "Kerberos|SSPI|Authentication"

# Common Kerberos errors:
# - "The target principal name is incorrect" = SPN missing or duplicate
# - "No authority could be contacted for authentication" = DC connectivity issue
# - "KDC has no support for encryption type" = Encryption mismatch

Performance Optimization

Gateway Resource Monitoring

# Monitor gateway server performance

function Get-GatewayPerformance {
    param(
        [int]$DurationMinutes = 60
    )
    
    $counters = @(
        "\Processor(_Total)\% Processor Time",
        "\Memory\Available MBytes",
        "\Process(PBIEgwService)\Private Bytes",
        "\Process(PBIEgwService)\% Processor Time",
        "\Process(PBIEgwService)\Thread Count"
    )
    
    Write-Host "Collecting gateway performance data for $DurationMinutes minutes..." -ForegroundColor Cyan
    
    $samples = Get-Counter -Counter $counters -SampleInterval 60 -MaxSamples $DurationMinutes
    
    # Calculate statistics
    foreach ($counter in $counters) {
        $values = $samples.CounterSamples | Where-Object { $_.Path -like "*$counter*" } | Select-Object -ExpandProperty CookedValue
        $avg = ($values | Measure-Object -Average).Average
        $max = ($values | Measure-Object -Maximum).Maximum
        
        Write-Host "$counter"
        Write-Host "  Average: $([math]::Round($avg, 2))"
        Write-Host "  Maximum: $([math]::Round($max, 2))"
    }
}

# Run performance monitoring
Get-GatewayPerformance -DurationMinutes 5

Query Performance Tuning

Performance Optimization Checklist:

☑ Data Source Optimization:
  ☐ Add indexes on frequently filtered columns
  ☐ Update statistics on large tables
  ☐ Implement query folding in Power Query
  ☐ Avoid SELECT * queries (specify columns)
  ☐ Use stored procedures for complex logic

☑ Gateway Configuration:
  ☐ Increase connection pool size (default: 10)
    Edit: GatewayCore.dll.config
    <add key="Microsoft.Mashup.Container.NetFX45.MaxConnections" value="20"/>
  
  ☐ Adjust query timeout (default: 30 minutes)
    <add key="QueryTimeout" value="3600"/>
  
  ☐ Enable query result caching
    <add key="EnableQueryResultCache" value="true"/>

☑ Network Optimization:
  ☐ Place gateway server close to data sources (low latency)
  ☐ Use wired gigabit connection (not WiFi)
  ☐ Monitor network utilization during peak times
  ☐ Consider ExpressRoute for Azure hybrid scenarios

☑ Refresh Scheduling:
  ☐ Stagger refresh schedules (avoid concurrent large refreshes)
  ☐ Schedule heavy refreshes during off-peak hours
  ☐ Use incremental refresh for large datasets
  ☐ Monitor refresh queue depth

Connection Pooling Configuration

<!-- Edit: C:\Program Files\On-premises data gateway\Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config -->

<configuration>
  <appSettings>
    <!-- Increase max connections per data source -->
    <add key="Microsoft.Mashup.Container.NetFX45.MaxConnections" value="20"/>
    
    <!-- Increase HTTP request timeout -->
    <add key="HttpRequestTimeout" value="00:30:00"/>
    
    <!-- Enable aggressive connection pooling -->
    <add key="PoolIdleTimeout" value="00:05:00"/>
    
    <!-- Query execution timeout (seconds) -->
    <add key="QueryTimeout" value="3600"/>
  </appSettings>
</configuration>

Monitoring and Alerting

Automated Health Check Script

# Gateway health monitoring script (run via scheduled task every 5 minutes)

function Test-GatewayHealth {
    param(
        [string]$EmailTo = "ops@contoso.com",
        [string]$SmtpServer = "smtp.contoso.com"
    )
    
    $report = @{
        Timestamp = Get-Date
        ServerName = $env:COMPUTERNAME
        Checks = @()
        OverallStatus = "Healthy"
    }
    
    # Check 1: Gateway service running
    $service = Get-Service -Name "PBIEgwService" -ErrorAction SilentlyContinue
    $serviceCheck = @{
        Name = "Gateway Service"
        Status = if ($service -and $service.Status -eq "Running") { "✅ Pass" } else { "❌ Fail" }
        Details = "Status: $($service.Status)"
    }
    $report.Checks += $serviceCheck
    
    if ($serviceCheck.Status -like "*Fail*") {
        $report.OverallStatus = "Unhealthy"
    }
    
    # Check 2: CPU usage
    $cpu = (Get-Counter "\Processor(_Total)\% Processor Time").CounterSamples[0].CookedValue
    $cpuCheck = @{
        Name = "CPU Usage"
        Status = if ($cpu -lt 80) { "✅ Pass" } else { "⚠️ Warning" }
        Details = "$([math]::Round($cpu, 1))%"
    }
    $report.Checks += $cpuCheck
    
    if ($cpu -gt 90) {
        $report.OverallStatus = "Warning"
    }
    
    # Check 3: Available memory
    $memAvailMB = (Get-Counter "\Memory\Available MBytes").CounterSamples[0].CookedValue
    $memCheck = @{
        Name = "Available Memory"
        Status = if ($memAvailMB -gt 2048) { "✅ Pass" } else { "⚠️ Warning" }
        Details = "$([math]::Round($memAvailMB, 0)) MB"
    }
    $report.Checks += $memCheck
    
    if ($memAvailMB -lt 1024) {
        $report.OverallStatus = "Warning"
    }
    
    # Check 4: Gateway log errors (last hour)
    $logPath = "C:\Users\*\AppData\Local\Microsoft\On-premises data gateway\Gateway*.log"
    $logs = Get-ChildItem $logPath | Sort-Object LastWriteTime -Descending | Select-Object -First 1
    
    if ($logs) {
        $recentErrors = Get-Content $logs.FullName -Tail 1000 | 
            Where-Object { $_ -match "ERROR|FATAL" } |
            Where-Object { (Get-Date) - [datetime]::Parse(($_ -split '\|')[0]) -lt (New-TimeSpan -Hours 1) }
        
        $errorCheck = @{
            Name = "Recent Errors"
            Status = if ($recentErrors.Count -eq 0) { "✅ Pass" } else { "⚠️ Warning" }
            Details = "$($recentErrors.Count) errors in last hour"
        }
        $report.Checks += $errorCheck
        
        if ($recentErrors.Count -gt 10) {
            $report.OverallStatus = "Warning"
        }
    }
    
    # Check 5: Disk space
    $disk = Get-WmiObject Win32_LogicalDisk -Filter "DeviceID='C:'"
    $freeSpaceGB = [math]::Round($disk.FreeSpace / 1GB, 2)
    $diskCheck = @{
        Name = "Disk Space (C:)"
        Status = if ($freeSpaceGB -gt 10) { "✅ Pass" } else { "⚠️ Warning" }
        Details = "$freeSpaceGB GB free"
    }
    $report.Checks += $diskCheck
    
    if ($freeSpaceGB -lt 5) {
        $report.OverallStatus = "Warning"
    }
    
    # Generate report
    $emailBody = @"
<html>
<head><style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #4CAF50; color: white; }
.pass { color: green; }
.warning { color: orange; }
.fail { color: red; }
</style></head>
<body>
<h2>Gateway Health Report - $($report.ServerName)</h2>
<p><strong>Timestamp:</strong> $($report.Timestamp)</p>
<p><strong>Overall Status:</strong> <span class="$($report.OverallStatus.ToLower())">$($report.OverallStatus)</span></p>
<table>
<tr><th>Check</th><th>Status</th><th>Details</th></tr>
"@
    
    foreach ($check in $report.Checks) {
        $emailBody += "<tr><td>$($check.Name)</td><td>$($check.Status)</td><td>$($check.Details)</td></tr>"
    }
    
    $emailBody += @"
</table>
</body>
</html>
"@
    
    # Send email if unhealthy or warning
    if ($report.OverallStatus -ne "Healthy") {
        $subject = "⚠️ Gateway Health Alert - $($report.ServerName) - $($report.OverallStatus)"
        Send-MailMessage -To $EmailTo -From "gateway-monitor@contoso.com" -Subject $subject `
            -Body $emailBody -BodyAsHtml -SmtpServer $SmtpServer
        
        Write-Host "Alert sent: $subject" -ForegroundColor Yellow
    } else {
        Write-Host "✅ Gateway health check passed" -ForegroundColor Green
    }
    
    return $report
}

# Run health check
Test-GatewayHealth

Log Analysis Automation

# Analyze gateway logs for patterns and issues

function Analyze-GatewayLogs {
    param(
        [int]$LastHours = 24
    )
    
    $logPath = "C:\Users\*\AppData\Local\Microsoft\On-premises data gateway\Gateway*.log"
    $logs = Get-ChildItem $logPath | Sort-Object LastWriteTime -Descending | Select-Object -First 1
    
    if (-not $logs) {
        Write-Host "No gateway logs found" -ForegroundColor Red
        return
    }
    
    Write-Host "Analyzing gateway logs: $($logs.Name)" -ForegroundColor Cyan
    
    $content = Get-Content $logs.FullName
    $cutoffTime = (Get-Date).AddHours(-$LastHours)
    
    # Parse log entries
    $entries = $content | Where-Object { $_ -match '^\d{4}-\d{2}-\d{2}' } | ForEach-Object {
        $parts = $_ -split '\|'
        [PSCustomObject]@{
            Timestamp = [datetime]::Parse($parts[0])
            Level = $parts[1].Trim()
            Message = $parts[2].Trim()
        }
    } | Where-Object { $_.Timestamp -gt $cutoffTime }
    
    # Statistics
    $totalEntries = $entries.Count
    $errors = ($entries | Where-Object { $_.Level -eq "ERROR" }).Count
    $warnings = ($entries | Where-Object { $_.Level -eq "WARNING" }).Count
    $info = ($entries | Where-Object { $_.Level -eq "INFO" }).Count
    
    Write-Host "`n=== Log Statistics (Last $LastHours hours) ===" -ForegroundColor Yellow
    Write-Host "Total entries: $totalEntries"
    Write-Host "Errors: $errors" -ForegroundColor $(if($errors -gt 0){"Red"}else{"Green"})
    Write-Host "Warnings: $warnings" -ForegroundColor $(if($warnings -gt 0){"Yellow"}else{"Green"})
    Write-Host "Info: $info"
    
    # Top errors
    if ($errors -gt 0) {
        Write-Host "`n=== Top Errors ===" -ForegroundColor Red
        $topErrors = $entries | Where-Object { $_.Level -eq "ERROR" } | 
            Group-Object Message | Sort-Object Count -Descending | Select-Object -First 5
        
        foreach ($error in $topErrors) {
            Write-Host "Count: $($error.Count) | $($error.Name.Substring(0, [math]::Min(100, $error.Name.Length)))"
        }
    }
    
    # Data source connection failures
    $connectionErrors = $entries | Where-Object { $_.Message -match "connection|timeout|refused" }
    if ($connectionErrors.Count -gt 0) {
        Write-Host "`n⚠️ Connection Issues Detected: $($connectionErrors.Count) occurrences" -ForegroundColor Yellow
    }
}

# Run log analysis
Analyze-GatewayLogs -LastHours 24

Security Hardening

Security Configuration Checklist

Gateway Security Hardening:

☑ Service Account:
  ☐ Use domain service account (not local account)
  ☐ Least privilege (member of Gateway Admins only)
  ☐ Password rotation policy (90 days)
  ☐ No interactive logon rights
  ☐ Service account documented in CMDB

☑ Network Security:
  ☐ TLS 1.2 enforced (disable TLS 1.0, 1.1)
  ☐ Outbound HTTPS only to Microsoft endpoints
  ☐ Firewall rules documented and approved
  ☐ Network segmentation (gateway in DMZ or protected zone)
  ☐ Proxy configuration (if required)

☑ Access Control:
  ☐ Gateway admins group restricted to 2-3 people
  ☐ Data source credentials managed centrally
  ☐ Least privilege on data sources
  ☐ Regular access reviews (quarterly)
  ☐ Separation of duties (gateway admin ≠ data source admin)

☑ Logging and Auditing:
  ☐ Gateway logs retained for 90+ days
  ☐ Send logs to SIEM (Splunk, Azure Sentinel)
  ☐ Alert on authentication failures
  ☐ Monitor for unusual query patterns
  ☐ Audit data source access monthly

☑ OS Hardening:
  ☐ Latest Windows updates installed
  ☐ Antivirus/EDR installed and updated
  ☐ Unnecessary services disabled
  ☐ Local firewall enabled
  ☐ Remote Desktop restricted to jump box only
  ☐ File system encryption (BitLocker)

☑ Data Protection:
  ☐ Credentials encrypted at rest (gateway recovery key)
  ☐ Data in transit encrypted (TLS 1.2+)
  ☐ No sensitive data cached on disk
  ☐ Temp files cleaned up automatically
  ☐ Gateway logs scrubbed of PII

Enforce TLS 1.2

# Disable TLS 1.0 and 1.1, enforce TLS 1.2

# Disable TLS 1.0
New-Item -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server' -Force
New-Item -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client' -Force
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server' -Name 'Enabled' -Value '0' -PropertyType 'DWord' -Force
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client' -Name 'Enabled' -Value '0' -PropertyType 'DWord' -Force

# Disable TLS 1.1
New-Item -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server' -Force
New-Item -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client' -Force
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server' -Name 'Enabled' -Value '0' -PropertyType 'DWord' -Force
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client' -Name 'Enabled' -Value '0' -PropertyType 'DWord' -Force

# Enable TLS 1.2
New-Item -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server' -Force
New-Item -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client' -Force
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server' -Name 'Enabled' -Value '1' -PropertyType 'DWord' -Force
New-ItemProperty -Path 'HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client' -Name 'Enabled' -Value '1' -PropertyType 'DWord' -Force

# Enable strong cryptography for .NET Framework
Set-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord
Set-ItemProperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NetFramework\v4.0.30319' -Name 'SchUseStrongCrypto' -Value '1' -Type DWord

Write-Host "✅ TLS 1.2 enforced. Restart required." -ForegroundColor Green

Gateway Updates and Maintenance

Automated Update Deployment

# Script to check for and install gateway updates

function Update-Gateway {
    param(
        [switch]$AutoRestart
    )
    
    Write-Host "Checking for gateway updates..." -ForegroundColor Cyan
    
    # Get current gateway version
    $gatewayExe = "C:\Program Files\On-premises data gateway\EnterpriseGatewayConfigurator.exe"
    if (-not (Test-Path $gatewayExe)) {
        Write-Host "❌ Gateway not found" -ForegroundColor Red
        return
    }
    
    $currentVersion = (Get-Item $gatewayExe).VersionInfo.FileVersion
    Write-Host "Current version: $currentVersion" -ForegroundColor White
    
    # Download latest installer
    $downloadUrl = "https://download.microsoft.com/download/D/A/1/DA1FDDB8-6DA8-4F50-B4D0-18019591E182/GatewayInstall.exe"
    $installerPath = "$env:TEMP\GatewayInstall_$(Get-Date -Format 'yyyyMMdd').exe"
    
    Write-Host "Downloading latest installer..." -ForegroundColor Cyan
    Invoke-WebRequest -Uri $downloadUrl -OutFile $installerPath
    
    $newVersion = (Get-Item $installerPath).VersionInfo.FileVersion
    Write-Host "Latest version: $newVersion" -ForegroundColor White
    
    if ($currentVersion -eq $newVersion) {
        Write-Host "✅ Gateway is up to date" -ForegroundColor Green
        return
    }
    
    Write-Host "Update available: $currentVersion → $newVersion" -ForegroundColor Yellow
    
    # Create pre-update backup
    $backupPath = "C:\Gateway-Backups\Pre-Update-$(Get-Date -Format 'yyyyMMdd-HHmmss')"
    New-Item -Path $backupPath -ItemType Directory -Force | Out-Null
    
    # Backup config files
    Copy-Item -Path "C:\Program Files\On-premises data gateway\*.config" -Destination $backupPath -Force
    Write-Host "Config backed up to: $backupPath" -ForegroundColor Cyan
    
    # Install update
    Write-Host "Installing gateway update..." -ForegroundColor Cyan
    $logPath = "C:\Logs\GatewayUpdate-$(Get-Date -Format 'yyyyMMdd-HHmmss').log"
    
    Start-Process -FilePath $installerPath -ArgumentList "-quiet", "-norestart", "-log", $logPath -Wait -NoNewWindow
    
    Write-Host "✅ Gateway updated successfully" -ForegroundColor Green
    Write-Host "Log: $logPath"
    
    if ($AutoRestart) {
        Write-Host "Restarting gateway service..." -ForegroundColor Yellow
        Restart-Service -Name "PBIEgwService"
        Start-Sleep -Seconds 10
        
        $service = Get-Service -Name "PBIEgwService"
        if ($service.Status -eq "Running") {
            Write-Host "✅ Gateway service restarted successfully" -ForegroundColor Green
        } else {
            Write-Host "❌ Gateway service failed to restart" -ForegroundColor Red
        }
    } else {
        Write-Host "⚠️ Manual restart required" -ForegroundColor Yellow
    }
}

# Run update (with auto-restart)
Update-Gateway -AutoRestart

Maintenance Window Schedule

Gateway Maintenance Schedule:

Monthly Maintenance Window:
  - Timing: 2nd Sunday of month, 2:00 AM - 6:00 AM
  - Duration: 4 hours
  - Activities:
    ☐ Apply Windows updates
    ☐ Install gateway updates
    ☐ Review performance metrics
    ☐ Archive old logs
    ☐ Test failover (cluster only)
    ☐ Verify backups
    ☐ Update documentation

Pre-Maintenance Checklist:
  ☐ Notify users of maintenance window (7 days advance)
  ☐ Backup gateway configuration
  ☐ Verify cluster health (if applicable)
  ☐ Document current version and config
  ☐ Prepare rollback plan
  ☐ Stage updates on test environment first

Post-Maintenance Verification:
  ☐ Gateway service running
  ☐ Test data source connections
  ☐ Verify report refreshes
  ☐ Check error logs
  ☐ Confirm cluster synchronization
  ☐ Update change log

Troubleshooting Guide

Issue 1: Gateway Service Won't Start

Symptoms:

• Gateway service fails to start
• Event Viewer shows service crash

Diagnosis:

# Check service status and recent errors
Get-Service -Name "PBIEgwService" | Format-List *

# Check Event Viewer
Get-EventLog -LogName Application -Source "On-premises data gateway" -Newest 20 | Format-Table -AutoSize

Common Causes & Resolutions:

1. Corrupted configuration

   • Delete: C:\Users\<ServiceAccount>\AppData\Local\Microsoft\On-premises data gateway\GatewaySettings.json
   • Reconfigure gateway

2. Port conflict

   • Gateway uses port 443 outbound
   • Check firewall rules: netstat -ano | findstr ":443"

3. .NET Framework issues

   • Reinstall .NET Framework 4.7.2+
   • Run: sfc /scannow to repair system files

Issue 2: Data Source Connection Failures

Symptoms:

• "Can't connect to data source" errors
• Intermittent connectivity

Diagnosis:

# Test connectivity from gateway server
Test-NetConnection -ComputerName sql-server.contoso.com -Port 1433

# Check Kerberos authentication
klist tickets

# Check DNS resolution
Resolve-DnsName sql-server.contoso.com

Resolutions:

1. Network connectivity

   • Verify firewall allows gateway → data source
   • Check DNS resolution
   • Test from gateway server directly

2. Authentication issues

   • Verify credentials in Power BI Service
   • Check Kerberos SPN configuration
   • Test with SQL authentication as fallback

3. Connection pooling exhausted

   • Increase max connections in config
   • Stagger refresh schedules

Issue 3: High CPU/Memory Usage

Symptoms:

• Gateway server CPU > 80%
• Memory exhaustion
• Slow query performance

Diagnosis:

# Monitor gateway process
Get-Process PBIEgwService | Format-List *

# Check concurrent queries
$logs = Get-ChildItem "C:\Users\*\AppData\Local\Microsoft\On-premises data gateway\Gateway*.log" -Recurse | Sort-Object LastWriteTime -Descending | Select-Object -First 1
Get-Content $logs.FullName -Tail 1000 | Select-String "Executing query"

Resolutions:

1. Optimize queries

   • Implement query folding
   • Add indexes on data sources
   • Reduce data volume returned

2. Scale out

   • Add more gateway nodes to cluster
   • Distribute load across nodes

3. Adjust refresh schedules

   • Stagger large refreshes
   • Move to off-peak hours

Issue 4: Cluster Synchronization Problems

Symptoms:

• Nodes out of sync
• Inconsistent routing
• Some nodes not receiving requests

Diagnosis:

# Check cluster membership
Import-Module DataGateway
Login-DataGatewayServiceAccount

$cluster = Get-DataGatewayCluster -Name "Production-Gateway-Cluster"
$cluster.Members | Format-Table MachineName, Status, Version

Resolutions:

1. Version mismatch

   • Ensure all nodes on same gateway version
   • Update lagging nodes

2. Network connectivity between nodes

   • Verify nodes can reach Azure Service Bus
   • Check for proxy misconfigurations

3. Remove and re-add node

   • Uninstall gateway from problematic node
   • Reinstall and rejoin cluster

Best Practices Summary

☑ Architecture:
  ☐ Dedicated gateway servers (no shared workloads)
  ☐ High availability cluster (2+ nodes) for production
  ☐ Co-locate gateway near data sources (low latency)
  ☐ Separate dev/test/prod gateway clusters

☑ Configuration:
  ☐ Use domain service accounts (not local accounts)
  ☐ Configure Kerberos for Windows authentication
  ☐ Optimize connection pooling for workload
  ☐ Document all data source mappings

☑ Security:
  ☐ Enforce TLS 1.2+ only
  ☐ Apply principle of least privilege
  ☐ Regular security patching (monthly)
  ☐ Audit access quarterly

☑ Monitoring:
  ☐ Automated health checks every 5 minutes
  ☐ Alert on service failures within 2 minutes
  ☐ Daily log analysis for patterns
  ☐ Monthly performance trend reviews

☑ Maintenance:
  ☐ Monthly update schedule (2nd Sunday)
  ☐ Test updates in non-prod first
  ☐ Backup config before changes
  ☐ Documented rollback procedures

☑ Performance:
  ☐ Monitor CPU/memory/disk utilization
  ☐ Optimize slow queries at source
  ☐ Stagger refresh schedules
  ☐ Implement incremental refresh for large datasets

☑ Disaster Recovery:
  ☐ Recovery key stored in vault
  ☐ Config files backed up daily
  ☐ Documented restore procedures
  ☐ Quarterly DR drills

Key Takeaways

• Gateway is mission-critical infrastructure—treat it with the same rigor as production databases
• High availability clustering is essential for production workloads to prevent single points of failure
• Kerberos configuration requires careful SPN management and delegation settings
• Performance tuning focuses on query optimization, connection pooling, and refresh scheduling
• Automated monitoring catches issues before they impact users
• Security hardening includes TLS 1.2 enforcement, least privilege, and regular patching
• Maintenance windows should be scheduled monthly with comprehensive pre/post-checks
• Troubleshooting requires systematic approach: logs, connectivity tests, performance metrics

Next Steps

1. Assess current gateway deployment (single node or cluster?)
2. Plan high availability implementation (if not already clustered)
3. Configure Kerberos authentication for Windows auth scenarios
4. Implement automated monitoring with health check scripts
5. Establish maintenance schedule with documented procedures
6. Security hardening audit (TLS, service accounts, access controls)
7. Performance baseline and optimization based on actual workload
8. Document disaster recovery procedures and test quarterly
9. Train operations team on troubleshooting common issues
10. Regular capacity planning reviews (CPU, memory, query load trends)

Additional Resources

• On-Premises Data Gateway Documentation
• Gateway High Availability and Disaster Recovery
• Troubleshooting the Gateway
• Kerberos-based SSO Configuration
• Gateway Performance Best Practices
• Gateway REST API
• PowerShell DataGateway Module

---

Reliable. Secure. Scalable.