.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.