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"). 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 "コマンド" 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) ホスト鍵のフィンガープリントを取得
ssh-keyscan -T 6 HOST 2>/dev/null | ssh-keygen -lf -
#   => 256 SHA256:<fingerprint> <host> (...)  のように、鍵種ごとに1行ずつ出る

# 2) 取得値を -hostkey で固定し、-batch で非対話接続
#    サーバが複数の鍵種(ED25519/RSA等)を返す環境では、ネゴシエートされる鍵に備え各 -hostkey を列挙する
plink -ssh -hostkey SHA256:<fingerprint> -batch -pw '****' USER@HOST "コマンド"

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.

:: 非同期起動し、完了時に終了コードをセンチネルへ（同一 cmd /c 内なので & は逐次評価される）
start "" /b cmd /c "installer.exe /quiet & echo DONE=%errorlevel%> C:\work\done.txt"
:: 別コマンドで完了確認:  if exist C:\work\done.txt type C:\work\done.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 & 後続コマンド

[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.

# UTF-8 BOM付きで書き出す（既存のBOM無しUTF-8を変換する例）
$txt = [IO.File]::ReadAllText($src, [Text.UTF8Encoding]::new($false))
[IO.File]::WriteAllText($dst, $txt, [Text.UTF8Encoding]::new($true))  # $true = 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:\work\task.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*GO\s*$, 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."

# 列0のGOのみで分割（インデントされたコメント内GOは無視）
$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."