title: ASP.NET impersonation from code (C#) and connected SQL Server Express user
body: <p>I'm having trouble with ASP.NET impersonation from code (C#) and using SQL Server Express:</p>
  
  <p>I'm able to impersonate successfully and connect to SQL Server, however executing <code>SELECT CURRENT_USER</code> returns <code>guest</code> instead of <code>TestUser_001</code>.</p>
  
  <p>Note: I've tried impersonation with <code>LOGON32_LOGON_NETWORK_CLEARTEXT</code> and then with <code>LOGON32_LOGON_INTERACTIVE</code> when calling <code>LogonUser</code> - the result remains the same. Account from which I'm executing this impersonation code has <code>Act as part of the operating system</code> set. Tested under IIS Express (F5 from Visual Studio 2013). I never logged in interactively as <code>TestUser_001</code> and connected to SQL Server Express instance - there is no <code>MY_DOMAIN\TestUser_001</code> under <code>Security\Logins</code> - would like for this to happen automatically when using the code. </p>
  
  <pre><code>using (var impersonation = new ImpersonationHelper("TestUser_001", "MY_DOMAIN", "pass"))
  {
      using (var conn = new SqlConnection(@"Data Source=MY_DOMAIN\SQLEXPRESS;Integrated Security=True;"))
      {
          conn.Open();
  
          using (var cmd = conn.CreateCommand())
          {
              cmd.CommandText = "SELECT CURRENT_USER";
              var result = cmd.ExecuteScalar();
          }
      }
  }
  </code></pre>
  
  <p>Here is the rudimentary <code>ImpersonationHelper</code> implementation:</p>
  
  <pre><code>public class ImpersonationHelper : IDisposable
  {
      public const int LOGON32_LOGON_INTERACTIVE = 2;
      public const int LOGON32_LOGON_NETWORK_CLEARTEXT = 8;
      public const int LOGON32_PROVIDER_DEFAULT = 0;
  
      [DllImport("advapi32.dll")]
      public static extern int LogonUser(
          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);
  
      private readonly WindowsImpersonationContext impersonationContext_;
  
      public ImpersonationHelper(string username, string domain, string password)
      {
          var token = IntPtr.Zero;
          var token_duplicate = IntPtr.Zero;
  
          if (RevertToSelf())
          {
              if (LogonUser(username, domain, password, LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, ref token) != 0)
              {
                  if (DuplicateToken(token, 2, ref token_duplicate) != 0)
                  {
                      var temp_windows_identity = new WindowsIdentity(token_duplicate);
                      impersonationContext_ = temp_windows_identity.Impersonate();
  
                      if (impersonationContext_ != null)
                      {
                          CloseHandle(token);
                          CloseHandle(token_duplicate);
                          return;
                      }
                  }
              }
          }
  
          if (token != IntPtr.Zero)
              CloseHandle(token);
  
          if (token_duplicate != IntPtr.Zero)
              CloseHandle(token_duplicate);
  
          throw new Exception(string.Format("Unable to impersonate as {0}\\{1}.", username, domain));
      }
  
      public void Dispose()
      {
          impersonationContext_.Undo();
      }
  }
  </code></pre>