Сервисный брокер - выборки между базами данных для вставки в локальную БД?

Я использую статью Ремуса http://rusanu.com/2006/03/07/call-a-procedure-in-other-database-from-an-activated-procedure/ в качестве руководства.

Что я пытаюсь сделать:

У меня есть активированная хранимая процедура, которая (в активированном SP) вызывает 2 разные хранимые процедуры. Одной из этих хранимых процедур требуется доступ к таблицам в другой базе данных для поиска некоторых данных, после чего она записывает запись локально с дополнительной информацией. Поскольку я пересекаю базы данных, мои варианты

  • "заслуживающий доверия" (проходит, спасибо)
  • «реплицировать таблицы поиска в тот же брокер службы базы данных, в котором находится»
  • «получить рабочие сертификаты» (этот подход)

Итак, я сделал следующее, но все еще не работает с «превышением времени блокировки» и «невозможно получить доступ к базе данных... в текущем контексте безопасности.

В сервисном брокере базы данных работает:

USE database_with_service_broker_and_queue
GO
CREATE CERTIFICATE mysp_Auditing ENCRYPTION 
    BY PASSWORD = '123457' 
    with subject = 'god_i_hope_this_works'
ADD SIGNATURE TO OBJECT::myschema.mystoredprocedure 
    BY CERTIFICATE mysp_Auditing 
    WITH PASSWORD = '123457'
ALTER CERTIFICATE mysp_Auditing REMOVE PRIVATE KEY
BACKUP CERTIFICATE mysp_Auditing to file = 'c:\mysp_auditing.CER'

Теперь в базе с дополнительными таблицами:

USE db_with_tables_I_need
GO
CREATE CERTIFICATE mysp_Auditing from file = 'c:\mysp_auditing.CER'
CREATE USER mysp_Auditing FROM CERTIFICATE mysp_Auditing
GRANT AUTHENTICATE to mysp_Auditing
GRANT SELECT ON TABLE1 to mysp_Auditing
GRANT SELECT on TABLE2 to mysp_Auditing

Затем я ВКЛЮЧАЮ очередь и смотрю, как прокручиваются все сообщения об ошибках. Я даже пытался изменить хранимую процедуру, чтобы добавить EXECUTE AS OWNER, но все равно не работает.

Любая помощь приветствуется.


sql-server service-broker
person mbourgon    schedule 06.01.2015    source источник
comment
Когда вы нажмете время ожидания блокировки превышено? Во время выполнения (когда запускается активированная процедура) или во время развертывания (когда вы пытаетесь настроить сертификаты)?   -  person Remus Rusanu    schedule 07.01.2015
comment
Превышение времени ожидания блокировки связано с активированной процедурой.   -  person mbourgon    schedule 07.01.2015
comment
Проголосовал за Бога, надеюсь, это сработает   -  person Prisoner ZERO    schedule 20.12.2018

Ответы (2)


arrow_upward
2
arrow_downward

слишком длинный текст для публикации в качестве комментария

не удалось получить доступ к базе данных... в текущем контексте безопасности.

Это означает, что бизнес сертификатов настроен неправильно (я не виню вас, это чертовски сложно сделать с первой попытки). Я бы посмотрел на эти:

  • убедитесь, что «dbo» является допустимым пользователем. Попробуйте запустить EXECUTE AS USER='dbo'; на обеих задействованных БД. В случае сбоя (это означает, что БД была создана с помощью Windows SID, который больше не действителен), запустите ALTER AUTHORIZATION ON DATABASE:<dbname> TO sa.

  • убедитесь, что вы не изменили процедуру после того, как подписали ее. Любой ALTER молча удалит подпись и сделает вашу цепочку доверия недействительной.

добавить ВЫПОЛНИТЬ КАК ВЛАДЕЛЕЦ

Процедура должна иметь предложение EXECUTE AS, но не имеет большого значения, что это такое, если оно есть. Вы предоставляете разрешение SELECT для сертификата (следовательно, для подписи), поэтому оно должно работать независимо от того, кого процедура олицетворяет.

Для отладки я рекомендую вам сделать это, просто запустив хранимую процедуру вручную из окна запроса SSMS:

 USE myactivateddb;
 GO

 EXECUTE AS USER='dbo'; -- does not matter who, is important to be AS USER
 GO

 EXEC myotherdb.dbo.myotherproc ...;
 GO

 REVERT;
 GO

Это легче отладить, чем пытаться сделать это из активированной процедуры. Если это сработает, попробуйте то же самое, но вместо выполнения другой процедуры, хранимой в БД, выполните активированную процедуру из окна запроса SSMS (без запуска при активации в очереди).

person Remus Rusanu    schedule 07.01.2015
comment
Ах! Хорошо, я вижу по крайней мере одну проблему, ALTER после подписания. (Разумно, не думал об этом). Я попробую сегодня. ОГРОМНОЕ СПАСИБО! - person mbourgon; 07.01.2015
comment
Похоже, это все - изначально у нас не было EXECUTE AS, а затем, когда мы ИЗМЕНИЛИ, чтобы добавить его, он лишился подписи. Спасибо! - person mbourgon; 08.01.2015

arrow_upward
0
arrow_downward

Похоже на блокировку метаданных. Если вы выполняете какой-либо DDL в своей процедуре активации, например, создаете индекс или манипулируете столбцами, вы можете ожидать тайм-ауты блокировки, если несколько экземпляров процедуры активации находятся в обслуживании. Помните, что процедура активации обычно представляет собой длительную транзакцию, и если она блокирует какой-либо объект, она сохраняет его заблокированным до конца транзакции.

person Endrju    schedule 07.01.2015