ETL: Reverse SSH Tunnel from Windows (PowerShell)

# Machine-wide folder for the tunnel
$Dir = "C:\integrateio"
New-Item -ItemType Directory -Force -Path $Dir | Out-Null

# Generate the key directly in that folder — press Enter through the prompts (leave the passphrase empty)
ssh-keygen -t rsa -b 4096 -f "$Dir\id_rsa_integrateio"

# Hand the key over to the service account so only SYSTEM and Administrators can read it
$key = "$Dir\id_rsa_integrateio"
takeown /F $key /A
icacls $key /inheritance:r /grant:r "NT AUTHORITY\SYSTEM:R" "BUILTIN\Administrators:R"
icacls $key /remove "$env:USERDOMAIN\$env:USERNAME"

notepad "C:\integrateio\id_rsa_integrateio.pub"

$YOUR_DB_HOST    = "HOST"   # e.g. mysql.us-east-1.rds.amazonaws.com
$YOUR_DB_PORT    = "PORT"   # e.g. 3306
$TUNNEL_SERVER   = "URL"    # e.g. tunnel.xplenty.com
$CONNECTION_PORT = "PORT"   # e.g. 70375

ssh -vv -NR "${CONNECTION_PORT}:${YOUR_DB_HOST}:${YOUR_DB_PORT}" `
  "sshtunnel@${TUNNEL_SERVER}" `
  -i "C:\integrateio\id_rsa_integrateio" `
  -p 50683 `
  -o "ExitOnForwardFailure yes" `
  -o "ServerAliveInterval 10" `
  -o "ServerAliveCountMax 1" `
  -o "StrictHostKeyChecking accept-new" `
  -o "UserKnownHostsFile C:\integrateio\known_hosts" `
  -N

# --- Replace with the SAME four values from Step 3
$CONNECTION_PORT = "PORT"
$YOUR_DB_HOST    = "HOST"
$YOUR_DB_PORT    = "PORT"
$TUNNEL_SERVER   = "URL"
# --- Nothing below needs editing ---

$Dir = "C:\integrateio"

# Save your connection settings for the tunnel script to read
@{ ConnectionPort = $CONNECTION_PORT; DbHost = $YOUR_DB_HOST; DbPort = $YOUR_DB_PORT; TunnelServer = $TUNNEL_SERVER } |
  ConvertTo-Json | Set-Content "$Dir\tunnel.config.json" -Encoding UTF8

# Write the tunnel script (identical for every install, all absolute paths)
@'
$cfg = Get-Content "C:\integrateio\tunnel.config.json" -Raw | ConvertFrom-Json
$ssh = "C:\Windows\System32\OpenSSH\ssh.exe"
while ($true) {
  & $ssh -NR "$($cfg.ConnectionPort):$($cfg.DbHost):$($cfg.DbPort)" `
    "sshtunnel@$($cfg.TunnelServer)" `
    -i "C:\integrateio\id_rsa_integrateio" `
    -p 50683 `
    -o "ExitOnForwardFailure yes" `
    -o "ServerAliveInterval 10" `
    -o "ServerAliveCountMax 1" `
    -o "StrictHostKeyChecking accept-new" `
    -o "UserKnownHostsFile C:\integrateio\known_hosts" `
    -N
  Start-Sleep -Seconds 5
}
'@ | Set-Content "$Dir\tunnel.ps1" -Encoding UTF8

# Register the task to run as SYSTEM, at startup — no password needed
$action  = New-ScheduledTaskAction -Execute "powershell.exe" `
  -Argument "-NoProfile -ExecutionPolicy Bypass -WindowStyle Hidden -File `"$Dir\tunnel.ps1`""
$trigger = New-ScheduledTaskTrigger -AtStartup
$settings = New-ScheduledTaskSettingsSet -StartWhenAvailable `
  -AllowStartIfOnBatteries -DontStopIfGoingOnBatteries `
  -RestartCount 3 -RestartInterval (New-TimeSpan -Minutes 1) `
  -ExecutionTimeLimit ([TimeSpan]::Zero)
$principal = New-ScheduledTaskPrincipal -UserId "SYSTEM" -LogonType ServiceAccount -RunLevel Highest
Register-ScheduledTask -TaskName "Integrate.io Reverse SSH Tunnel" `
  -Action $action -Trigger $trigger -Settings $settings -Principal $principal -Force

# Start it now (no reboot needed)
Start-ScheduledTask -TaskName "Integrate.io Reverse SSH Tunnel"

Get-Process ssh
Get-ScheduledTask -TaskName "Integrate.io Reverse SSH Tunnel" | Select-Object TaskName, State

Unregister-ScheduledTask -TaskName "Integrate.io Reverse SSH Tunnel" -Confirm:$false
Get-Process ssh | Stop-Process -Force