SQL Server 客戶端連接組件精簡部署：核心 DLL、注冊表、連接字串最小化棲指南

核心問題：為什麼「只複製 DLL」在新機器失敗？

SQL Server 客戶端連接不僅需 DLL，還依賴：

1. 網絡庫 (Net-Libraries)：`dbnetlib.dll` (TCP/IP)、`dbnmpntw.dll` (Named Pipes)、`dbmsrpcn.dll` (Multiprotocol/RPC)、`dbmsadsn.dll` (AppleTalk)、`dbmsspxn.dll` (IPX/SPX)。
2. 核心通信層：`ntwdblib.dll` (DB-Library 兼容)、`msvcr71/80/90/100/110/120/140.dll` (CRT 運行庫)。
3. 注冊表配置：`HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo` 別名映射、`HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SNI` 協議順序。
4. 系統組件：MDAC 2.8+ / Windows Data Access Components (WDAC) / SQL Native Client (SNAC) / ODBC Driver / OLE DB Driver。

最小化部署清單 (x86/x64 必須匹配目標進程位數)

零注冊表部署：連接字串內嵌別名

無需寫入 `HKLM\...\ConnectTo`，直接在連接字串指定協議與端口：

-- TCP/IP 直連 (推薦)
Server=tcp:192.168.1.100,1433;Database=MyDB;Uid=User;Pwd=Secret;Encrypt=TrustServerCertificate;

-- Named Pipes (內網低延遲)
Server=np:\\DBServer\pipe\sql\query;Database=MyDB;Trusted_Connection=yes;

-- 別名 (需客戶端配置別名或 SQL Native Client 支持)
Server=MyAlias;Database=MyDB;...

PowerBoot 腳本：一鍵檢測並修復客戶端環境

#Requires -RunAsAdministrator
param($TargetDir = "C:\App\Bin")

# 1. 確保 VC++ Redist
if (-not (Test-Path "HKLM:\SOFTWARE\Microsoft\VisualStudio\14.0\VC\Runtimes\x64")) {
    Write-Host "Installing VC++ 2015-2022 Redist..." -Fore Yellow
    Start-Process -Wait -FilePath "vc_redist.x64.exe" -ArgumentList "/install", "/quiet", "/norestart"
}

# 2. 複製核心 DLL (假設已放在腳本目錄下 \Dlls)
$Dlls = @("sqlncli11.dll","msodbcsql17.dll","msoledbsql.dll","dbnetlib.dll")
foreach ($d in $Dlls) {
    if (Test-Path ".\Dlls\$d") {
        Copy-Item ".\Dlls\$d" -Destination $TargetDir -Force
        Write-Host "Copied $d"
    }
}

# 3. 驗證連接 (需引用 System.Data.dll)
Add-Type -AssemblyName System.Data
$cs = "Server=tcp:192.168.1.100,1433;Database=master;Uid=sa;Pwd=Test123;Connect Timeout=5;Encrypt=TrustServerCertificate;"
try {
    $conn = New-Object System.Data.SqlClient.SqlConnection $cs
    $conn.Open()
    Write-Host "Connection OK, ServerVersion: $($conn.ServerVersion)" -Fore Green
    $conn.Close()
} catch {
    Write-Host "Connection FAILED: $($_.Exception.Message)" -Fore Red
}

常見報錯與對應修復

現代化建議：棄用舊組件，擁抱 Microsoft Data Access Components (MDAC) 後繼者

1. 新開發：強制使用 **Microsoft ODBC Driver 17/18 for SQL Server** / **Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)** / **.NET SqlClient (System.Data.SqlClient / Microsoft.Data.SqlClient)**。
2. 遷移存量：逐步替換連接字串 `Provider=SQLOLEDB` → `MSOLEDBSQL`，`Driver={SQL Server}` → `ODBC Driver 17 for SQL Server`。
3. 容器化：基礎鏡像預裝 `msodbcsql17` + `msoledbsql`，應用鏡像無需額外 DLL。
4. 雲原生：Azure SQL / AWS RDS 連接強制加密，驅動需支持 `Encrypt=Strict`、`TrustServerCertificate=False`、托管身份認證。