ADO.NET 3.5 Cookbook, 2nd Edition

1.6. Connecting to SQL Server Using Integrated Security from ASP.NET

Problem

You want to coordinate Windows security accounts between an ASP.NET application and SQL Server.

Solution

Connect to SQL Server from ASP.NET using Windows Authentication in SQL Server:

1. Begin by creating a new ASP.NET Web Application project.

2. Add the following elements to the Web.config file within the <system.web> element:

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

3. Add a connection string to AdventureWorks on the local machine to the configuration file Web.config by updating the <connectionStrings> element within the <configuration> element as follows:

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

The C# code in Default.aspx.cs in the project IntegratedSecurityFromAspNet is shown in Example 1-7.

Example 1-7. File: Default.aspx.cs for IntegratedSecurityFromAspNet solution

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, ...