Запуск хранимой процедуры t-sql с оператором EXECUTE AS с помощью .NET SqlCommand

У меня есть приложение веб-службы .NET, которое выполняет параметризованные хранимые процедуры MS SQL Server с использованием System.Data.SqlCommand. Приложение получает имя хранимой процедуры и ее параметры из пользовательского ввода.

Также приложение развернуто в домене Windows AD и может получить имя удаленного пользователя с помощью аутентификации SSPI.

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]));
    }
    con.Open();
    using (var reader = cmd.ExecuteReader()) {
      // processing results
    }
  }
}

Теперь я хочу выполнить хранимую процедуру с оператором EXECUTE AS.

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

Можно ли это сделать? Как я могу добавить EXECUTE AS в SqlCommand?

Я не хотел бы прибегать к коду, подверженному инъекции sql, и создавать запрос sql из строк, полученных от пользователя.


person prot    schedule 30.06.2017    source источник
comment
почему бы вам не указать имя пользователя в строке подключения при создании объекта подключения sql, вы будете выполнять его как этот пользователь таким образом   -  person Basem Sayej    schedule 30.06.2017
comment
Используйте Windows Authentication для подключения к SQL Server, а не пользователя sa.   -  person Rand Random    schedule 30.06.2017
comment
Я не могу указать учетные данные пользователя в строке подключения, так как у меня их нет. У меня есть только имя пользователя в формате «домен\пользователь». Нет пароля.   -  person prot    schedule 30.06.2017
comment
@random-random Я уже использую аутентификацию Windows.   -  person prot    schedule 30.06.2017
comment
если вы используете аутентификацию Windows, то зачем вам выполнять от имени пользователя?   -  person GuidoG    schedule 30.06.2017
comment
Тогда зачем вам нужно выполнить, поскольку ваши команды уже выполняются как пользователь, который использует соединение?!?   -  person Rand Random    schedule 30.06.2017
comment
Расскажите нам, что делает ваша хранимая процедура или вы хотите, чтобы она делала, возможно, есть другое решение, о котором вы не подумали.   -  person Rand Random    schedule 30.06.2017
comment
Я использую учетную запись службы домена Windows для доступа к базе данных, затем я хочу выполнить хранимые процедуры с учетной записью удаленного пользователя. Таким образом, реализация RBAC устраняет проблему аутентификации с двойным переходом.   -  person prot    schedule 30.06.2017
comment
Хранимые процедуры манипулируют данными. Пользователи должны получать доступ только к тем, к которым им разрешен доступ.   -  person prot    schedule 30.06.2017
comment
Возможно, вы захотите настроить делегирование Kerberos, вот некоторые обсуждения: stackoverflow.com/questions/6077046/ . Однако помните, что это сложнее, чем кажется, поэтому сначала заручитесь помощью системного инженера, имеющего опыт работы с AD.   -  person Roger Wolf    schedule 30.06.2017
comment
@RogerWolf К сожалению, это не вариант, потому что я работаю в крупной компании, и токен Kerberos для некоторых пользователей может быть раздут выше MaxTokenSize. И на исправление потребуется гораздо больше времени, чем вы думаете support.microsoft.com/en- нам/help/327825.   -  person prot    schedule 30.06.2017
comment
Похоже, вам нужно два подключения к базе данных, каждое с разными учетными данными. Может быть проще использовать проверку подлинности Windows для подключения пользователя и учетную запись службы SQL Server вместо учетной записи службы домена для другого?   -  person Mic    schedule 30.06.2017
comment
@Mic Нет. Мне нужно ровно одно соединение на запрос пользователя. И я хочу всегда использовать проверку подлинности Windows с SQL Server. И он у меня уже есть. Мне нужно реализовать EXECUTE AS при вызове параметризованной хранимой процедуры.   -  person prot    schedule 30.06.2017
comment
@prot, вы действительно столкнулись с ограничением MaxTokenSize? Есть ли в вашей сети еще не исправленная Windows 2000? В любом случае, то, что вы хотите, невозможно, потому что, если бы кто-то мог создать произвольный токен Kerberos только из своего логина, это не называлось бы безопасностью.   -  person Roger Wolf    schedule 01.07.2017
comment
@RogerWolf, да для лимита и нет для win2k. Я работаю в огромной корпорации с довольно сложной моделью группы безопасности. Я не хочу повторно создавать токен Kerberos, я хочу воссоздать доверие на уровне приложения, а не на уровне сервера. Я доверяю приложению, поскольку я разработчик, и я доверяю ему доступ к базе данных с учетными данными (фактически идентификатором), которые оно получает от IIS.   -  person prot    schedule 04.07.2017


Ответы (2)


Решил это некоторое время назад с моим коллегой.

Для достижения запрошенного поведения оператор execute as должен выполняться в отдельной предшествующей SqlCommand, находясь в том же SqlConnection.

После закрытия reader, все еще в том же SqlConnection, требуется еще одна отдельная команда SqlCommand — revert — для обратного переключения контекста.

person prot    schedule 01.10.2017

У меня была аналогичная проблема, когда я мог выполнить от имени пользователя, но возврат не работал. Следуя ответу проца, я смог это исправить. Так что для тех, у кого есть похожая проблема, вот как выглядит код

   SqlCommand cmd = new SqlCommand("EXECUTE AS USER = 'domain\\user';");
   OSSDBDataContext dc = new OSSDBDataContext();
   cmd.Connection = dc.Connection as SqlConnection;
   cmd.Connection.Open();
   cmd.ExecuteNonQuery();

   //Execute stored procedure code goes here

   SqlCommand cmd2 = new SqlCommand("REVERT;");
   cmd2.Connection = dc.Connection as SqlConnection;
   cmd2.ExecuteNonQuery();
person Madhu    schedule 15.02.2018