6

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?

Community
  • 1
  • 1
Michael Sorens
  • 35,361
  • 26
  • 116
  • 172
  • Taking a step backwards from the issue: If you're going to logon at your DB server instance using Integrated Security, you shouldn't have to rely on impersonation at all; in that case, **1.** using a SQL Server login would be the easier option. If you're using Integrated Security, **2.** simply allow the target users to login (but restrict their rights on the server & database as much as possible); or (even better:) **3.** only let a service access the database, and have your application access only the service, which performs its own authentication & authorization. – stakx - no longer contributing Aug 07 '13 at 08:00
  • 1
    I appreciate the suggestions, @stakx; however, in this instance I do not have the flexibility to make those architectural changes. And besides, I may not ever be able to get a sound night's sleep again until I find out why the above fails :-) ! – Michael Sorens Aug 07 '13 at 19:01
  • What happens if you use C to get a LOGON32_LOGON_NEW_CREDENTIALS token and then launch a separate process with the token to do the SQL work on your behalf? (The new process could be in C#.) – Harry Johnston Aug 08 '13 at 00:09

1 Answers1

5

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.

Michael Sorens
  • 35,361
  • 26
  • 116
  • 172