Impersonate user for SQL connection strings c# MVC

Question

I have couple of connection strings in web.config, ideally using those connection strings and the credentials specified (in web.config) I will have to open a connection and do an insert - for both the connections (lets says connection1 and connection 2).

Now connetcion1 and connection2 have different credentials. I have tried using them in the connection string of web.config but it always says login failed for user. Below are the connection strings.

<connectionStrings>
<add name="connection_2" connectionString="Data Source=domain\servername;Initial Catalog=DBName;User ID=domain\xxxx;Password=abcgdd****;"  providerName="System.Data.SqlClient"/>

<add name="connection_3" connectionString="Data Source=domain\servername;Initial Catalog=DBName;User ID=domain\yyyy;Password=fgdd****;"  providerName="System.Data.SqlClient"/>
<connectionStrings>

So after some googling understood that I have to use impersonation.

Using the below code for impersonation

using (new Impersonator("username","domain","pwd"))
{
// trying to open connection 1 from web.config but it says no such user. 

using (SqlConnection connection = new SqlConnection("Connection1FromConfig"))
                            {
                                cmd = new SqlCommand("select * from abc", connection);
                                connection.Open();
                                cmd.ExecuteNonQuery();
                                connection.Close();
                                
                            }
}

Connection used while impersonation is :

 <add name="Connection2" connectionString="Data Source=domain\server;Initial Catalog=DB;"  providerName="System.Data.SqlClient"/>

`````

Used the code from here for impersonation class - https://daoudisamir.com/impersonate-users-in-c/

for impersonation of a domain user to work, the machine running the Impersonator must be a member of the domain, and the user under which the process runs should also be either a domain member or else the computer account -- i.e. if in IIS run the app pool under app pool identity or network_service — Ross Presser
– Ross Presser, Commented Aug 25, 2020 at 14:14

Jawad · Accepted Answer · 2021-06-05 01:32:11Z

2

You need to provide Trusted_Connection=yes; in your connection string if you intend to use Windows Credentials with impersonate.

This is my connectionString that I used successfully,

string srvConnection = $"Server={serverName}; Trusted_Connection=yes; connection timeout=15";

I got the Impersonator class from here and was able to use it successfully. SQL account I needed to use was different than the domain account being used with IIS / app pool.

string srvConnection = $"Server={ListenerName}; Trusted_Connection=yes; connection timeout=15";
using (SqlConnection connection = new SqlConnection(srvConnection)) 
{
    using (new Impersonator(creds.Username.Split('\\').Last(), creds.Username.Split('\\').First(), creds.Password))
        connection.Open();

    // other code that needs to use the connection goes here.
    cmd = new SqlCommand("select * from abc", connection);
    cmd.ExecuteNonQuery();
}

You dont need to close the connection because of the using statement. Since impersonation is only required to open the connection, you dont need to use it anywhere other than with connection.Open(); statement.

edited Jun 5, 2021 at 1:32

answered Jun 4, 2021 at 19:51

Jawad

11.4k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen Ostermiller Over a year ago

The link in this post still works and you can see the article there, but the download link within that article for the code no longer works.

Collectives™ on Stack Overflow

Impersonate user for SQL connection strings c# MVC

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related