--- title: "Ten Pitfalls Hit With plink, PowerShell 5.1, and SQL Server 2022 — Entrusting Hands-On Windows Work to an AI Agent (SSH / Encoding / T-SQL)" description: "Ten pitfalls from automating Windows/SQL Server with an AI agent on real machines: plink host-key pinning, CP932/BOM, cmd quoting, and GO-splitting." url: "https://bestnetllc.co.jp/en/docs/ai-agent-windows-sqlserver-pitfalls-en/" updated: "2026-10-19" category: Tech Blog / Database Migration --- # Ten Pitfalls Hit With plink, PowerShell 5.1, and SQL Server 2022 — Entrusting Hands-On Windows Work to an AI Agent (SSH / Encoding / T-SQL) BESTNET TECH BLOG Ten Pitfalls Hit With plink, PowerShell 5.1, and SQL Server 2022 Entrusting hands-on Windows work to an AI agent — a field record of stumbling at the "seams" of SSH non-interactivity, character encoding, and T-SQL Author: Hideyuki Chinda / BESTNET LLC2026-06-13Series: Technical Side Note In the main installment of this series, we reported that we automated the preprocessing for an Oracle 19c to SQL Server 2022 migration with an AI coding agent (Claude Code) without handing over any real data, and completed everything through to applying and functionally verifying it on the actual target machine. This article is the flip side of that. When you entrust "hands-on work on a real machine" to an AI agent, what trips you up is usually **not the AI's cleverness, but the "seams" between the AI, the shell, the OS's character encoding, and the tools**. Making SSH non-interactive, garbled characters on Windows, cmd quoting, injecting T-SQL from code — each is unremarkable on its own, but enough to halt automation. Of the traps hit in the field, this article records the ten more technical ones in the form of symptom, cause, and remedy (the remaining two are a governance story involving the AI's safety mechanism, so see [Side Note Part 2, "The AI Guardrail Edition"](https://bestnetllc.co.jp/en/docs/ai-agent-guardrails-three-layer-governance-en/)). I hope it saves a few hours for those considering similar automation. > The command examples in this article have all connection destinations and credentials replaced with placeholders (`USER@HOST`, `-pw '****'`, `C:work`). --- ## 1. Connection — Establishing Hands-Free SSH As a prerequisite for having the AI agent do the work, the SSH command needs to go through **without a human ever typing a password**. Here come the first three. ### ① OpenSSH cannot pass a password non-interactively - **Symptom**: `ssh user@host "command"` stalls waiting for password input. It cannot be run automatically from a script. - **Cause**: For security reasons, the OpenSSH client reads the password only from the terminal (tty). It will not accept it even if piped to standard input. Windows does not have `sshpass` by default, either. - **Remedy**: Use PuTTY's `plink` and pass the password with `-pw`. ``` plink -ssh -pw '****' USER@HOST "whoami" ``` > From a security standpoint, for production operations consider public-key authentication first (a key pair plus `pageant` or `-i`). Passing `-pw` on the command line can remain in the process list and shell history, so even when you must use it, the premise is a disposable password plus rotation after the work. ### ② plink hangs on the first-time host-key prompt - **Symptom**: Even `echo y | plink ...` does not proceed. No response until it times out. - **Cause**: plink reads the key-cache confirmation on first connection, "Store key in cache? (y/n)," from the terminal (tty). In an environment without a tty, such as AI-agent execution (via pipe / process launch), it cannot accept the piped `y` and hangs. There are configurations where `echo y | plink` works on an interactive console, but it cannot be relied upon in automated execution. - **Remedy**: Obtain the host key in advance with `ssh-keyscan`, and pin the SHA256 fingerprint with `-hostkey`. Disable the prompt itself with `-batch`. ``` # 1) Get the host key fingerprint ssh-keyscan -T 6 HOST 2>/dev/null | ssh-keygen -lf - # => 256 SHA256: (...) — one line per key type # 2) Pin that value with -hostkey and connect non-interactively with -batch # Where the server offers several key types (ED25519/RSA etc.), list each -hostkey so whichever is negotiated is covered plink -ssh -hostkey SHA256: -batch -pw '****' USER@HOST "command" ``` Pinning is also a security plus (against man-in-the-middle attacks, it rejects any key other than the expected one). ### ③ Design long-running commands to "fire and wait" - **Symptom**: Running a long process such as an ISO download or product installation synchronously hits the tool-side timeout and gets cut off. - **Cause**: A process that blocks for more than ten minutes while holding the SSH session easily runs into the agent's execution limit. - **Remedy**: Launch long processes **in the background and pick up completion via a sentinel file or completion notification**. Do not occupy the SSH session for a long time. ``` :: Start asynchronously and write the exit code to a sentinel on completion (& is evaluated in sequence inside the same cmd /c) start "" /b cmd /c "installer.exe /quiet & echo DONE=%errorlevel%> C:workdone.txt" :: Check completion from a separate command: if exist C:workdone.txt type C:workdone.txt ``` --- ## 2. Character Encoding — The Two-Stage Setup of CP932 and BOM When dealing with Japanese Windows, garbled characters are not a "cosmetic problem" but **a bug that breaks processing**. ### ④ Remote output garbles in Shift-JIS - **Symptom**: Command results are unreadable, garbled like `�w�肳�ꂽ�p�X...`. - **Cause**: Japanese Windows outputs in CP932 (Shift-JIS). It garbles when passed to a terminal that assumes UTF-8. - **Remedy**: Put `chcp 65001` (switch to UTF-8) at the head of the remote-side command. For PowerShell, also specify the output encoding explicitly. ``` chcp 65001>nul & subsequent command ``` ``` [Console]::OutputEncoding = [Text.UTF8Encoding]::new() ``` ### ⑤ PowerShell 5.1 reads BOM-less .ps1 as CP932 [the trap that dissolved the most time] - **Symptom**: Japanese literals in a script (such as `'すべて'`) garble into something else like `'鬟溷刀'`, and SQL or processing falls over with `'～' 付近に不適切な構文があります`. - **Cause**: **PowerShell 5.1 interprets a BOM-less `.ps1` as the system ANSI code page (CP932 in a Japanese environment)**. The Japanese in a script saved as UTF-8 gets broken. A script with only ASCII is unharmed, so **the discovery is delayed** — which is the troublesome part. - **Remedy**: Save a `.ps1` that contains Japanese as **UTF-8 (with BOM)**. PS5.1 sees the BOM and judges it as UTF-8. ``` # Write out as UTF-8 with BOM (example: converting existing UTF-8 without BOM) $txt = [IO.File]::ReadAllText($src, [Text.UTF8Encoding]::new($false)) [IO.File]::WriteAllText($dst, $txt, [Text.UTF8Encoding]::new($true)) # $true = with BOM ``` These two (④⑤) are problems at different layers (terminal output vs. script reading), but because the symptom is the same "garbled characters," they are easy to confuse. **Output = chcp, input (script) = BOM** — separating them this way is the quickest to remember. --- ## 3. Execution Patterns — Escaping cmd Quoting Hell ### ⑥ Inline PowerShell via cmd breaks on `|` `"` `;` - **Symptom**: Running `powershell -Command "Get-ChildItem | Select-Object Name"` gives `'Select-Object' は…認識されていません`. It also breaks with nested `"`. - **Cause**: Because it passes through multiple stages, SSH → cmd → PowerShell, **cmd interprets the `|` as a pipe first**. Nested double quotes also break at the cmd stage. The method of feeding it into standard input (`-Command -`) could also get cut off partway with a long script. - **Remedy**: Give up on inline; locally write a `.ps1` → transfer it with `pscp` → execute with `powershell -NoProfile -File`. In this work's environment (Windows Server 2022's default `RemoteSigned`), a local unsigned `.ps1` ran as is. This resolves most of both the quoting problem and the standard-input cutoff. > This pattern was stable in this work. "Don't struggle to do complex remote processing inline; make it a file, send it, and invoke it with `-File`." There is no need to weaken the execution policy with `Bypass` either (that operation was, in fact, stopped by the safety mechanism — see Side Note Part 2). > > > > However, a caveat about the premise is needed. Whether a transferred script runs under `RemoteSigned` depends on the organization's group policy and signing policy (AllSigned / Constrained Language Mode / AppLocker / WDAC, etc.). Also, "a pscp-transferred file does not get the MOTW (Mark of the Web) = the signature guard does not take effect" is not an advantage but a trade-off, and for third-party-origin scripts it is rather a risk. Do not forget the premise that it was tolerated because it was a tool whose contents we understood in-house. ``` pscp -hostkey SHA256:xxxx -batch -pw '****' .task.ps1 USER@HOST:C:/work/task.ps1 plink -ssh -hostkey SHA256:xxxx -batch -pw '****' USER@HOST "powershell -NoProfile -File C:worktask.ps1" ``` --- ## 4. SQL Injection-of-Statements — Traps When Streaming T-SQL From Code This is the triple-hit when streaming schema DDL directly via `System.Data.SqlClient` (the .NET standard SQL Server connection library; it can issue DDL/DML directly from an application without sqlcmd or SSMS, but on the flip side it does not have sqlcmd-specific features = `GO` separators, etc.) with integrated authentication, without going through sqlcmd. ### ⑦ `GO` splitting breaks on a `GO` inside a comment - **Symptom**: Batch-executing DDL makes some batches fall over with `コメントの終了マーク '*/' がありません` or `'=' 付近に不適切な構文`. - **Cause**: `System.Data.SqlClient` does not understand `GO` (`GO` is a batch separator for sqlcmd/SSMS and is not T-SQL). You need to split it yourself, but if you naively split on `^s*GOs*$`, you also split on a `GO` written with indentation inside a `/* … */` comment (such as example code), fragmenting the comment. - **Remedy**: Split only on a `GO` at the head of a line (column 0). If a `GO` inside a comment is indented (which this codebase was), this excludes it. More strictly, the proper approach is to "exclude comments and string literals, then split on `GO`." ``` # Split only on a GO in column 0 (a GO inside an indented comment is ignored) $batches = [regex]::Split($script, '(?im)^GO[ t]*;?[ t]*r?$') ``` ### ⑧ Creating an indexed view fails on `ARITHABORT` - **Symptom**: `CREATE UNIQUE CLUSTERED INDEX` (turning it into an indexed view) fails with `…SET options have incorrect settings: 'ARITHABORT'`. - **Cause**: `System.Data.SqlClient` has `ARITHABORT OFF` by default. Creating an indexed view requires several SET options to be ON. - **Remedy**: Inject the necessary SET options right after connecting, then stream the DDL. ``` SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; SET ARITHABORT ON; SET CONCAT_NULL_YIELDS_NULL ON; SET QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF; ``` ### ⑨ Getting row counts gives `列名 'rows' が無効です` - **Symptom**: Trying to get per-table counts with `SELECT SUM(p.rows) FROM sys.dm_db_partition_stats p …` falls over with `列名 'rows' が無効です（Invalid column name 'rows'）`. - **Cause**: The row-count column of `sys.dm_db_partition_stats` (a DMV = dynamic management view; a system view that returns the server's internal state) is `row_count`, not `rows`. The one that has a `rows` column is `sys.partitions`. We had them mixed up. - **Remedy**: Use `sys.partitions` for count aggregation (this is the standard). ``` SELECT t.name, ISNULL(SUM(p.rows), 0) AS rows FROM sys.tables t LEFT JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0,1) WHERE t.is_ms_shipped = 0 GROUP BY t.name ORDER BY t.name; ``` --- ## 5. Cleanup — Unremarkable Traps That Still Halt Automation ### ⑩ `Get-ChildItem -Exclude` returns nothing, and cleanup does not proceed - **Symptom**: Even running `Get-ChildItem 'C:work' -Exclude 'keep.sql' | Remove-Item -Force` deletes **not a single file** (no error, either). - **Cause**: A directory-specified `-Exclude` can **fail to enumerate as expected and return empty** unless you append `*` to the end of the path or also use `-Recurse` (a known PowerShell quirk). Since the input passed to `Remove-Item` is empty, nothing happens. - **Remedy**: Don't cut corners; explicitly enumerate the targets and use `Remove-Item -LiteralPath -Force`. ``` foreach ($f in 'a.exe','b.iso','c.ps1') { Remove-Item -LiteralPath (Join-Path 'C:work' $f) -Force -ErrorAction SilentlyContinue } ``` --- ## Conclusion — The Pitfalls Show Up at the "Seams," Not in "Cleverness" Lined up, there are almost no places where the AI agent itself made a mistake. What we got stuck on was **almost entirely the seams**. - The seam between the AI and the shell (cmd's quoting interpretation, the input route for the host-key prompt) - The seam between the shell and the OS (CP932, BOM) - The seam between the code and the tools (SqlClient not knowing `GO`, the DMV's column name, the default value of ARITHABORT) Conversely, the more you standardize the seams, the more stable the automation becomes (though, of course, other seams may remain). The practical solution in this work boiled down to the following three points. 1. For connections, make it hands-free with `plink` / `pscp` + host-key pinning + `-batch` 2. Don't write complex processing inline; send a `.ps1` and execute it with `-File` (this held in this work's `RemoteSigned` environment; verify per your organization's policy) 3. For character encoding, separate it as "output = chcp 65001 / input (script) = UTF-8 BOM" Note that, in the course of crushing these, there was also a scene where the AI agent's safety mechanism blocked the AI's own operation twice. The case of trying to weaken the execution policy, and the case of trying to write credentials to a file. This is less a "pitfall" and more a **governance story**, so it is covered in [Side Note Part 2, "The AI Guardrail Edition."](https://bestnetllc.co.jp/en/docs/ai-agent-guardrails-three-layer-governance-en/) At BESTNET LLC, we provide support for automating infrastructure and DB migration work using AI agents. Please consult us starting from "how to standardize the seams of your own environment."