How to Use Windows Authentication to Access SQL Server Through a ASP.NET Web Application

This sample application demonstrates how to use Windows authentication for Web-based intranet applications to access a SQL Server database using ASP.NET. Most of the intranet Web applications prefer to use Windows authentication because they are going to used by internal users.

We need to configure the following four different areas to access Windows integrated security:

  1. SQL Server
  2. IIS Web Server
  3. ASP.Net web application
  4. ConnectionString

SQL Server be running on same IIS machine. If both are on different machines, we should go for analternative security model such as Forms authentication, which is not covered in this article. The access users must be in the same domain where the Web server is running.

Configuring SQL Server

To configure SQL Server for Windows integrated security:

  1. From the Windows Start menu, choose Microsoft SQL Server, and then choose Enterprise Manager.
  2. Open the node for the server and expand the node for the database you want to give users permissions for.
  3. Right-click the Users node and choose New Database User.
  4. In the Database User Properties dialog box, enter domain\username in the Login name box, and then click OK. Alternatively, configure the SQL Server to allow all domain users to access the database.

Configuring IIS

You need to configure your application in IIS to turn off anonymous access and turn on Windows authentication. To configure IIS for Windows integrated security:

  1. In Windows, open the Internet Information Services administration tool.
  2. Open the node for your server, and then open nodes until you find the node for your application, typically under Default Web Site.
  3. Right-click your application and choose Properties.
  4. In the Directory Security tab, click Edit.
  5. In the Authentication Methods dialog box, clear the Anonymous Access box and make sure Integrated Windows authentication is checked.
  6. Click OK to close all the dialog boxes.

Configuring the ASP.NET Web Application

In the application configuration file (Web.config), you establish the authentication mode that your application uses and establish that the application will impersonate the user's credentials—that is, that it will run as that user. To configure Web.config to allow Windows integrated security:

Open the Web.config file for your application and add the following elements to it:

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

The <authentication> element might already be there.

Creating Connection Strings

When you create a connection string to access SQL Server, you must include attributes that tell SQL Server that you are using Windows integrated security. To configure connection strings for Windows integrated security:

In any connection string for SQL Server, include the Trusted_Connection=Yes attribute and remove the username and password attributes. The following shows a typical connection string configured for Windows integrated security:

"data source=Sql01;initial catalog=Northwind;
 integrated security=SSPI;persist security info=False;
 Trusted_Connection=Yes."

Sample C# code for connecting SQL server from ASP.Net application using windows authentication:

private void DataBind() {
   sqlConnection = SqlConnection("data source=bondugula;
                                  initial catalog=Northwind;
                                  integrated security=SSPI;
                                  persist security info=False;
                                  Trusted_Connection=Yes");

   sqlConnection.Open();
      sqlDataAdapter = new SqlDataAdapter(
         "SELECT EmployeeID, FirstName, LastName, Title
          FROM Employees", sqlConnection);

   dataSet = new DataSet();
   sqlDataAdapter.Fill(dataSet, "Employees");
   DataGrid1.DataSource = dataSet.Tables["Employees"].DefaultView;
   DataGrid1.DataBind();
}

Important settings in the web.config file are as follows:

<system.web>
<authentication mode = "Windows"/>
<identity impersonate="true"/>

<authorization>
   <allow users = "*"/>
</authorization>
<!--other settings-->

</system.web>		
    


Comments

  • Thanks!

    Posted by elizaBennet on 08/02/2004 05:32pm

    Thanks so much! I was getting the error: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." when I tried to connect to a remote SQL Server. This completely solved my problem! You're a lifesaver. :)

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Finance leaders have been talking about expanding the value-added role of their teams for a long time. The debate is no longer whether the finance and accounting function needs to become a more strategic partner to the rest of the business but rather how to get there. Technology innovation has caught up to this ambition, and what was once aspiration can be a reality – and the choice is now yours. Read this research report to learn how to make the most of information tools to enable innovation and growth.

  • On-demand Event Event Date: March 19, 2015 The 2015 Enterprise Mobile Application Survey asked 250 mobility professionals what their biggest mobile challenges are, how many employees they are equipping with mobile apps, and their methods for driving value with mobility. Join Dan Woods, Editor and CTO of CITO Research, and Alan Murray, SVP of Products at Apperian, as they break down the results of this survey and discuss how enterprises are using mobile application management and private app stores to …

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date