Chapter 1__1.6 Connecting to SQL Server Using Integrated Security from ASP.NET（整合安全連線資料庫）

How do it ：

（1）建立一個解決方案

（2）在Web.config設定檔裡面的<system.web>節點裡面添加

             

<authentication mode="Windows" />
<identity impersonate="true" />

（3）添加連接字串：

     

<connectionStrings>
<add name="AdventureWorks" providerName="System.Data.SqlClient"
            connectionString="Data Source=(local);
            Integrated security=SSPI;Initial Catalog=AdventureWorks;"/>
    </connectionStrings>

執行個體

代碼

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace IntegratedSecurityFromAspNet
{
public partial class _Default : System.Web.UI.Page
    {
protected void Page_Load(object sender, EventArgs e)
        {
string sqlText = "SELECT TOP 10 * FROM Person.Contact";
string connectString = 
                ConfigurationManager.ConnectionStrings[
"AdventureWorks"].ConnectionString;
            DataTable dt = new DataTable(  );
            SqlDataAdapter da = new SqlDataAdapter(sqlText, connectString);
            da.Fill(dt);

foreach (DataRow row in dt.Rows)
                Response.Write(row["ContactID"] + " - " + row["LastName"] +
", " + row["FirstName"] + "<br/>");
        }
    }
}

下面

討論兩種驗證。windows驗證和sql驗證(SQL Server Authentication)

Integrated security requires that all application users are on the same domain so that their credentials are available to IIS. The following areas of the application need to be configured:

• Configure the ASP.NET application so that Integrated Windows Authentication is enabled and Anonymous Access is disabled.

• The web.config file establishes the authentication mode that the application uses and that the application will run as or impersonate the user. Add the following elements to the web.config file within the <system.web> element:

  <authentication mode="Windows" /><identity impersonate="true" />
   
  

• The connection string must contain attributes that tell the SQL Server that integrated security is used. Use the Integrated Security=SSPI attribute-and-value pair instead of the User ID and Password attributes in the connection string. The older attribute-and-value pair Trusted_Connection=Yes is also supported.

• Add users and groups from the domain and set their access permissions as required.

By default, ASP.NET applications run in the context of a local user ASPNET on IIS. The account has limited permissions and is local to the IIS computer and therefore not recognized as a user on remote computers. To use SQL Server on a different computer than IIS, run the web application in the context of a domain user recognized on both IIS and SQL Server computers.

In addition to the areas identified where IIS and SQL Server are on the same computer, the following additional items must be configured if the SQL Server is on a different domain:

• Ensure that the mapped domain user has required privileges to run the web application.

• Configure the web application to impersonate the domain user. Add the following elements to the web.config file for the web application:

  <authentication mode="Windows" />
  <identity impersonate="true" userName="domain\username"
              password="myPassword" />