We help IT Professionals succeed at work.

C# Console App impersonate

Brian Burton
Brian Burton asked
on
I have a c# console exe that runs under the logged in Windows user but when making calls I need the SQL call to use a different user.

logged in user: Corp\MyUser
Need to use different user to connect SQL:  Admin\ad-adminuser

In appconfig I have this:

  <system.web>
    <identity impersonate="true" userName="Admin\ad-adminuser" password="MyPassword" ></identity>
  </system.web>
  <connectionStrings>

    <add name="CS" connectionString="Data Source=MYSOURCE;Integrated security=True;
          Initial Catalog=MYCATALOG; UID=Admin\ad-adminuser;Password=MyPassword;" providerName="System.Data.SqlClient"></add>

And then using the connection string for SQL server:

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["CS"].ConnectionString))

And I get an error:
Login Failed for user 'Admin\ad-adminuser'
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2015

Commented:

1) You don't need impersonation to query the database if you're putting the username and password in your connection string. Having those in the connection string itself is enough. __IF__ you were going to use Trusted Connection in your connection string, then impersonation would be necessary (although I've never done that, so I can't confidently say that it would work).


2) "Login Failed for user" means either that account doesn't exist on your database server, or that user hasn't been given permission to the specific database on the server. Check permissions for that user on the server.

Author

Commented:
I verified the user has permission by using Shift right click on SSMS and run as different user.  Used the same name and password that is in the appconfig and SSMS connects to the database.

Author

Commented:
One thing to point out is the server is using Windows Authentication not SQL server which is why I wanted to impersonate the Windows user
This is very similar to ASP.NET behavior...

When we use Integrated Windows security in IIS,  IIS cannot delegate that user's credentials to a remote machine. So it always uses ASP.NET server name (<Domain>\<MachineName>$) to login SQL server.

But you can configure Kerberos authentication to delegate security credentials. For more information, see http://msdn.microsoft.com/en-us/library/ms998355.aspx

For more information about impersonation, see http://msdn.microsoft.com/en-us/library/ms998351.aspx

More info: https://forums.asp.net/t/1433961.aspx?Identity+Not+Used+for+SQL+Connection+String+Integrated+Security

Author

Commented:
I am not using IIS and I have no control or ability to configure Kerberos.  I do have the windows user password I am trying to impersonate in a c# executable console application.  I need to do this through code in C#.
I found a wrapper for the LogonUser function of the Win32 API that allows you to impersonate any user, as long as you have their credentials.