IIS Connect SQL Server With Windows Authentication

2021-07-16

教學如何在 IIS 的環境，使用 Windows 驗證連線 SQL Server 資料庫。示範的程式是以 ASP.NET MVC 5 進行示範，完成設定後，將可以使用 Windows 驗證的方式去存取資料庫，不需要額外在資料庫中安全性中新增 SQL Server 驗證。

同時也說明過程中碰到的 500.24 Http Status 等問題該如何進行處理 😎

說明

資料庫設定

本次使用的範例資料庫為 AdventureWorks，需要完成的步驟為安全性實體新增 Windows 驗證 Login，並設定對應的資料庫 Read / Write 權限。

應用程式

使用 Windows 驗證，加入 Model 使用 SQL Server 驗證。

詳細的步驟省略請參考 ASP.NET MVC EntityFramework Model

調整完 Web.config 後發佈至 Web Server，準備進行 IIS 的設定。

Web.config 設定

Web.Config

必須要加入 Impersonate 才能夠跨伺服器的使用 Windows 驗證存取 SQL Server。

  <system.web>
    <identity impersonate="true"/>
    <authentication mode="Windows" />
    <authorization>
      <deny users="?" />
    </authorization>
  </system.web>

此外必須另外設定加入 validateIntegratedModeConfiguration 以處理 500.24 問題。

<system.webServer>
  <validation validateIntegratedModeConfiguration="false"/>
</system.webServer>

Web.Release.Config

    <connectionStrings>
    <add name="AdventureWorksLT2019Entities" 
    connectionString="metadata=res://*/Models.Model1.csdl|
    res://*/Models.Model1.ssdl|
    res://*/Models.Model1.msl;
    provider=System.Data.SqlClient;
    provider connection string=
    &quot;data source=db.webber.local;
      initial catalog=AdventureWorksLT2019;
      Integrated Security=True;
      MultipleActiveResultSets=True;
      App=EntityFramework
    &quot;" 
    providerName="System.Data.EntityClient" 
    xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>
  </connectionStrings>

IIS 設定

連線身分調整

AD User 的密碼預設會保存於下列路徑：

echo C:\Windows\System32\inetsrv\Config\applicationHost.config

應用程式的集區身分不需要特別調整

驗證方式調整

ASP.NET Impersonate 必須要啟動，否則無法跨伺服器進行 Widnows 驗證存取資料庫。此外必須開啟 Windows Authentication。

成果

IIS 順利連到遠端資料庫 Barvo 🤓

同時驗證 Services 使用的身分為 ApplicationPool，而不是　Webber\Yi （Webber\Yi 用於與資料庫的連線使用）。

TroubleShooting

使用的身分權限不足存取 .NET Framework 資料夾

權限不足存取 .NET Framework 資料夾

處理方法：將 AD User 加入到本機伺服器的 IIS_IUSRS 群組中。

處理方法

Connect Faile With Wrong Identity

IIS 使用錯誤的身分 (WEBBER\W-DC2$) 去向 SQL Server 連線並請求資料。

處理方法：必須要加入 Impersonate 才能夠跨伺服器的使用 Windows 驗證存取 SQL Server。

  <system.web>
    <identity impersonate="true"/>
  </system.web>

500.24

在加入 Impersonate 到 web.config 後會衍生出 Http Status Code 500.24 的錯誤，有兩種方式可以修正。

Web.config 調整

在 Web.config 加入 validateIntegratedModeConfiguration 以處理 500.24 問題。本次的專案使用此方式進行處理，因為 Managed Pipe 調整為 Classic 整個就是覺得怪 😯

<system.webServer>
  <validation validateIntegratedModeConfiguration="false"/>
</system.webServer>

調整 Managed Pipe

調整為 Classic 即可，本次專案不採用這個方法。

參考資料

How to: Access SQL Server Using a Mapped Windows Domain User

黑暗執行緒 - IIS 與 web.config 的 Windows 驗證設定

小朱 | 你認為偷懶的方法卻是最安全的方法