ETL: Windows File Share Destination

Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0
Start-Service sshd  # creates the default sshd_config on first run

$cfgPath = "C:\ProgramData\ssh\sshd_config"
$cfg = Get-Content $cfgPath -Raw

# Ensure the SFTP subsystem uses internal-sftp (sftp-server.exe can't be reached from inside the chroot used in Step 2)
$cfg = $cfg -replace "(?m)^\s*#?\s*Subsystem\s+sftp\s+.*\r?\n", ""
$cfg = "Subsystem sftp internal-sftp`r`n" + $cfg

# Idempotent global directives — must be inserted BEFORE any Match block to stay in global scope
if ($cfg -notmatch "# BEGIN integrate-io-global") {
  $globalBlock = @"

# BEGIN integrate-io-global
ListenAddress 127.0.0.1
PasswordAuthentication no
PubkeyAuthentication yes
PubkeyAcceptedAlgorithms +ssh-rsa
# END integrate-io-global

"@
  if ($cfg -match "(?m)^Match\s+") {
    $cfg = $cfg -replace "(?m)^(Match\s+)", "$globalBlock`$1"
  } else {
    $cfg = $cfg.TrimEnd() + $globalBlock
  }
}

Set-Content -Path $cfgPath -Value $cfg -Encoding UTF8 -NoNewline

Restart-Service sshd
Set-Service -Name sshd -StartupType Automatic

New-Item -ItemType Directory -Path "C:\fileshare-test\out" -Force | Out-Null

if (-not (Get-LocalUser -Name "xplenty-sftp" -ErrorAction SilentlyContinue)) {
  $pw = Read-Host -AsSecureString "Password for xplenty-sftp (any value; key auth is enforced)"
  New-LocalUser -Name "xplenty-sftp" -Password $pw -PasswordNeverExpires | Out-Null
}

icacls "C:\fileshare-test"     /inheritance:r | Out-Null
icacls "C:\fileshare-test"     /grant "SYSTEM:(OI)(CI)F" "Administrators:(OI)(CI)F" "xplenty-sftp:(OI)(CI)RX" | Out-Null
icacls "C:\fileshare-test\out" /grant "xplenty-sftp:(OI)(CI)F" | Out-Null

$cfgPath = "C:\ProgramData\ssh\sshd_config"
if (-not (Select-String -Path $cfgPath -Pattern "# BEGIN integrate-io-match" -Quiet)) {
  Add-Content -Path $cfgPath -Value @"

# BEGIN integrate-io-match
Match User xplenty-sftp
    AuthorizedKeysFile __PROGRAMDATA__/ssh/authorized_keys_%u
    ForceCommand internal-sftp
    ChrootDirectory C:\fileshare-test
    AllowTcpForwarding no
    PermitTunnel no
    X11Forwarding no
# END integrate-io-match
"@
}

Restart-Service sshd

$Dir = "C:\integrateio"
New-Item -ItemType Directory -Force -Path $Dir | Out-Null
ssh-keygen -t rsa -b 4096 -f "$Dir\tunnel_key" -C "integrate-io-tunnel-$env:COMPUTERNAME"

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

# Copy the public key to the clipboard — paste it into the Integrate.io UI in Step 4
Get-Content "$Dir\tunnel_key.pub" | Set-Clipboard
Write-Host "Tunnel public key copied. Paste it into the Integrate.io SFTP connection's tunnel public-key field."

$sftpPubKey = Read-Host "Paste the SFTP public key from Integrate.io, then press Enter"
$keyFile = "C:\ProgramData\ssh\authorized_keys_xplenty-sftp"
New-Item -ItemType Directory -Force -Path "C:\ProgramData\ssh" | Out-Null

# If updating an existing key, restore write access first (the restrictive ACL below otherwise blocks rewrites)
if (Test-Path $keyFile) {
  takeown /F $keyFile /A | Out-Null
  icacls $keyFile /grant "Administrators:F" | Out-Null
}

Set-Content -Path $keyFile -Value $sftpPubKey -Encoding ASCII
icacls $keyFile /inheritance:r | Out-Null
icacls $keyFile /grant:r "NT AUTHORITY\SYSTEM:R" "xplenty-sftp:R" | Out-Null

# --- Replace with the values from the Integrate.io UI ---
$BASTION_HOST    = "tunnel.xplenty.com"  # replace if endpoint is different
$BASTION_FORWARD = "..."                 # forwarding port, e.g. 49667
# --- Nothing below needs editing ---

& "C:\Windows\System32\OpenSSH\ssh.exe" -vv -NR "${BASTION_FORWARD}:127.0.0.1:22" `
  "sshtunnel@${BASTION_HOST}" `
  -p 50683 `
  -i "C:\integrateio\tunnel_key" `
  -o "ExitOnForwardFailure yes" `
  -o "ServerAliveInterval 10" `
  -o "ServerAliveCountMax 1" `
  -o "StrictHostKeyChecking accept-new" `
  -o "UserKnownHostsFile C:\integrateio\known_hosts" `
  -N

# --- Replace with the values from the Integrate.io UI ---
$BASTION_HOST    = "tunnel.xplenty.com"  # replace if endpoint is different
$BASTION_FORWARD = "..."                 # forwarding port, e.g. 49667
# --- Nothing below needs editing ---

$Dir = "C:\integrateio"
@{ BastionHost = $BASTION_HOST; BastionForward = $BASTION_FORWARD } |
  ConvertTo-Json | Set-Content "$Dir\tunnel.config.json" -Encoding UTF8

@'
$cfg = Get-Content "C:\integrateio\tunnel.config.json" -Raw | ConvertFrom-Json
$ssh = "C:\Windows\System32\OpenSSH\ssh.exe"
while ($true) {
  & $ssh -NR "$($cfg.BastionForward):127.0.0.1:22" `
    "sshtunnel@$($cfg.BastionHost)" `
    -p 50683 `
    -i "C:\integrateio\tunnel_key" `
    -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

$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 SFTP Reverse Tunnel" `
  -Action $action -Trigger $trigger -Settings $settings -Principal $principal -Force
Start-ScheduledTask -TaskName "Integrate.io SFTP Reverse Tunnel"

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

Get-ChildItem C:\fileshare-test\out\
Get-Content   C:\fileshare-test\out\test.csv

Unregister-ScheduledTask -TaskName "Integrate.io SFTP Reverse Tunnel" -Confirm:$false
Get-Process ssh -ErrorAction SilentlyContinue | Stop-Process -Force
Stop-Service sshd
Set-Service sshd -StartupType Disabled
Remove-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0
Remove-LocalUser -Name xplenty-sftp
# WARNING: permanently deletes C:\fileshare-test (including any output files in out\) and every file in it. Back up first.
Remove-Item -Recurse -Force C:\fileshare-test, C:\integrateio
Remove-Item -Force C:\ProgramData\ssh\sshd_config -ErrorAction SilentlyContinue
Remove-Item -Force C:\ProgramData\ssh\authorized_keys_xplenty-sftp -ErrorAction SilentlyContinue