Impersonate user for SQL connection strings C# MVC

See more:

impersonation

connectionstring

Hi,

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/

What I have tried:

Connection used while impersonation is :

<add name="Connection2" connectionstring="Data Source=domain\server;Initial Catalog=DB;" providername="System.Data.SqlClient">