理解 SQL Server 連接架構不僅是面試必考題，更是排查連接池耗盡、分佈式事務失效、驗證模式不匹配等疑難雜症的基石。本文從網路協議層、提供者模型、連接字串解析三個維度，構建完整的客戶端連接知識體系。
應用程式層 (Application)
│
▼
物件庫 / API 層 (Object Library / API Layer)
├── ADO.NET (SqlClient, OleDbClient, OdbcClient) ← .NET 應用首選
├── ADO / OLE DB (SQLOLEDB, MSDASQL) ← 傳統 COM/VB6/ASP
├── ODBC (SQL Server ODBC Driver) ← 跨平台/C++/舊系統
├── DB-Library / Embedded SQL (已淘汰) ← 歷史遺留
│
▼
網路協議層 (Net-Library / Protocol Layer)
├── Shared Memory (本機連接，最高效)
├── TCP/IP (網路連接，標準 1433 埠)
├── Named Pipes (局域網，Windows 整合驗證友善)
└── VIA (Virtual Interface Adapter，高效能專用，已棄用)
│
▼
SQL Server 引擎 (Database Engine)
├── TDS (Tabular Data Stream) 協議封包處理
├── 連接認證 (Windows / SQL Server 驗證)
├── 會話管理 (SPID、交易上下文、鎖資源)
└── 查詢執行 (解析、優化、執行、回傳)
| 特性 | SqlClient (ADO.NET) | OLE DB (SQLOLEDB) | ODBC Driver |
|---|---|---|---|
| 適用平台 | .NET Framework / .NET Core / .NET 5+ | COM 應用 (VB6, ASP, C++, PowerBuilder) | 跨平台 (Linux/macOS/Windows), 非 .NET 語言 |
| 效能 | 原生 Managed 代碼，零互操作開銷，最優 | COM 互操作開銷，RCW 包裝，中等 | 非托管代碼，需 ODBC→OLE DB 橋接 (MSDASQL)，最低 |
| 功能支援 | 完整支援新功能 (AlwaysOn, Columnstore, JSON, UTF-8) | 支援大多數功能，新功能滯後 | 基礎功能完整，高階功能受限 |
| 連接池 | 內建高效連接池，進程級共享 | OLE DB 服務元件提供連接池 | ODBC 驅動管理器提供連接池 |
| 異步操作 | 原生 Task-based Async (BeginExecuteReader 等) | 不支援原生異步 | 支援異步 (SQLAsyncExecute) |
| 部署 | NuGet 套件 Microsoft.Data.SqlClient (推薦) / System.Data.SqlClient | MDAC / Windows Data Access Components (內建 OS) | Microsoft ODBC Driver 17/18 for SQL Server (獨立安裝) |
| 推薦場景 | 所有新建 .NET 專案 | 維護舊有 COM/VB6/ASP 系統 | Linux 容器、Python/Java/Node.js、舊版 C++ |
Server=tcp:myserver.database.windows.net,1433; -- 伺服器位址，支援 TCP/NP/共享記憶體前綴
Database=MyDB; -- 初始資料庫
Authentication=Active Directory Default; -- 驗證模式 (SQL/Windows/AAD/Managed Identity)
User Id=myUser; Password=myPass; -- SQL 驗證時必填
Encrypt=True; TrustServerCertificate=False; -- TLS 加密 (Azure SQL 必須 True)
Connection Timeout=30; -- 連接逾時 (秒)
Command Timeout=300; -- 指令逾時 (秒，SqlCommand 設定)
Pooling=True; Min Pool Size=5; Max Pool Size=100; -- 連接池參數
Application Name=MyApp_ModuleX; -- 識別應用 (sys.dm_exec_sessions.program_name)
Workstation ID=WEB01; -- 客戶端機器名
MultiSubnetFailover=True; -- AG 多子網故障轉移加速
TrustServerCertificate=True; -- 開發/測試環境跳過憑證驗證
SqlConnection.ClearAllPools() 釋放SELECT * FROM sys.dm_exec_sessions WHERE program_name LIKE 'MyApp%' 檢查睡眠連接數using (var conn = new SqlConnection(cs)) { conn.Open(); ... } 確保 Dispose() 歸還池
是否為 Azure SQL / Azure 託管實例？
├─ 是 → 推薦: Active Directory Default (支援 Managed Identity, AAD 應用, 開發人員憑證)
└─ 否 → 內部部署 SQL Server
├─ 域環境 + 服務帳號 → Windows Authentication (Kerberos 雙跳需 SPN 設定)
├─ 非域 / 跨域 / 容器化 → SQL Server Authentication (強密碼 + 定期輪換)
└─ 含舊版應用 (VB6/ASP) → OLE DB + Windows/SQL 驗證 (視應用支援)
| 症狀 | 診斷指令/工具 | 常見原因 |
|---|---|---|
| 連接逾時 | Test-NetConnection -Port 1433; telnet; SELECT * FROM sys.dm_exec_connections | 防火牆、TCP/IP 未啟用、埠錯誤、AG Listener 配置錯誤 |
| 登入失敗 (18456) | 錯誤日誌 State 碼對照: 5=用戶不存在, 6=密碼錯誤, 8=密碼過期, 11=登入被拒 | 帳號鎖定、密碼策略、預設資料庫離線、信任關係中斷 |
| 連接池耗盡 | SELECT program_name, COUNT(*) FROM sys.dm_exec_sessions GROUP BY program_name | 未 Dispose、長跑查詢佔用、MaxPoolSize 過小、連接洩漏 |
| 分佈式事務失敗 (MSDTC) | Component Services → MSDTC 安全性設定; DBCC OPENTRAN | 網路 DTC 存取未啟用、防火牆阻擋 135/動態埠、雙跳驗證失敗 |
架構師建議：新專案無腦選
Microsoft.Data.SqlClient(NuGet)，啟用Encrypt=True、MultiSubnetFailover=True、Application Name明確標識模組。連接字串集中管理 (Key Vault / App Configuration)，嚴禁硬編碼。定期執行sp_refreshsqlmodule更新連接相關的系統模組元數據。
