How do you impersonate as a different user/service id and then try and connect to sql server using windows authentication?

Question

This is my connection string
Data Source =server;Initial Catalog=Database;Trusted_Connection=True;MultipleActiveResultSet=true;Integrated Security=SSPI;
After establishing the connection string I am trying to test connection and here instead of it using my id to check whether I have access to the server I need it to check for the service id I am providing to test and establish connection

Answer 1

Trusted_Connection=True

That means the current Windows account is used to logon to SQL Server and if you e.g. have a SQL Server-Agent job, the the service account of the Agent is used. So run the job to see if it works = testing

Integrated Security=SSPI;

That's the same as aTrusted_Connection, so redundant.

Answer 2

if you create a web application, the app pool account is the account used to connect to sqlserver. if you are writing a desktop application, then you will need to impersonate the service account. this will require the user have the impersonate permission. your app will need the service account name and password.

[DllImport("advapi32.dll", SetLastError = true, CharSet = CharSet.Unicode)]  
public static extern bool LogonUser(String lpszUsername, String lpszDomain, String lpszPassword,  
        int dwLogonType, int dwLogonProvider, out SafeAccessTokenHandle phToken);  

...

const int LOGON32_PROVIDER_DEFAULT = 0;  
//This parameter causes LogonUser to create a primary token.   
const int LOGON32_LOGON_INTERACTIVE = 2;  

// Call LogonUser to obtain a handle to an access token.   
SafeAccessTokenHandle safeAccessTokenHandle;  
bool returnValue = LogonUser(
    serviceAccountUserName, 
    domainName, 
    password,  
    LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT,  
    out safeAccessTokenHandle);  

WindowsIdentity.RunImpersonated(SafeAccessTokenHandle, () =>
{
    // run sql queries here
});

Answer 3

Hi @Adithyan

According to your description, I have a scheme:

1.In order to achieve the function of user input query string, we can add a TextBox to get the input string.

2.Depending on your authentication needs, we can put "UserID" and "ServiceID" in a table in a database. We first enter "UserID" and "ServiceID" to match. At the same time, we put "ServiceID" and "ServiceName" in a database table. If we verify successfully in the previous step, we can get "ServiceName" through "ServiceID" here. If the validation fails, "Fail" is displayed directly.

3.Finally, we define "ServiceName" in a string, and then use the query string entered earlier in "TextBox" to complete the operation using the database.

4.Finally, we can use the "GridView" to get the database query results.

Code:

public partial class SQLTest : System.Web.UI.Page
    {
        
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
             string connString1 = "Data Source=localhost;Initial Catalog=TestSQL;Integrated Security=TRUE";
            using (SqlConnection conn = new SqlConnection(connString1))
            {
                conn.Open();
                SqlCommand sqlcom = new SqlCommand("select  count(*) from dbo.Service where UserID = '"+TextBox2.Text+ "' AND ServiceID='"+TextBox3.Text+"' ", conn);
                int count = Convert.ToInt32(sqlcom.ExecuteScalar());
                if (count>0)
                    {
                    TextBox4.Text = "Success";
                    string connString2 = "Data Source=localhost;Initial Catalog=TestSQL;Integrated Security=TRUE";
                    using(SqlConnection conn1=new SqlConnection(connString2))
                    {
                        conn1.Open();
                        SqlCommand sqlcom1 = new SqlCommand("select ServiceName from dbo.ServiceName where ServiceID='" + TextBox3.Text + "'", conn1);
                        string DatabaName=Convert.ToString(sqlcom1.ExecuteScalar());
                        TextBox1.Text= DatabaName;
                        string connString3 = "Data Source="+DatabaName+";Initial Catalog=TestSQL;Integrated Security=TRUE";
                        using (SqlConnection conn2 = new SqlConnection(connString3))
                        {
                            conn2.Open();
                            SqlCommand sqlcom2 = new SqlCommand(TextBox1.Text, conn2);
                            SqlDataReader sdr = sqlcom2.ExecuteReader();
                            GridView1.DataSource = sdr;
                            GridView1.DataBind();
                        }
                    }
                }
                else
                {
                    TextBox4.Text = "Fail";
                }
            }
        }
    }

<body>
    <form id="form1" runat="server">
        <div>
            <asp:label ID="Label1" Text="Please Enter SqlQueryString:" runat="server"></asp:label>
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        </div>
        <div>
            <asp:label ID="Label2" Text="Please Enter UserID" runat="server"></asp:label>
            <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
            <asp:label ID="Label3" Text="Please Enter ServiceID" runat="server"></asp:label>
            <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        </div>
        <div>
            <asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" />
        </div>
         <div>
            <asp:label ID="Label4" Text="Result" runat="server"></asp:label>
            <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
        </div>
        <div>
            <asp:GridView ID="GridView1" runat="server" text-align="center" style="margin: 0px auto;Font-Size:20px; border:3px;" ></asp:GridView>
        </div>
    </form>
</body>

Database data

Result:

Best regards,
Qi You

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.