IIS 中 ASP.NET 連接 SQL Server 失敗完整排查指南

常見錯誤訊息

• Login failed for user 'MACHINE\ASPNET'
• Cannot open database "xxx" requested by the login. The login failed.
• A network-related or instance-specific error occurred while establishing a connection to SQL Server.

核心原因

ASP.NET 應用程式預設以 ASPNET（IIS 6）或 NETWORK SERVICE / ApplicationPoolIdentity（IIS 7+）帳號執行。若連接字串使用 Integrated Security=SSPI（Windows 驗證），SQL Server 必須授權該 Windows 帳號登入並存取目標資料庫。

解決步驟（Windows 驗證模式）

1. 以 Windows 驗證登入 SSMS

開啟 SQL Server Management Studio，選擇 Windows Authentication 連線至目標執行個體。

2. 建立 SQL 登入對應 Windows 帳號

-- 取代 YOUR_MACHINE_NAME 為實際電腦名稱
EXEC sp_grantlogin 'YOUR_MACHINE_NAME\ASPNET';        -- IIS 6
-- 或
EXEC sp_grantlogin 'NT AUTHORITY\NETWORK SERVICE';   -- IIS 7+ 預設
-- 或應用程式集區身分識別
EXEC sp_grantlogin 'IIS APPPOOL\YourAppPoolName';

3. 授權資料庫存取

USE YourDatabaseName;
EXEC sp_grantdbaccess 'YOUR_MACHINE_NAME\ASPNET'; -- 或對應帳號

4. 給予資料庫角色權限

-- 簡單做法：加入 db_owner（開發/測試環境）
EXEC sp_addrolemember 'db_owner', 'YOUR_MACHINE_NAME\ASPNET';

-- 生產環境建議：僅給必要權限
EXEC sp_addrolemember 'db_datareader', 'YOUR_MACHINE_NAME\ASPNET';
EXEC sp_addrolemember 'db_datawriter', 'YOUR_MACHINE_NAME\ASPNET';
-- 若需執行預存程序
GRANT EXECUTE ON SCHEMA::dbo TO 'YOUR_MACHINE_NAME\ASPNET';

替代方案：SQL Server 驗證

若不想管理 Windows 帳號，可改用 SQL 登入：

1. SSMS → 安全性 → 登入 → 新增登入 → SQL Server 驗證，設定密碼。
2. 對應使用者至目標資料庫，給予適當角色。
3. 連接字串改為：

<connectionStrings>
  <add name="ConnStr" connectionString="
    Server=.;Database=YourDB;User Id=aspnet_user;Password=StrongPwd;
    TrustServerCertificate=True" />
</connectionStrings>

ASP.NET 偽裝模式

若應用程式需以特定網域使用者身分存取 SQL：

<system.web>
  <identity impersonate="true" userName="DOMAIN\user" password="pwd" />
</system.web>

連接字串維持 Integrated Security=SSPI，SQL Server 授權該網域使用者即可。

連接字串常見錯誤檢查清單

進階排查工具

1. SQL Server Profiler / Extended Events：擷取 Audit Login Failed 事件，確認失敗帳號與錯誤代碼。
2. SQL Server Configuration Manager：確認「SQL Server Network Configuration」→「Protocols for MSSQLSERVER」→ TCP/IP 已啟用，IPAll TCP Port 設為 1433。
3. Telnet / Test-NetConnection：驗證連接埠通達性 tnc DB_SERVER -Port 1433。
4. 應用程式集區身分識別：IIS → Application Pools → Advanced Settings → Identity，確認是否為 ApplicationPoolIdentity 或自訂帳號。

最佳實務建議

• 生產環境勿使用 db_owner，遵循最小權限原則。
• 連接字串密碼勿硬編碼，使用 aspnet_regiis -pef connectionStrings 加密 web.config 區段。
• 啟用連接池（預設開啟），連接字串加入 Max Pool Size=100; Min Pool Size=5 依負載調整。
• 監控 sys.dm_exec_sessions 觀察連接池使用狀況。