prot prot - 3 years ago 184
C# Question

Running a t-sql stored procedure with EXECUTE AS statement with .NET SqlCommand

I have a .NET web service application that executes parameterized MS SQL Server stored procedures using System.Data.SqlCommand. The application receives a name of the stored procedure and its parameters from user input.

Also the application deployed within Windows AD domain and it is able to obtain the name of a remote user with the help of SSPI authentication.

using (var con = new SqlConnection(connectionString)) {
using (var cmd = new SqlCommand(procedureName, con)) {
cmd.CommandType = CommandType.StoredProcedure;
foreach (var pair in pairs.AllKeys) {
cmd.Parameters.Add(new SqlParameter(pair, pairs[pair]));
using (var reader = cmd.ExecuteReader()) {
// processing results

Now I want to execute a stored procedure with an EXECUTE AS statement.

use [db]
execute as user = 'domain\user' --execute as RemoteUser
exec [db].[stored_procdure] @param1=value1

Can this be done? How can I add EXECUTE AS to the SqlCommand?

I would not like to resort to sql injection prone code and build the sql request from strings received from user.

Answer Source

Solved it some time ago with a colleague of mine.

To achieve the requested behavior the execute as statement should be run in a separate preceeding SqlCommand while in the same SqlConnection.

After the closing of the reader, still in the same SqlConnection, there's another separate SqlCommand needed - revert - to switch back the context.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download