ETL: Windows File Share Source

Read files from a Windows file share (SMB/CIFS) in 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 SFTP user and grant read access

A dedicated non-admin SFTP user reads files from a folder on the Windows host. You’ll be prompted for a password — any value; key auth is enforced below. Pick one of the two options:

• Option A — create a new folder for Integrate.io to read from.
• Option B — point the SFTP user at an existing folder you already have.

Option A: Create a new folder to read from

A dedicated non-admin user, chrooted into C:\fileshare-test. Drop the files you want Integrate.io to read into the in/ subfolder; the SFTP user gets read-only access.

New-Item -ItemType Directory -Path "C:\fileshare-test\in" -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
# Admins/SYSTEM own the chroot; the SFTP user gets read-only (RX), which inherits into in/.
icacls "C:\fileshare-test" /grant "SYSTEM:(OI)(CI)F" "Administrators:(OI)(CI)F" "xplenty-sftp:(OI)(CI)RX" | 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

Option B: Use an existing folder (read-only)

Use this instead of Option A if you already have a folder to ingest from.

Important: Does any non-admin currently write to the folder? OpenSSH refuses the session unless the chroot root and every parent up to the drive are owned by Admins/SYSTEM and not writable by any non-admin principal. This is only recommended if there are no other non-admin users or any existing integrations writing to the folder, otherwise, create a new folder to ingest from.

## Point the SFTP user at the existing folder (read-only)

$Share = "C:\ExistingData"   # <- the existing folder you want to ingest from

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
}

# Additive read-only grant — does NOT touch existing ACEs.
# RX = list + read + traverse, no write/delete. (OI)(CI) propagates to files + subfolders.
icacls $Share /grant "xplenty-sftp:(OI)(CI)RX" | Out-Null

# --- ONLY if the chroot check fails with "bad ownership or modes" ---
# That means a non-admin principal has write at the chroot root. Clear it,
# then re-grant read to anyone who still needs it. Skip this block otherwise.
#   icacls $Share /remove:g "Users" "Authenticated Users" "Everyone" | Out-Null
#   icacls $Share /grant "Users:(OI)(CI)RX" | Out-Null   # re-grant read if needed
# -------------------------------------------------------------------

$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 $Share
    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 the folder you set in Step 2, so that folder is the SFTP root. In Integrate.io, the source path always starts at /, which points at that folder on Windows. Do not put the Windows drive or the chroot prefix in the Integrate.io path.

File on Windows (chroot = C:\ExistingData)	Source path in Integrate.io
C:\ExistingData\test.csv	/test.csv
C:\ExistingData\sales\jan.csv	/sales/jan.csv
every .csv directly under the folder	/*.csv

To verify an actual read, first confirm the file exists under the chroot folder on Windows, then run a one-row test package in Integrate.io with the matching source path:

Get-ChildItem C:\ExistingData\

Removing everything later

This removes the tunnel, the SFTP user, and the OpenSSH server. A folder you created in Option A is deleted; an existing folder used in Option B is left in place (only the read grant is revoked).

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
Remove-Item -Recurse -Force C:\integrateio
# Option A only — WARNING: permanently deletes C:\fileshare-test and every file in it. Back up first.
Remove-Item -Recurse -Force C:\fileshare-test -ErrorAction SilentlyContinue
# Option B only — leave the existing folder, just revoke the read grant:
# icacls "C:\ExistingData" /remove "xplenty-sftp" | Out-Null
Remove-Item -Force C:\ProgramData\ssh\sshd_config -ErrorAction SilentlyContinue
Remove-Item -Force C:\ProgramData\ssh\authorized_keys_xplenty-sftp -ErrorAction SilentlyContinue