What is the magic to Windows impersonation with LO

Question

From my reading on user impersonation on Windows, one should properly use the LOGON32_LOGON_NEW_CREDENTIALS logon type for impersonating a user to a database. Using Matt Johnson's nice impersonation wrapper (originally posted here and then polished up here), I tried to test this out--here is my entire program except for constants defining my particular DOMAIN, USER, PWD, and CONN_STRING.

using System;
using System.Data.SqlClient;
using SimpleImpersonation;

namespace ImpersonationDemo
{
    class Program
    {
        private static SqlConnection _connection;

        static void Main(string[] args)
        {
            using (Impersonation.LogonUser(
                    DOMAIN, USER, PWD, LogonType.NewCredentials))
            {
                GetOpenConnection();
                CheckDbCredentials();
                CloseConnection();
            }
            Console.WriteLine("Press return to exit");
            Console.ReadLine();
        }

        private static void CheckDbCredentials()
        {
            using (
                var command = new SqlCommand(
                    "SELECT nt_user_name, SUSER_SNAME() "
                    +"FROM sys.dm_exec_sessions WHERE session_id = @@SPID",
                    _connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("{0}, {1}",
                            reader.GetString(0), reader.GetString(1));
                    }
                }
            }
        }


        private static void GetOpenConnection()
        {
            _connection = new SqlConnection(CONN_STRING);
            _connection.Open();
        }

        private static void CloseConnection()
        {
            _connection.Close();
        }
    }
}

But that does not work. The output reports me (my underlying logged in user) from both nt_user_name and SUSER_NAME(). (And SQL Profiler reports exactly the same thing; the query in code is simply a convenient way to see what SQL Profiler tells me.)

If I change from LogonType.NewCredentials to LogonType.Interactive (these enums have the values you would expect, as defined on pinvoke.net), then it does work--the above code reports the correct DOMAIN and USER impersonation. But this also means the current session is being impersonated which I do not want--I only want the DB connection to be impersonated.

I thought I found one glitch in the above--Johnson's Impersonation wrapper hard-codes the logon provider as LOGON32_PROVIDER_DEFAULT, when the LogonUser API clearly states that the LOGON32_LOGON_NEW_CREDENTIALS logon type is supported only by the LOGON32_PROVIDER_WINNT50 logon provider. So I grabbed the source and added a parameter to allow specifying the requisite logon provider... but that made no difference.

So what am I missing?

Answer 1

The answer, I am ashamed to say, was right in front of me all along. The LogonUser API states:

This logon type allows the caller to clone its current token and specify new credentials for outbound connections. The new logon session has the same local identifier but uses different credentials for other network connections. [emphasis mine]

But my database is on the same machine as my running program so by definition it will not show the new credentials! I am confident the impersonation will work correctly with LOGON32_LOGON_NEW_CREDENTIALS once I move my database to a different box. Sigh.