ETL: Windows File Share Destination

Write output files to a Windows file share (SMB/CIFS) from Integrate.io ETL using SFTP over a reverse SSH tunnel, with no inbound firewall rules.

Open Windows Powershell via Run as Administrator and run the commands below

1. Install OpenSSH Server (localhost only)

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

2. Create the share folder and the SFTP user

A dedicated non-admin user, chrooted into C:\fileshare-test, writable only inside out/. You’ll be prompted for a password — any value; key auth is enforced below.

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

3. Generate the tunnel keypair, upload to Integrate.io dashboard

Go to Integrate.io dashboard Settings > SSH Public Key and paste the public key

If you have already uploaded a public key from this same machine, skip this step.

$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."

4. Create the SFTP connection in Integrate.io

In the Integrate.io UI, create a new SFTP connection:

• Access type: reverse
• Authentication method: Public key authentication
• User: xplenty-sftp
• Both tunnel endpoint and public key would be generated after saving the connection.

5. Install the Integrate.io SFTP public key on Windows

Run the script below and paste the SFTP public key generated from the connection on our previous step.

$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

6. Quick test before making it permanent

Run the tunnel once in the foreground to confirm it connects, then click Test Connection in the Integrate.io UI. Press Ctrl + C here to stop the tunnel once it passes.

Fill in the two values from Step 4 and paste:

# --- 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

7. Run the tunnel as a persistent SYSTEM task

Fill in the two values from the Integrate.io UI in Step 4, then paste:

# --- 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"

8. Confirm and test

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

You should see one ssh process and State: Running. In the Integrate.io UI, click Test Connection — it should pass.

Path mapping: Integrate.io path vs Windows path

The SFTP user is chrooted into C:\fileshare-test, so that folder is the SFTP root. In Integrate.io, the destination path always starts at /, which points at C:\fileshare-test on Windows. Do not put the Windows drive or the C:\fileshare-test prefix in the Integrate.io path. The user can only write inside out/, so destination paths must begin with /out/.

Destination path in Integrate.io	File on Windows
/out/test.csv	C:\fileshare-test\out\test.csv
/out/sales/jan.csv	C:\fileshare-test\out\sales\jan.csv

To verify an actual write, run a one-row test package in Integrate.io with the SFTP destination path set to /out/test.csv, then on Windows:

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

Removing everything later

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