Chapter 12.161.6 connecting to SQL Server using integrated security from ASP. NET (Integrated Security Connection database)

How do it:

(1) create a solution

(2) InIn the Web. config configuration file<System. Web> Add

 <Authentication Mode="Windows" />
<Identity impersonate="True" />
 

(3) Add a connection string:

  <  Connectionstrings  >  
  <  Add name  =  "  Adventureworks  "  Providername  = "  System. Data. sqlclient  "  
Connectionstring  =  "  Data Source = (local );  
  Integrated Security  =  Sspi; initial catalog  =  Adventureworks;  " />  
    </  Connectionstrings  > 

Instance

Code

   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/>  "  );
}
}
}

 

Below

Two types of verification are discussed. Windows verification and SQL verification (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 thatIntegrated Windows AuthenticationIs enabled andAnonymous AccessIs disabled.

• TheWeb. configFile establishes the Authentication mode that the application uses and that the application will run as or impersonate the user. Add the following elements toWeb. configFile within<System. Web>Element:

       code highlighting produced by actipro codehighlighter (freeware) 
   http://www.CodeHighlighter.com/
   -->    Authentication mode  = "   Windows  "    />   identity impersonate  = "   true  "    />  
   
   
   

• The connection string must contain attributes that tell the SQL server that integrated security is used. UseIntegrated Security = sspiAttribute-and-Value Pair instead ofUser IDAndPasswordAttributes in the connection string. The older Attribute-and-Value PairTrusted_connection = YesIs 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 userASPnetOn 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 toWeb. configFile for the Web application:

       <  Authentication Mode  =  "  Windows "     />  
    <  Identity impersonate  =  "  True  "  Username  =  "  Domain \ Username  "  
  Password  = "  Mypassword  "     />