Login using the active directory in ASP.NET

I evaluated the project that I leave @Pikoh and it's fine to some extent, but as you indicate, it still does not fulfill all of your problems.

I took the audacity to take this project as a base and make some modifications to try to bring it closer to what you need, adding a class called Impersonator that we will use every time we need to access the base of data. I hope this option comes closer to what you need

global.asax

  

It is left without methods (We do not need to add code here)

web.config (Notese impersonate="false")

<?xml version="1.0"?>
<configuration>
  <system.web>
    <authentication mode="Forms">
      <forms loginUrl="logon.aspx" name="adAuthCookie" timeout="10" path="/"/>
    </authentication>
    <authorization>
      <deny users="?"/>
      <allow users="*"/>
    </authorization>
    <identity impersonate="false"/>
    <compilation debug="true"/>
  </system.web>
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
  </system.webServer>
</configuration>

The Impersonator.cs class is created in the root of the project

Namespace

using System;
using System.Web;
using System.Security.Principal;
using System.Runtime.InteropServices;

Class

public class Impersonator
{
    public const int LOGON32_LOGON_INTERACTIVE = 2;
    public const int LOGON32_PROVIDER_DEFAULT = 0;

    static WindowsImpersonationContext impersonationContext;

    [DllImport("advapi32.dll")]
    public static extern int LogonUserA(String lpszUserName,
    String lpszDomain,
    String lpszPassword,
    int dwLogonType,
    int dwLogonProvider,
    ref IntPtr phToken);

    [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
    public static extern int DuplicateToken(IntPtr hToken,
    int impersonationLevel,
    ref IntPtr hNewToken);

    [DllImport("advapi32.dll", CharSet = CharSet.Auto, SetLastError = true)]
    public static extern bool RevertToSelf();

    [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
    public static extern bool CloseHandle(IntPtr handle);

    public static string Domain
    {
        get
        {
            return HttpContext.Current.Session["domain"].ToString();
        }
        set
        {
            HttpContext.Current.Session["domain"] = value;
        }
    }

    public static string UserName {
        get
        {
            return HttpContext.Current.Session["userName"].ToString();
        }
        set
        {
            HttpContext.Current.Session["userName"] = value;
        }
    }

    public static string Password
    {
        get
        {
            return HttpContext.Current.Session["password"].ToString();
        }
        set
        {
            HttpContext.Current.Session["password"] = value;
        }
    }


    public static bool ImpersonateValidUser()
    {
        WindowsIdentity tempWindowsIdentity;
        IntPtr token = IntPtr.Zero;
        IntPtr tokenDuplicate = IntPtr.Zero;

        if (RevertToSelf())
        {
            if (LogonUserA(UserName, Domain, Password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref token) != 0)
            {
                if (DuplicateToken(token, 2, ref tokenDuplicate) != 0)
                {
                    tempWindowsIdentity = new WindowsIdentity(tokenDuplicate);
                    impersonationContext = tempWindowsIdentity.Impersonate();
                    if (impersonationContext != null)
                    {
                        CloseHandle(token);
                        CloseHandle(tokenDuplicate);
                        return true;
                    }
                }
            }
        }

        if (token != IntPtr.Zero)
            CloseHandle(token);
        if (tokenDuplicate != IntPtr.Zero)
            CloseHandle(tokenDuplicate);
        return false;
    }

    public static void UndoImpersonation()
    {
        impersonationContext.Undo();
    }
}

Logon.aspx

<%@ Page language="c#" AutoEventWireup="true" %>
<%@ Import Namespace="WebApplication1" %>

<script runat="server">
    protected void Login_Click(object sender, EventArgs e)
    {
        Impersonator.Domain = txtDomain.Text;
        Impersonator.UserName = txtUsername.Text;
        Impersonator.Password = txtPassword.Text;

        if(Impersonator.ImpersonateValidUser())
        {
            //Insert your code that runs under the security context of a specific user here.

            //Create the ticket, and add the groups.
            bool isCookiePersistent = chkPersist.Checked;
            FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(1,
                        txtUsername.Text,DateTime.Now, DateTime.Now.AddMinutes(60), isCookiePersistent, string.Empty);

            //Encrypt the ticket.
            string encryptedTicket = FormsAuthentication.Encrypt(authTicket);

            //Create a cookie, and then add the encrypted ticket to the cookie as data.
            HttpCookie authCookie = new HttpCookie(FormsAuthentication.FormsCookieName, encryptedTicket);

            if(true == isCookiePersistent)
                authCookie.Expires = authTicket.Expiration;

            //Add the cookie to the outgoing cookies collection.
            Response.Cookies.Add(authCookie);

            //You can redirect now.
            Response.Redirect(FormsAuthentication.GetRedirectUrl(txtUsername.Text, false));


            // Logout impersonation                
            //undoImpersonation();                        
        }
        else
        {
            //Your impersonation failed. Therefore, include a fail-safe mechanism here.
        }
    }
</script>

<html>
  <body>
    <form id="Login" method="post" runat="server">
      <asp:Label ID="Label1" Runat=server >Domain:</asp:Label>
      <asp:TextBox ID="txtDomain" Runat=server ></asp:TextBox><br>    
      <asp:Label ID="Label2" Runat=server >Username:</asp:Label>
      <asp:TextBox ID=txtUsername Runat=server ></asp:TextBox><br>
      <asp:Label ID="Label3" Runat=server >Password:</asp:Label>
      <asp:TextBox ID="txtPassword" Runat=server TextMode=Password></asp:TextBox><br>
      <asp:Button ID="btnLogin" Runat=server Text="Login" OnClick="Login_Click"></asp:Button><br>
      <asp:Label ID="errorLabel" Runat=server ForeColor=#ff3300></asp:Label><br>
      <asp:CheckBox ID=chkPersist Runat=server Text="Persist Cookie" />
    </form>
  </body>
</html>

Default.aspx (Here you must change the connection information to SQL)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.Default" %>
<%@ Import Namespace="WebApplication1" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "System.Data.SqlClient" %>

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Impersonator.ImpersonateValidUser()) {
            SqlConnection sql = new SqlConnection("Data Source=SERVER;Initial Catalog=DATABASE;Trusted_Connection=Yes");
            SqlDataAdapter da = new SqlDataAdapter("Select * from Table", sql);
            DataTable dt = new DataTable();
            da.Fill(dt);
            Response.Write(dt.Rows.Count);

            // Reestablece las credenciales
            Impersonator.UndoImpersonation();
        }        
    }
</script>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>    
    </div>
    </form>
</body>
</html>

Actually, the process is not very complicated. In your question do not indicate if you already have the part of making the login against the Active Directory. If not, you can simply follow the process explained here: Authentication of Services Domain Active Directory from ASP .NET .

Once you have the login part (the part of <identity impersonate="true"/> is very important, since it is what makes the asp.net process use the identity of the logged in user), making the login in the SqlServer is very simple. You just have to add in the connection string Trusted_Connection=Yes , so windows authentication is used. It would be something like this:

SqlConnection conn = new SqlConnection("Data Source=servidor;Initial Catalog=baseDatos;Trusted_Connection=Yes");
SqlDataAdapter da = new SqlDataAdapter("Select * from tabla", conn);
DataTable dt = new DataTable();
da.Fill(dt);

Note that you must have the windows authentication mode activated on the SqlServer server for this to work.

References: How to: Access SQL Server using integrated Windows security

EDIT

I paste here the web.config that I used for my test:

  <?xml version="1.0"?>
  <configuration>
    <system.web>
      <authentication mode="Forms">
        <forms loginUrl="logon.aspx" name="adAuthCookie" timeout="10" path="/"/>
      </authentication>
      <authorization>
        <deny users="?"/>
        <allow users="*"/>
      </authorization>
      <identity impersonate="true"/>
      <compilation debug="true"/>
    </system.web>
    <system.webServer>
      <validation validateIntegratedModeConfiguration="false" />
    </system.webServer>
  </configuration>