Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Service Broker, exec SP at linked server in activation SP on Queue  [new]
Sergey_mnsk
Member

Откуда:
Сообщений: 7
Доброго дня всем.

Столкнулся со следующей задачей:

На локальном сервере есть N-linked серверов (все MS SQL 2008R2), необходимо запускать асинхронно процедуры на этих серверах, по некоторому событию. Пришел к выводу, что подходит service broker.

Создал необходимую обвязку (тип сообщения, контракт, два сервиса, две очереди, на принимающую очередь указал ХП ).
Тестовым примером обкатал схему "передал сообщение" - "разобрал сообщение в ХП, висящей на очереди" - "отправил ответ". Схема рабочая можно двигаться дальше.

В теле сообщения передаю строку на выполнение. В ХП, "обработчик" очереди принимающего сервиса, выполняю ( exec(@str) ), после выполнения делаю вставку информационного характера в таблицу - фиксирование времени выполнения (в релизе будет убрано, пока для статистики времени выполнения).

Вот тут и полезли черти из табакерки. Выходные провел на работе искал способы решения.

Немного информации:
Очередь принимающего сервиса:
CREATE QUEUE [dbo].[TargetQueue] 
WITH 
        STATUS = ON , 
        RETENTION = OFF , 
        ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[_ProcessBRequest] , MAX_QUEUE_READERS = 6 
, EXECUTE AS N'dbo'  ) ON [PRIMARY] 


ХП на этой же очереди

	
...
RECEIVE TOP(1)
		@TargetDlgHandle=Conversation_Handle
		,@ReplyMessage=Message_Body
		,@ReplyMessageName=Message_Type_Name
		FROM TargetQueue
...
/* блок проверок какое сообщение, разбор XML-сообщения, формирование строки на выполнение с учетом переданных параметров*/
...
/* фиксируем время начала выполнения */
insert into _Request([DateTime],message_body) select GETDATE(), N'Start executing SQL-string'
begin try
        /* выполняем строку */
        execute(@str) --AS LOGIN = 'TestUser';
end try
begin catch
        /* если обвалились, записываем сообщение ошибки (сугубо информативность) */
        insert into _Request([DateTime], message_body) select getdate(), ERROR_MESSAGE ()
end catch
/* фиксируем время выполнения */
insert into _Request([DateTime],message_body, conversation_handle, message_type)
select GETDATE(), @ReplyMessage, @TargetDlgHandle,@ReplyMessageName	
...
/* отправка обратного сообщения  */


В целом чаще ловил "Access to the remote server is denied because the current security context is not trusted". По результам поисков попробывал следующее:

EXEC master.dbo.sp_addlinkedsrvlogin N'linkedSrv1',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='password'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linkedSrv1',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
alter database dbname set trustworthy on


Результат:
При set trustworthy off - ошибка та же,что собственно логично. Изменил на "on" - ошибки нет, но и результата записи перед и после строки exec тоже нету. Очереди пустые. Этот результат мне вообще не понятен, кто-нибудь сталкивался с подобным ? Может я не учел какой-то параметр ?

потом я попробывал, по примеру из МСДН, создать сертификат, пользователя, логин и подписать ХП, которую необходимо выполнить.

Результат: при set trustworthy off - я получаю сообщение "Cannot execute as the server principal because the principal "TestUser" does not exist, this type of principal cannot be impersonated, or you do not have permission". При значении параметра - "on", очереди пустые, записей в таблице _Request отсутствуют.

Параметр для Linked server "Устанавливать с использованием следующего контекста безопастности" установлен "sa"

Буду признателен за свежий взгляд, т.к. в голове присутствует сумбур после выходных.

Заранее спасибо откликнувшимся.
2 апр 12, 16:12    [12352365]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
Sergey_mnsk
Member

Откуда:
Сообщений: 7
Забыл добавить:
Оба сервиса на одном инстансе. никаких роутов и прочего не использовалось.
Схема действий: сообщение в очередь - обработал( выполнил указанную ХП на удаленном ) - сообщение "процедура отработала" обратно.
2 апр 12, 16:19    [12352422]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
hpv
Member

Откуда:
Сообщений: 153
Sergey_mnsk,

1. У вас оцередь нормально заполняется без процедуры (которая висит на очереди)?
2. Мб у вас заходит в процедуру и падает до блока catch, вы смотрели логи?
3. Я думаю сначала стоит потестить процедуру без использования линкед серверов.
2 апр 12, 16:58    [12352730]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
Sergey_mnsk
Member

Откуда:
Сообщений: 7
hpv
1. У вас очередь нормально заполняется без процедуры (которая висит на очереди)?
2. Мб у вас заходит в процедуру и падает до блока catch, вы смотрели логи?
3. Я думаю сначала стоит потестить процедуру без использования линкед серверов.


1. Да. я проверял, изменив STATUS off в Activation. Сообщения в очереди есть.
2. Я закомментировать строку exec(@str); записи в таблице _Request появились, но самого выполнения строки с вызовом ХП не было. Если "падением" можно назвать exec(@str), то падает ХП "целиком", а не "падает до блока catch". Обработку XML убрал, оставил: получил сообщение, его сразу же на выполнение, чтобы избежать лишних проблем. Результат нулевой.
3. Процедура, вызываемая с удаленного сервера, отдельно работает. Проводил так же тест: изменил status c on на off, далее просто вызывал ХП, которая обрабатывает сообщения очереди. Как ни странно, но я получил нужный мне результат. Все ХП на удаленных серверах отработали.

Резюмируя: каким образом Service Broker выполняет ХП, которую я вызываю внутри ХП-обработчика очереди? Вроде бы на очереди стоит
execute as N'dbo'
, а этот пользователь - представление 'sa' для базы. Так же пробывал "протирать фары" и добавлял в объекты связанные с Service Broker (контракт,сервис, очередь ... ) строку authorization on 'dbo'

Хочу отметить, что кроме этой заморочки у меня есть функционал, который спокойно получается данные из функций на удаленных серверах(linked) при помощи openquery и даже не морщиться на политику безопасности...
2 апр 12, 17:32    [12352972]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
hpv
Member

Откуда:
Сообщений: 153
Sergey_mnsk,

а что покажет в этой процедуре ORIGINAL_LOGIN()?
и мб выставить EXECUTE AS OWNER
2 апр 12, 17:42    [12353050]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
Sergey_mnsk
Member

Откуда:
Сообщений: 7
Хм... добавил запись значения ORIGINAL_LOGIN() в таблицу _Request внутри ХП-обработчика очереди. Результат - пусто.
Изменил execute as owner... Сейчас добавлю authorization on ... и отправлю в рестарт инстанс. может поможет.
у брокера есть неготивный момент - порой альтер не помогает и приходиться перезапускать инстанс.
2 апр 12, 17:51    [12353134]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
hpv
Member

Откуда:
Сообщений: 153
Sergey_mnsk,

я имел ввиду просто
select ORIGINAL_LOGIN()
2 апр 12, 17:56    [12353167]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
Sergey_mnsk
Member

Откуда:
Сообщений: 7
hpv
Sergey_mnsk,

я имел ввиду просто
select ORIGINAL_LOGIN()


Если процедуру вызывать из Management Studio,то показывается тот логин под который открыто мое соединение - 'sa'
Или я не правильно понял ?
2 апр 12, 18:05    [12353218]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
hpv
Member

Откуда:
Сообщений: 153
Sergey_mnsk,

Да, я не правильно написал)
Нужно было добавить в таблицу _Request этот логин.
Например как message_body.
2 апр 12, 18:08    [12353229]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
Sergey_mnsk
Member

Откуда:
Сообщений: 7
я написал выше, при запуске ХП-обработчика на очереди значение пустое, даже не null.
2 апр 12, 18:18    [12353281]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
hpv
Member

Откуда:
Сообщений: 153
Sergey_mnsk,

чудес не бывает)
Вы писали, что после того как закомментили exec(@str) записи в requests появились.
Но после добавления original_login перестали добавляться?
2 апр 12, 18:26    [12353312]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
Sergey_mnsk
Member

Откуда:
Сообщений: 7
hpv
Sergey_mnsk,

чудес не бывает)
Вы писали, что после того как закомментили exec(@str) записи в requests появились.
Но после добавления original_login перестали добавляться?


при set trushworthy off для базы в которой вызывается ХП и excute as owner для очереди, где висит ХП-обработчик:
original_login() возвращает пустое значение, и если оставить exec(@str), то
Access to the remote server is denied because the current security context is not trusted.

при set trushworthy on, execute as owner:
записей в _request вообще нету.

аналогичное состояние, только ХП-обработчик отлючена. и я её вызываю из Management Studio, то данные есть. original_login() возвращает 'sa', процедуры на удаленном сервере работают.

Не получается ли так, что Service Broker по какой-то причине не передает\не принимает\ не знает под каким "логином" ему работать ?
Остается вариант execute as caller для очереди....
2 апр 12, 18:49    [12353406]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
hpv
Member

Откуда:
Сообщений: 153
Sergey_mnsk,

Вы имеете ввиду AS SELF?
И как результат?
3 апр 12, 12:34    [12356211]     Ответить | Цитировать Сообщить модератору
 Re: Service Broker, exec SP at linked server in activation SP on Queue  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Есть пользовтель, от чего имени запускается вервер (сервис SQL)
Есть владелцы баз (см свойства у них)
Есть свойство доверия к этой базе (TRUSTWORTHY) и олицетворение цепочек владения (DB_CHAINING)
Есть олицетворения к внешним системам (Proxy Account: CmdShell ...)
Есть свойства процедуры от чъего имени запускать (от имени: запускающего / владельца процедуры / конкретного пользователя)
Есть олицетворения для Linked серверов

Вам нужно понимать как это всё завязано.

Вы написали EXECUTE AS N'dbo', это тоже самое что и EXECUTE AS OWNER, т.е. владельца базы (dbo).
3 апр 12, 17:42    [12359422]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить