.net impersonate not passing proper credentials to SQL Server

Question

I currently have a .net console application that needs to receive an active domain account via the appConfig file to connect to the database. The application is being executed from the TaskScheduler. I cannot use the domain account to execute the task as this is a security setting (saving password).

I have the connection string in a consolidated settings file (appSettings.config) and have set the identity in the console app settings file including the username and password

My question is how can I use the task scheduler to execute the job and have the username/password in the config files?

In testing I have used the "Local Service" account and "Network Service" account and receive an a logon error from SQL Server"

Login failed for user 'DOMAIN_NAME\MACHINE_NAME$'. Reason: Could not find a login matching the name provided. [CLIENT: xxx.xxx.xx.xx (ip address of client machine)]

If I use a local account that has admin rights, the following error is returned:

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: xxx.xxx.xx.xx]

NOTES:

all machines are on the same domain and have connectivity

when the task is set to run as the domain account, and the identity tag does NOT have the username/password, the task executes as designed.

appSettings.config

    <?xml version="1.0"?>
    <appSettings>    
        <!-- CONNECTION STRINGS -->
        <add key="connectionString"                 value="Data Source=DB_SERVER_NAME;Initial Catalog=DB_NAME;Integrated Security=SSPI;" />
.....
.....

application.exe.config

   <?xml version="1.0"?>
    <configuration>
      <configSections>

      </configSections>  
     <appSettings file="F:\SPASystems\Conf\appSettings.config" />
      <system.web>  
      <identity impersonate="true" userName="DOMAIN_NAME\svc.ACCOUNT_NAME.user" password="dummy_password"/>
        <membership defaultProvider="ClientAuthenticationMembershipProvider">
....
....

Answer 1

my final solution was to use impersonation and pass delegate the method to execute under the impersonated context. these are the code snippits i used to accomplish this:

internal class NativeMethods
    {
        // closes open handes returned by LogonUser
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        public extern static bool CloseHandle(IntPtr handle);

        // obtains user token
        [DllImport("advapi32.dll", SetLastError = true)]
        public static extern bool LogonUser(string pszUsername, string pszDomain, string pszPassword,
            int dwLogonType, int dwLogonProvider, ref IntPtr phToken);
    }

[PermissionSet(SecurityAction.Demand, Name = "FullTrust")]
    public sealed class Impersonation
    {
        /// <summary>
        /// impersonates a user based on username/password provided. executed method after impersonation and then reverts impersonation when task/method is complete.
        /// </summary>
        /// <param name="userName">username to impersonate</param>
        /// <param name="password">password for user account</param>
        /// <param name="domain">domain of user to impersonate</param>
        /// <param name="action">method to invoke after impersonation</param>
        /// <param name="logonType">LogonType to use, defaulted to Network</param>
        /// <param name="logonProvider">LogonProvider type, defaulted to default</param>
        public static void impersonate(string userName, string password, string domain, Action action, int logonType = 2, int logonProvider = 0)
        {
            //elevate privileges before doing file copy to handle domain security
            WindowsImpersonationContext context = null;
            IntPtr userHandle = IntPtr.Zero;
            try
            {
                Console.WriteLine("windows identify before impersonation: " + WindowsIdentity.GetCurrent().Name);
                // Call LogonUser to get a token for the user
                bool loggedOn = NativeMethods.LogonUser(userName,
                                            domain,
                                            password,
                                            logonType,
                                            logonProvider,
                                            ref userHandle);
                if (!loggedOn)
                {
                    Console.WriteLine("Exception impersonating user, error code: " + Marshal.GetLastWin32Error());
                }

                // Begin impersonating the user
                context = WindowsIdentity.Impersonate(userHandle);

                Console.WriteLine("windows identify after impersonation: " + WindowsIdentity.GetCurrent().Name);                
                //execute actions under impersonated user
                action();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception impersonating user: " + ex.Message);
            }
            finally
            {
                // Clean up
                if (context != null)
                {
                    context.Undo();
                }

                if (userHandle != IntPtr.Zero)
                {
                    NativeMethods.CloseHandle(userHandle);
                }
            }
        }

i was then able to call and use the impersonation context in the following way:

Impersonation.impersonate(impersonationUserName, impersonationPassword, impersonationDomain, () => processReport(args));

the method processReport(string[] args) is then executed under the context using the account information provided in the domain, username and password. these can be secure strings too if needed.

Answer 2

If it works when the process runs under domain account but does not work when it is impersonating a domain account, I am pretty sure this is because impersonation does not flow from machine to machine. From MSDN:

Impersonation flows the original caller's identity to back-end resources on the same computer. Delegation flows the original caller's identity to back-end resources on computers other than the computer running the service.

Your SQL Server is probably on a different machine so although you are impersonating, when the process starts communicating with another machine, it starts using the original account the process was started under.

Therefore, you need to use delegation.

You may also find this thread helpful.