Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3      [все]
 Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
Пытаюсь по шагам воспроизвести пример из MSDN по взаимодействию компонентов service broker на разных серверах:
https://technet.microsoft.com/ru-ru/library/bb839483(v=sql.105).aspx

База [InstTargetDB] расположена на экземпляре с установленным 2014SP2CU8, [InstInitiatorDB] - на экземпляре 2016SP1CU7 (обе версии - стандарт)

После воспроизведения всех примеров без ошибок, на шаге Занятие 6. Получение ответа и завершение диалога https://technet.microsoft.com/ru-ru/library/bb839494(v=sql.105).aspx - получаю:
(0 rows affected)
Msg 8418, Level 16, State 1, Line 16
The conversation handle is missing. Specify a conversation handle.

(1 row affected)

Сервера - в одном домене (физически две разные виртуалки на одном хосте).

Подскажите, как вообще искать неисправности в этом случае? Где у этого "розетка", в которую нужно заглянуть в первую очередь?
5 фев 18, 11:10    [21166041]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
uaggster,

если получили такую ошибку, то у вас оператор receive вернул null ссылку на диалог.

там пример написан без проверки: что есть плохо
WAITFOR
( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
  FROM InstInitiatorQueue
), TIMEOUT 1000;


вы ждете секунду получение сообщения из очереди и потом пытаетесь закрыть диалог, но может получиться так что сообщений в очереди не будет, receive вернет null в @RecvReplyDlgHandle и получите данную ошибку


пишите так:
WAITFOR
( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body
  FROM InstInitiatorQueue
), TIMEOUT 1000;
if @@ROWCOUNT = 1
    end conversation @RecvReplyDlgHandle
else begin
     throw 50000, 'Ошибка! в очереди нет сообщений для обработки', 1
     --какие то действия при данной ситуации
end


в первую очередь смотрите статусы в sys.conversation_endpoints, если там ничего подозрительного смотрите наличие сообщений в очередях получателя/отправителя, если и там нет признаков происков макаронного монстра идите в sys.transmission_queue
5 фев 18, 12:49    [21166528]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
felix_ff
в первую очередь смотрите статусы в sys.conversation_endpoints, если там ничего подозрительного смотрите наличие сообщений в очередях получателя/отправителя, если и там нет признаков происков макаронного монстра идите в sys.transmission_queue

Подозрительное, думаю, есть (см).
Вот только что с этим делать?

К сообщению приложен файл. Размер - 39Kb
16 фев 18, 11:39    [21196285]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
Вот так, думаю, будет нагляднее:

К сообщению приложен файл. Размер - 124Kb
16 фев 18, 11:45    [21196318]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826


К сообщению приложен файл. Размер - 83Kb
16 фев 18, 11:45    [21196319]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
uaggster,

uaggster,

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

судя по вашим данным у вас два открытых исходящих диалога, но если оно с состоянии conversing, то это значит что у вас отработало открытие диалога со стороны инициатора и отправка сообщения в принимающую сторону, но до сих пор целевая служба ничего не ответила в ответ. вот оно у вас и висит в ожидании.

это если "беглым" вгзлядом.
16 фев 18, 12:20    [21196435]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
в sys.transmission_queue у вас что сейчас?
16 фев 18, 12:22    [21196440]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
felix_ff
в sys.transmission_queue у вас что сейчас?

А вот то, что на втором скрине.
Я транспонировал потому что иначе картинка очень мелкая.

На целевом сервере - в очередях пусто.

Т.е., сообщение туда, видимо, не доставлено.
Но по какой причине? Где затык то?
16 фев 18, 12:54    [21196563]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
uaggster,

а так второй скрин это содержимое transmission_queue? ну так прочитайте что там в message_body

select coalesce(try_convert(xml, [message_body]), try_convert(varchar(max), [message_body]), '') from sys.transmission_queue
16 фев 18, 14:22    [21196943]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
felix_ff
uaggster,

а так второй скрин это содержимое transmission_queue? ну так прочитайте что там в message_body

select coalesce(try_convert(xml, [message_body]), try_convert(varchar(max), [message_body]), '') from sys.transmission_queue

Ээээ... там
<test>test</test>
<test>test</test>
Ровно то, что я пытаюсь передать на второй сервер.
Но на второй сервер - оно не передается!
16 фев 18, 14:31    [21196978]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
uaggster,

проверяйте включен ли брокер в базе msdb на сервере источнике, включен ли брокер на базе сервера получателя, не отключена ли очередь целевой службы
16 фев 18, 14:38    [21197013]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
а еще вам необходимо проверить маршруты, и привязки удаленных служб
16 фев 18, 14:40    [21197021]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
а и самое главное: а конечная точка то включена вообще? :D

покажите результат
select * from sys.routes
select * from sys.remote_service_bindings
select * from sys.service_broker_endpoints
16 фев 18, 14:44    [21197048]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
felix_ff
uaggster,

проверяйте включен ли брокер в базе msdb на сервере источнике

Да, включен

felix_ff
, включен ли брокер на базе сервера получателя,

Да, включен

felix_ff
не отключена ли очередь целевой службы

А как это проверить?


Я пытаюсь воспроизвести пример:
+

На первом сервере:
Create database databaseA
Go
Alter database databaseA set Enable_Broker
Go

Use databaseA
GO
Create Message Type SenderMessageType validation=NONE 
GO
Create Message Type ReceiverMessageType validation=NONE
GO

Create Contract SampleContract
(
  SenderMessageType  SENT BY INITIATOR,
  ReceiverMessageType   SENT BY TARGET
)

Create Queue InitiatorQueue
 WITH status = ON
 
 Create Service SenderService ON QUEUE InitiatorQueue  (SampleContract) 

 Create Route RouteA
WITH
  SERVICE_NAME = 'ReceiverService',
  BROKER_INSTANCE = '7CCCCBB4-3F03-44F6-B94D-AC6282B4DFAD',
  ADDRESS = 'TCP://10.30.10.53:4022'
GO

select service_broker_guid
 from sys.databases
 where name = 'DatabaseA'

Use master
Go
--1. Create a master key for master database.
Create Master Key Encryption BY Password = '1234qwer@'
--Go
/*2.Create certificate and End Point that support 
     certificate based authentication 
*/
Create Certificate EndPointCertificateA
WITH Subject = 'A.Server.Local',
    START_DATE = '01/01/2017',
    EXPIRY_DATE = '01/01/2020'
ACTIVE FOR BEGIN_DIALOG = ON;
GO

CREATE ENDPOINT ServiceBrokerEndPoint
   STATE=STARTED
   AS TCP (LISTENER_PORT = 4022)
   FOR SERVICE_BROKER 
   (
     AUTHENTICATION = CERTIFICATE EndPointCertificateA,
     ENCRYPTION = SUPPORTED
   );

BACKUP CERTIFICATE EndPointCertificateA 
 TO FILE = 
   'C:\backup\EndPointCertificateA.cer';
GO

Create Certificate EndPointCertificateB
 From FILE = 
 'C:\Backup\EndPointCertificateB.cer';
GO


CREATE LOGIN sbLogin
 FROM CERTIFICATE EndPointCertificateB;
GO

GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO

Use DatabaseA
GO
Create Master Key Encryption BY
Password = '1234qwer@'
Go

Create Certificate UserCertificateA
 WITH Subject = 'A.Server.Local',
    START_DATE = '01/01/2018',
    EXPIRY_DATE = '01/01/2020'
ACTIVE FOR BEGIN_DIALOG = ON;
GO

BACKUP CERTIFICATE UserCertificateA TO FILE=
'C:\backup\UserCertificateA.cer';
GO

Create User UserB WITHOUT LOGIN
GO


CREATE CERTIFICATE UserCertificateB
 AUTHORIZATION UserB
 FROM FILE = 'C:\backup\UserCertificateB.cer';
GO

GRANT CONNECT TO UserB;

GRANT SEND ON SERVICE::SenderService To UserB;
GO

CREATE REMOTE SERVICE BINDING ServiceBindingB
 TO SERVICE 'ReceiverService'
 WITH USER = UserB


 /**********Begin a Dialog and Send a Message******************/
Declare @ConversationHandle uniqueidentifier

Begin Transaction
Begin Dialog @ConversationHandle
 From Service SenderService
 To Service 'ReceiverService'
 On Contract SampleContract
 WITH Encryption=off;
SEND 
      ON CONVERSATION @ConversationHandle
      Message Type SenderMessageType
  ('<test>test</test>')
Commit


На втором сервере:
Create database databaseB
Go
Alter database databaseB set Enable_Broker
Go

Use databaseB
GO
Create Message Type SenderMessageType validation=NONE
Create Message Type ReceiverMessageType validation=NONE

Create Contract SampleContract
(
  SenderMessageType  SENT BY INITIATOR,
  ReceiverMessageType   SENT BY TARGET
)

Create Queue TargetQueue WITH status= ON

Create Service ReceiverService ON QUEUE TargetQueue (SampleContract)

Create Route RouteB
WITH
  SERVICE_NAME = 'SenderService',
  BROKER_INSTANCE='886B2333-B33F-4A07-A16C-82DD193CAD57',
 ADDRESS = 'TCP://10.30.10.205:4022'
GO

Use master
Go
 
--1. Create a master key for master database.
Create Master Key Encryption BY Password = '1234qwer@';
Go
--2.Create certificate and End Point that support certificate based authentication.
Create Certificate EndPointCertificateB
WITH Subject = 'B.Server.Local',
       START_DATE = '01/01/2018',
       EXPIRY_DATE = '01/01/2020'
ACTIVE FOR BEGIN_DIALOG = ON;
GO
CREATE ENDPOINT ServiceBrokerEndPoint
      STATE=STARTED
      AS TCP (LISTENER_PORT = 4022)
      FOR SERVICE_BROKER
      ( 
         AUTHENTICATION = CERTIFICATE EndPointCertificateB,
         ENCRYPTION = SUPPORTED
      );

BACKUP CERTIFICATE EndPointCertificateB TO FILE=
  'C:\Backup\EndPointCertificateB.cer';
GO

Create Certificate EndPointCertificateA
 From FILE = 
 'C:\backup\EndPointCertificateA.cer';
GO

CREATE LOGIN sbLogin
 FROM CERTIFICATE EndPointCertificateA;
GO

GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO


Use DatabaseB
GO
Create Master Key Encryption BY
Password = '1234qwer@';
Go

Create Certificate UserCertificateB
 WITH Subject = 'B.Server.Local',
    START_DATE = '01/01/2018',
    EXPIRY_DATE = '01/01/2020'
ACTIVE FOR BEGIN_DIALOG = ON;
GO

BACKUP CERTIFICATE UserCertificateB TO
FILE='C:\backup\UserCertificateB.cer';
GO

Create User UserA WITHOUT LOGIN
GO

CREATE CERTIFICATE UserCertificateA
 AUTHORIZATION UserA
FROM FILE = 'C:\backup\UserCertificateA.cer';
GO

GRANT CONNECT TO UserA;

GRANT SEND ON SERVICE::ReceiverService To UserA;
GO

CREATE REMOTE SERVICE BINDING ServiceBindingA
 TO SERVICE 'SenderService'
 WITH USER = UserA

 select cast(message_body as xml)  from TargetQueue

 /*****Receive the Message and send a message to the ender**********/

Declare @ConversationHandle as uniqueidentifier
Declare @MessageBody as nvarchar(max)
Declare @MessageType as sysname

Begin Transaction
Print 'Started Receiving ';

RECEIVE top (1)
      @MessageType = message_type_name,
      @ConversationHandle = conversation_handle,
    @MessageBody = message_body
FROM TargetQueue;

if @MessageType = 'SenderMessageType'
      Begin
            SEND 
                  ON CONVERSATION @ConversationHandle
                  Message Type ReceiverMessageType
                  ('Message is received')
            END Conversation @ConversationHandle
      END

Commit


Это уже второй пример.
Отсюда: http://www.sqlservercentral.com/articles/Service Broker/2797/
Аналогичный из учебника MSDN - тоже не работает.
Пример с пересылкой сообщений между базами на одном сервере - воспроизводится без проблем.

Как обнаружить проблему? Где у него логгируется сообщения об ошибках?
16 фев 18, 14:49    [21197075]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
На сервере А, databaseA:

К сообщению приложен файл. Размер - 98Kb
16 фев 18, 14:53    [21197096]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
На сервере B, databaseB:

К сообщению приложен файл. Размер - 89Kb
16 фев 18, 14:54    [21197100]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 888
не понятно , есть результат:
select *
from sys.transmission_queue (nolock)


Там будет статус в поле transmission_status

Второе логи sql смотрели, есть ли там ошибки , права на коннект к endpoint-ам дали?
16 фев 18, 15:13    [21197196]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
Вот что в статусе:
An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.
16 фев 18, 15:32    [21197269]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
Из ошибок в логе только: Implied authentication manager initialization failed. Implied authentication will be disabled.
16 фев 18, 15:39    [21197302]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
Slava_Nik
Второе логи sql смотрели, есть ли там ошибки , права на коннект к endpoint-ам дали?

Вроде бы да, в скрипте (см. выше) есть вот что, например:
GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO
16 фев 18, 15:41    [21197305]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 888
uaggster,
у вас ошибка из-за прав.
на второй вопрос не ответили, права к endpoint-у дали?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e8346a87-4225-4468-a143-66682f58973c/an-error-occurred-while-receiving-data-10054an-existing-connection-was-forcibly-closed-by-the?forum=sqlservicebroker
16 фев 18, 15:42    [21197307]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
uaggster,

безопасность транспорта значит кривая,

почему у вас в одном случае шифрование RC4 а для другой точки AES?
16 фев 18, 15:44    [21197322]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
felix_ff
uaggster,

безопасность транспорта значит кривая,

почему у вас в одном случае шифрование RC4 а для другой точки AES?

Не знаю. Видимо, что-то где то по умолчанию.
Инициирующий сервер:
Microsoft SQL Server 2014 (SP2-CU8) (KB4037356) - 12.0.5557.0 (X64) Oct 3 2017 14:56:10 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Целевой сервер:
Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64)
Nov 8 2017 17:32:23
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

А как поправить различие в шифровании?

Кстати, интересно, а сейчас в статусе сообщений - ПУСТО!
16 фев 18, 15:53    [21197356]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
Slava_Nik
uaggster,
у вас ошибка из-за прав.
на второй вопрос не ответили, права к endpoint-у дали?

Да говорю ж, вроде как дал:

GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint To sbLogin
GO

И на том, и не другом серверах.
16 фев 18, 15:55    [21197366]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
uaggster,

ну для начала сделайте

на сервере целевой службы

alter endpoint [ServiceBrokerEndPoint]
state = STOPPED;

alter endpoint [ServiceBrokerEndPoint]
FOR SERVICE_BROKER (  
    AUTHENTICATION = CERTIFICATE EndPointCertificateA
   , ENCRYPTION = SUPPORTED, ALGIRITHM RC4;

alter endpoint [ServiceBrokerEndPoint]
state = STARTED;
16 фев 18, 16:13    [21197434]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
felix_ff, Сделал, только AES, на обоих серверах.
Пока результат 0
16 фев 18, 16:27    [21197486]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
И в transmission_status - пусто
16 фев 18, 16:29    [21197500]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
uaggster,

у меня нет сейчас под рукой инстансов на которых можно СБ поднять, вечером скину вам алгоритм если сами не разберетесь.
у вас явно какие то проблемы с настройками безопасности.
16 фев 18, 16:35    [21197539]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
Хорошо, спасибо!
Буду ждать.

Сам не разберусь.
16 фев 18, 16:40    [21197559]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
uaggster
Member

Откуда:
Сообщений: 826
Можно даже пример пошаговой настройки компонентов, чтобы понять, где ломается.
Проблема в том, что я пошагово воспроизвожу MSDN-овский учебник, и тем не менее не могу получить результат.
16 фев 18, 16:44    [21197583]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
Владислав Колосов
Member

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

сервер может принудительно закрывать соединение с той стороны если, например, нет каких-то разрешений на подключение или он получает сообщение, не воспринимаемое как handshake.
16 фев 18, 17:55    [21197828]     Ответить | Цитировать Сообщить модератору
 Re: Как обнаружить ошибку при работе service broker?  [new]
Ennor Tiegael
Member

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

Посмотрел свой старый скрипт инициализации брокера, еще для 2005 версии (но вроде особо ничего не поменялось в транспорте с тех пор). Отличия:

  • Grant connect on endpoint надо давать не только соотв. логину, но и public, зачем-то. Хз, может сейчас уже не надо, но проверьте;
  • Помимо grant send on service, удаленному логину надо давать также и control на этот сервис.

    Все это - на обеих сторонах, естественно.
  • 16 фев 18, 18:15    [21197860]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    uaggster
    Member

    Откуда:
    Сообщений: 826
    Ennor Tiegael
    uaggster,

    Посмотрел свой старый скрипт инициализации брокера, еще для 2005 версии (но вроде особо ничего не поменялось в транспорте с тех пор). Отличия:

  • Grant connect on endpoint надо давать не только соотв. логину, но и public, зачем-то. Хз, может сейчас уже не надо, но проверьте;
  • Помимо grant send on service, удаленному логину надо давать также и control на этот сервис.

    Все это - на обеих сторонах, естественно.

  • Хорошо, проверю в понедельник, но звучит несколько странно.
    17 фев 18, 16:50    [21198959]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    uaggster,

    ох, получается много :) но скоро будет готово
    17 фев 18, 19:57    [21199210]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Добрый вечер,

    Чтож как и обещал делюсь знаниями по настройке SB.
    Я решил представить эти сведения в виде мини статьи по потому что тема довольно интересная, я уже не первый раз сталкиваюсь с людьми у которых возникают проблемы при настройке Service Broker, да и мне самому не помешает поднять в памяти некоторые нюансы на которые наталкивался когда разбирался с реализацией компонента.

    При этом стоит отметить что настройка коммуникации служб в пределах одной базы и даже одно экземпляра не представляет ничего сложного.
    Самое непотребство возникает когда дело касается обмена сообщениями между службами нескольких инстансов Sql Server.
    Замечу только что даже при отправке сообщений в одном экземпляре между различными базами необходимо установить флаг TRUSTWORTHY для обеих баз (так было во времена 2008 сиквела возможно данное требование осталось и сейчас, чесно - не тестил).

    Компонент обеспечивает безопасность на двух уровнях - транспортном и на уровне диалога.
    Транспортная безопасность дает защиту самого соединения на уровне стека TCP/IP между серверами, при этом транспортную безопасность следует тоже делить на два шага: аутентификацию и шифрование.(причем шифрование опционально - может быть а может не быть, а вот аутентификация обязательна)
    Безопасность на уровне диалога обеспечивает соотвественно защиту самого диалога между конечными точками.

    Пока поговорим о транспортной аутентификации:
    Service Broker имеет опять таки же два типа аутентификации - Windows-аутентификация и аутентификация с использованием сертификатов, собственно с последними мы сегодня и будем играться.

    Отмечу касательно windows-аутентификации, для ее настройки необходимо что бы служба Sql Server запускалась под определенными доменными учетными данными (или в сети поддерживался протокол аутентификации Kerberos), каждая доменная учетка будет отмаплена логином в зеркальный инстанс и ей будет дано право на CONNECT к конечной точке.
    То есть к примеру имеея два инстанса и домен я создаю две учетки: mydomain\sql1 и mydomain\sql2
    Делаю так что бы один экземпляр Sql Server запускался под учеткой mydomain\sql1, а второй экземляр запускался под учеткой mydomain\sql2
    Создаю логин mydomain\sql1 во втором экземпляре, а логин mydomain\sql1 создаю в первом экземляре;

    --на сервере №1
    CREATE ENDPOINT [endpoint_server1] STATE = STARTED AS TCP (LISTENER_PORT=4022) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS, ENCRYPTION = REQUIRED);
    CREATE LOGIN [mydomain\sql2] FROM WINDOWS;
    GRANT CONNECT ON ENDPOINT::[endpoint_server1] TO [mydomain\sql2];
    --на сервере №2
    CREATE ENDPOINT [endpoint_server2] STATE = STARTED AS TCP (LISTENER_PORT=4022) FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS, ENCRYPTION = REQUIRED);
    CREATE LOGIN [mydomain\sql1] FROM WINDOWS;
    GRANT CONNECT ON ENDPOINT::[endpoint_server2] TO [mydomain\sql1];
    


    Еще один хитрый нюанс: может случиться так что служба Sql Server запущена под служебной учетной записью LocalSystem, в таком случае логин создается для сервера а не для доменной учетки и будет представлять из себя вид [mydomain\sqlserver1$]
    т.е. вместо пользовательской учетки мы создаем логин для сервера:
    --на сервере №1
    CREATE LOGIN [mydomain\sqlserver2$] FROM WINDOWS;
    --на сервере №2
    CREATE LOGIN [mydomain\sqlserver1$] FROM WINDOWS;
    

    И эта фишка будет работать ТОЛЬКО в том случае если в сети используется протокол Kerberos для аутентификации, если его нет, то можно сразу рассматривать аутентификацию с использованием сертификатов.

    Собственно я вкратце описал сейчас алгоритм windows аутентификации, он сильно проще чем предстоящий геморрой с сертификатами но требует наличие домена (коего у меня сейчас под рукой нет :-) и протокола Kerberos если службы запущены не под доменными учетками а от системных учетных записей, кстати да учетка NETWORK SERVICE так же как и LocalSystem должна обеспечивать аутентификацию по сети, в то время как LOCAL SERVICE ничего подобного не допустит.


    Наконец перейдем к моим нелюбимым сертификатам и вооружившись всеми необходимыми средствами можно приступать.
    Входной набор или "что имеем":

    Инстанс Sql Server 2014 LocalDb развернутый на моей основной домашней машине:
    Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) Jun 17 2016 19:14:09 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    и полноценный инстанс Sql Server 2016 Developer Edition на моем ноутбуке:
    Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 15063: )


    К сообщению приложен файл. Размер - 128Kb
    18 фев 18, 21:31    [21200302]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364


    К сообщению приложен файл. Размер - 104Kb
    18 фев 18, 21:31    [21200303]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Удостоверимся что обе машины видят друг друга по сети. По ICMP компы друг друга видят и это хорошо, бывает что ping не проходит так как ICMP-протокол блокирован брандмауэром с какой то стороны и.т.д нас это не сильно сейчас волнует поскольку далее мы все равно убедимся что между конечными точками связь должна быть.

    К сообщению приложен файл. Размер - 103Kb
    18 фев 18, 21:32    [21200305]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Переходим непосредственно к настройке сначала работаем на основной машине (когда я буду выполнять какие то действия на ноуте буду отдельно писать)
    Поскольку у нас предполагается обмен данными между экземплярами нам необходимо обеспечить безопасность транспорта, что бы сообщения шифровались на выходе и Sql Server делает это всегда если какие то данные выходят за пределы экземпляра.

    Предварительно я перегенерирую основной ключ службы поскольку раньше не бэкапил его, а по хорошему эту процедуру следует сделать сразу же после установки инстанса и уверенности что потом не будет "фиаско"
    USE master;
    GO
    
    ALTER SERVICE MASTER KEY REGENERATE;
    GO
    DECLARE @backupFile NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceDefaultDataPath')) + REPLACE(@@SERVERNAME, '\', '_') + '.key';
    EXEC (N'BACKUP SERVICE MASTER KEY TO FILE = ''' + @backupFile + N'''ENCRYPTION BY PASSWORD = ''123456'';');
    GO
    



    Далее нам необходимо защитить основной ключ базы данных master ключом службы, создать сертификат для нашего сервера и забэкапить его в файл.
    USE master;
    GO
    
    IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456';
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [is_master_key_encrypted_by_server] = 1) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE [name] = 'main_cert') CREATE CERTIFICATE [main_cert] WITH SUBJECT = 'Main machine certificate';
    GO
    
    DECLARE @backupFile NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceDefaultDataPath')) + 'main_cert.cer';
    EXEC (N'BACKUP CERTIFICATE [main_cert] TO FILE = ''' + @backupFile + ''';');
    GO
    

    Теперь создаем конечную точку на нашем основном компе:

    CREATE ENDPOINT [main_endpoint] STATE = STARTED AS TCP (LISTENER_PORT=4022) FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE [main_cert], ENCRYPTION = REQUIRED ALGORITHM AES);
    


    И собственно запустив netstat -anb можем убедиться что наш инстанс теперь прослушивает порт 4022 протокола TCP :)

    Теперь проделываем аналогичные настройки на ноутбуке:
    USE master;
    GO
    
    ALTER SERVICE MASTER KEY REGENERATE;
    GO
    DECLARE @backupFile NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceDefaultDataPath')) + REPLACE(@@SERVERNAME, '\', '_') + '.key';
    EXEC (N'BACKUP SERVICE MASTER KEY TO FILE = ''' + @backupFile + N'''ENCRYPTION BY PASSWORD = ''123456'';');
    GO
    
    USE master;
    GO
    
    IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456';
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [is_master_key_encrypted_by_server] = 1) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE [name] = 'note_cert') CREATE CERTIFICATE [note_cert] WITH SUBJECT = 'Notebook certificate';
    GO
    
    DECLARE @backupFile NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceDefaultDataPath')) + 'note_cert.cer';
    EXEC (N'BACKUP CERTIFICATE [note_cert] TO FILE = ''' + @backupFile + ''';');
    GO
    
    CREATE ENDPOINT [note_endpoint] STATE = STARTED AS TCP (LISTENER_PORT=4022) FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE [note_cert], ENCRYPTION = REQUIRED ALGORITHM AES);
    

    SELECT * FROM sys.service_broker_endpoints; --представление показывает что у нас настроены конечные точки слушающие порт 4022 (может быть любым другим, но такой используется по умолчанию) для входящих подключений компонента Service Broker
    


    К сообщению приложен файл. Размер - 142Kb
    18 фев 18, 21:39    [21200312]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Далее давайте убедимся что оба инстанса могут видеть конечные точки друг друга:

    К сообщению приложен файл. Размер - 53Kb
    18 фев 18, 21:42    [21200316]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Отлично, нами были созданы сертификаты на обоих серверах и проверено что с каждого сервера прослушиваемый TCP-порт конечной точки другого сервера виден.
    Теперь необходимо обменяться сертификатами между серверами что бы они могли узнавать друг друга, перекидываем файлик C:\Users\felix_ff\main_cert.cer с основной машины на ноут, а с ноута файлик C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\note_cert.cer на основной комп

    Итого на основной машине выполняю:
    CREATE LOGIN [note_user] with password = '123456';
    CREATE USER [note_user];
    CREATE CERTIFICATE [note_cert] AUTHORIZATION [note_user] FROM FILE = 'C:\Users\felix_ff\note_cert.cer';
    GRANT CONNECT ON ENDPOINT::main_endpoint TO [note_user];
    

    На ноуте выполняю:
    CREATE LOGIN [main_user] with password = '123456';
    CREATE USER [main_user];
    CREATE CERTIFICATE [main_cert] AUTHORIZATION [main_user] FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\main_cert.cer';
    GRANT CONNECT ON ENDPOINT::note_endpoint TO [main_user];
    

    Тем самым я создаю в базе master логин, пользователя под которым удаленный сервер будет стучаться в гости и сертификат с помощью которого данный пользователь будет проходить проверку подлинности.
    На этом настройка аутентификации с помощью сертификатов подходит к концу и слава богу если я ничего не забыл.

    К сообщению приложен файл. Размер - 121Kb
    18 фев 18, 21:43    [21200321]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Далее затронем аспект шифрования: конфигурация конечной точки в каждом экземпляре требует задания параметра ENCRYPTION оно может быть как выключено, так и включено.
    Самое интересное что параметр ECNRYPTION может быть передан в трех различных вариациях (DISABLED, SUPPORTED, REQUIRED).
    Как я уже говорил ранее по умолчанию шифрование требуется для всех соединений в случае если мы имеем ситуацию с отправкой сообщений к другому экземпляру, но конечные точки можно настроить с различными параметрами шифрования и вот в таком случае от комбинации настроек зависит будут ли зашифрованны данные.
    Чесно признаюсь я забыл сетку в каких случаях оно шифруется а в каких нет, на момент написания этих строк полез в книжку издательства wrox.com Sql server 2008 руководство администратора авторства Брайна Найта и КО.

    Так вот сетка представляет из себя вид:
    ENDPOINT A ENDPOINT B ENCRYPTED
    REQUIRED REQUIRED YES
    REQUIRED SUPPORTED YES
    SUPPORTED SUPPORTED YES
    DISABLED DISABLED NO
    DISABLED SUPPORTED NO


    Когда у нас готовы конечные точки нам необходимо создать базы данных в которых будут объекты компонента Service Broker, поехали:

    --основной комп:
    USE [master];
    GO
    IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = 'SB_test') DROP DATABASE [SB_test];
    GO
    CREATE DATABASE [SB_test];
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = 'SB_test' AND [is_broker_enabled] = 1) ALTER DATABASE [SB_test] SET ENABLE_BROKER;
    GO
    USE [SB_test];
    GO
    
    CREATE MESSAGE TYPE [//SB_test/MessageTypes/test_msg] VALIDATION = NONE;
    CREATE CONTRACT [//SB_test/Contracts/test_contract] (
      [//SB_test/MessageTypes/test_msg] SENT BY ANY
    );
    CREATE QUEUE [//SB_test/Queues/Inbound];
    CREATE QUEUE [//SB_test/Queues/Outbound];
    
    CREATE SERVICE [//SB_test/Services/main_service_inbound] ON QUEUE [//SB_test/Queues/Inbound] ([//SB_test/Contracts/test_contract]);
    CREATE SERVICE [//SB_test/Services/main_service_outbound] ON QUEUE [//SB_test/Queues/Outbound] ([//SB_test/Contracts/test_contract]);
    GO
    


    --ноут:
    USE [master];
    GO
    IF EXISTS (SELECT 1 FROM sys.databases WHERE [name] = 'SB_test') DROP DATABASE [SB_test];
    GO
    CREATE DATABASE [SB_test];
    GO
    IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE [name] = 'SB_test' AND [is_broker_enabled] = 1) ALTER DATABASE [SB_test] SET ENABLE_BROKER;
    GO
    USE [SB_test];
    GO
    
    CREATE MESSAGE TYPE [//SB_test/MessageTypes/test_msg] VALIDATION = NONE;
    CREATE CONTRACT [//SB_test/Contracts/test_contract] (
      [//SB_test/MessageTypes/test_msg] SENT BY ANY
    );
    CREATE QUEUE [//SB_test/Queues/Inbound];
    CREATE QUEUE [//SB_test/Queues/Outbound];
    
    CREATE SERVICE [//SB_test/Services/note_service_inbound] ON QUEUE [//SB_test/Queues/Inbound] ([//SB_test/Contracts/test_contract]);
    CREATE SERVICE [//SB_test/Services/note_service_outbound] ON QUEUE [//SB_test/Queues/Outbound] ([//SB_test/Contracts/test_contract]);
    GO
    


    Базы называются аналогично можно назвать по разному, кому как нравится.
    Сразу создам две процедурки в каждой базе для удобства тестирования, одна показывает некоторые моменты что бы понять что происходит сейчас в компоненте, вторая скидывает все диалоги у которых состояние отличается от 'CLOSED'
    IF OBJECT_ID('[dbo].[sb_clear]', N'P') IS NULL EXEC(N'create procedure [dbo].[sb_clear] as select 0;');
    GO
    ALTER PROCEDURE [dbo].[sb_clear]
    AS
    DECLARE @sql NVARCHAR(MAX) = N''
    SELECT @sql = @sql + REPLACE('END CONVERSATION "' + CONVERT(NVARCHAR(36), [conversation_handle]) + '" WITH CLEANUP', NCHAR(34), NCHAR(39)) + NCHAR(59) + NCHAR(13) + NCHAR(10) FROM sys.conversation_endpoints WHERE [state] <> 'CD';
    PRINT @sql;
    EXEC (@sql);
    GO
    
    IF OBJECT_ID('[dbo].[sb_get]', N'P') IS NULL EXEC(N'CREATE PROCEDURE [dbo].[sb_get] AS SELECT 0;');
    GO
    ALTER PROCEDURE [dbo].[sb_get]
    AS
    SELECT
         'sys.conversation_endpoints' AS [desc],
         [conversation_handle],
         [is_initiator],
         [state_desc],
         [far_service],
         [far_broker_instance],
         [outbound_session_key_identifier] AS [ok],
         [inbound_session_key_identifier] AS [ik]
    FROM sys.conversation_endpoints;
    
    SELECT
          'sys.transmission_queue' AS [desc],
          [conversation_handle],
          [to_service_name],
          [to_broker_instance],
          [from_service_name],
          [message_type_name],
          [transmission_status],
          CONVERT(VARCHAR(MAX), [message_body]) AS [msg],
          CONVERT(NVARCHAR(MAX), [message_body]) AS [msg2]
    FROM sys.transmission_queue;
    
    SELECT
          'sys.dm_broker_connections' AS [desc],
          [connection_id],
          [state_desc],
          [connect_time],
          [principal_name],
          [remote_user_name],
          [last_activity_time],
          [is_accept],
          [login_state_desc],
          [peer_certificate_id],
          [encryption_algorithm_desc],
          [total_sends],
          [total_receives]
    FROM sys.dm_broker_connections;
    
    SELECT
          'service_to_certs_relations' AS [desc],
          USER_NAME(s.[principal_id]) AS [service_owner],
          s.[name] AS [service_name],
          c.[name] AS [cert_name],
          USER_NAME(c.[principal_id]) AS [cert_owner],
          c.[certificate_id] AS [cert_id],
          c.[pvt_key_encryption_type_desc],
          c.[is_active_for_begin_dialog],
          IIF(c.[issuer_name] <> c.[subject], CONCAT('issuer:', c.[issuer_name], CHAR(32), 'subject:', c.[subject]), c.[subject]) AS [comment],
          c.[start_date],
          c.[expiry_date],
          c.[pvt_key_last_backup_date]
    FROM sys.services s
        JOIN sys.certificates c ON c.[principal_id] = s.[principal_id];
    
    SELECT
          'remote_service_bindings' AS [desc],
          USER_NAME([remote_principal_id]) AS [remote_login],
          *
    FROM sys.remote_service_bindings;
    GO
    
    18 фев 18, 21:49    [21200332]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Теперь необходимо настроить маршруты.
    Необходимо понимать КАК компонент определяет куда ему направить сообщение, а это определяется вот таким правилом:
    - при поступлении инструкции SEND ON CONVERSATION просматривается представление sys.routes в случае нахождения сообщения в данной таблице пересылка осуществляется правилами заданными маршрутом соотвествующим критериям отправки.
    - при поступлении сообщения в экземпляр просматривается sys.routes базы msdb что бы определить в какую базу отправить сообщение.
    - при отправлении подтверждения доставки сообщения также используется sys.routes, если маршрут возврата определен не будет это тоже может повлечь ошибки доставкиэ. может получиться так что если в sys.routes не будет обратного маршрута ответные сообщения будут складироваться в sys.transmission_queue
    - и опять такиже маршрут из базы msdb используется в случае получения подтверждения в экземпляр отправитель.

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

    Предварительно получив GUID экземпляров службы Service Broker для каждой базы каждого инcтанса, получаем инструкцией:
    USE [SB_test];
    GO
    SELECT [service_broker_guid] FROM sys.databases WHERE database_id = DB_ID(); --EC1080D9-AF1F-490A-97D8-47682754FB16
    GO
    


    Сейчас нам необходимо создать маршрут на основной машине в базе которую я буду использовать как инициатора сообщений:
    IF EXISTS (SELECT 1 FROM sys.routes WHERE [name] = 'route_to_note') DROP ROUTE [route_to_note];
    GO
    CREATE ROUTE [route_to_note] WITH SERVICE_NAME = '//SB_test/Services/note_service_inbound', ADDRESS = 'TCP://192.168.1.205:4022', BROKER_INSTANCE = '1FEEA52E-452B-459E-8DB7-66067445303E';
    
    А на ноуте нам нужен обратный маршрут что бы служба понимала куда ей отвечать:
    [src]
    IF EXISTS (SELECT 1 FROM sys.routes WHERE [name] = 'route_to_main') DROP ROUTE [route_to_main];
    GO
    CREATE ROUTE [route_to_main] WITH SERVICE_NAME = '//SB_test/Services/main_service_outbound', ADDRESS = 'TCP://192.168.1.20:4022', BROKER_INSTANCE = 'EC1080D9-AF1F-490A-97D8-47682754FB16';
    

    После настройки марштутов, подходим к теме обеспечения безопасности диалога, что пожалуй самый сложный вопрос во всем моем повествовании.
    Безопасность диалога обеспечивает безопасность между конечными точками, данные между конечными точками шифруются по умолчанию, отличительная черта безопасности диалога заключается в том что в процессе доставки сообщение может проходить несколько узлов в сети если к примеру настраивается MESSAGE FORWARDING.
    Я такой сценарий встречал один раз когда пересылка настраивалась не между двумя экземплярами Sql Server, а на пути следования был промежуточный экземпляр поскольку сервера находились в разных сегментах сети и технически было сложно организовать прямой доступ между двумя экземплярами.
    Так вот в случае если сообщение проходит несколько узлов, при достижении каждлой конечной точки оно шифруется/дешифруется в случае обеспечения транспортной безопасности, что накладывает дополнительные расходы. Если же будет использована безопасность диалога то сообщение будет зашифровано и расшифровано один раз у инициатора и получателя.

    Используется или не используется безопасность диалога настраивается опцией ENCRYPTION инструкции BEGIN DIALOG CONVERSATION;
    BEGIN DIALOG CONVERSATION @h FROM SERVICE [//svc_initiator] TO SERVICE '//svc_target' ON CONTRACT [contract] WITH ENCRYPTION = ON; --Используется
    BEGIN DIALOG CONVERSATION @h FROM SERVICE [//svc_initiator] TO SERVICE '//svc_target' ON CONTRACT [contract] WITH ENCRYPTION = OFF; --Не используется
    Следует отметить важный аспект: будет ли сообщение вообще не зашифронано в случае ENCRYPTION = OFF? Нет, параметр ENCRYPTION = OFF означает что шифрование не требуется, но в случае удаленной привязки диалог должен быть защищен даже с опцией в состоянии OFF. Это мы увидим далее.

    На основном компе и ноуте следует подготовить пользователя и его сертификат для дальнейшей настройки безопасности диалога но мы не будем его пока применять.
    --на основном компе:
    USE [SB_test];
    GO
    
    DECLARE @config TABLE ([name] sysname, [mininum] int, [maximum] int, [config_value] int, run_value int);
    INSERT INTO @config
      EXEC sp_configure;
    IF NOT EXISTS (SELECT 1 FROM @config WHERE [name] = 'xp_cmdshell' AND [run_value] = 1) EXEC (N'sp_configure "xp_cmdshell", 1; RECONFIGURE;');
    GO
    
    CREATE USER [main_service_owner] WITHOUT LOGIN; 
    ALTER AUTHORIZATION ON SERVICE::[//SB_test/Services/main_service_inbound] TO [main_service_owner];
    ALTER AUTHORIZATION ON SERVICE::[//SB_test/Services/main_service_outbound] TO [main_service_owner];
    GO
    
    IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##') BEGIN
        CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'main_service';
        ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
    END;
    GO
    
    IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERe [name] = 'main_service_owner_cert') CREATE CERTIFICATE [main_service_owner_cert] AUTHORIZATION [main_service_owner] WITH SUBJECT = 'Certificate for main service owner dialog security'
    GO
    
    DECLARE @backupFile NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceDefaultDataPath')) + 'main_service_owner_cert.cer';
    DECLARE @cmd NVARCHAR(512) = 'IF EXIST "' + @backupFile + '" (DEL /Q "' + @backupFile + '")';
    EXEC xp_cmdshell @cmd;
    EXEC (N'BACKUP CERTIFICATE [main_service_owner_cert] TO FILE = ''' + @backupFile + ''';');
    GO
    


    --на ноуте:
    USE [SB_test];
    GO
    
    DECLARE @config TABLE ([name] sysname, [mininum] int, [maximum] int, [config_value] int, run_value int);
    INSERT INTO @config
      EXEC sp_configure;
    IF NOT EXISTS (SELECT 1 FROM @config WHERE [name] = 'xp_cmdshell' AND [run_value] = 1) EXEC (N'sp_configure "xp_cmdshell", 1; RECONFIGURE;');
    GO
    
    CREATE USER [note_service_owner] WITHOUT LOGIN; 
    ALTER AUTHORIZATION ON SERVICE::[//SB_test/Services/note_service_inbound] TO [note_service_owner];
    ALTER AUTHORIZATION ON SERVICE::[//SB_test/Services/note_service_outbound] TO [note_service_owner];
    GO
    
    IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##') BEGIN
        CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'note_service';
        ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
    END;
    GO
    
    IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERe [name] = 'note_service_owner_cert') CREATE CERTIFICATE [note_service_owner_cert] AUTHORIZATION [note_service_owner] WITH SUBJECT = 'Certificate for notebook service owner dialog security';
    GO
    
    DECLARE @backupFile NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceDefaultDataPath')) + 'note_service_owner_cert.cer';
    DECLARE @cmd NVARCHAR(512) = 'IF EXIST "' + @backupFile + '" (DEL /Q "' + @backupFile + '")';
    EXEC xp_cmdshell @cmd;
    EXEC (N'BACKUP CERTIFICATE [note_service_owner_cert] TO FILE = ''' + @backupFile + ''';');
    GO
    
    18 фев 18, 21:52    [21200338]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Теперь сразу покажу типичную ошибку которая может быть допущена на этой стадии:
    В текущий момент инфраструктура для обеспечения безопасности диалога у нас почти готова, но давайте посмотрим что же будет если я в текущий момент попытаюсь отправить сообщение в принимающую очередь на ноутбук?
    Сначала проверим что будет если попытаться послать сообщение в принимающей службе на ноутбуке с опцией ENCRYPTION OFF инструкции BEGIN DIALOG CONVERSATION
    USE [SB_test]
    DECLARE @conversation_handle UNIQUEIDENTIFIER;
    BEGIN DIALOG CONVERSATION @conversation_handle
    FROM SERVICE [//SB_test/Services/main_service_outbound] TO SERVICE '//SB_test/Services/note_service_inbound'
    ON CONTRACT [//SB_test/Contracts/test_contract]
    WITH ENCRYPTION = OFF; 
    
    SEND ON CONVERSATION @conversation_handle MESSAGE TYPE [//SB_test/MessageTypes/test_msg] ('test message without message security');
    GO
    WAITFOR DELAY '00:00:05.000'; --задержка сделана специально, потому что если сразу запросить статусы в sys.conversation_endpoints может сложиться мнимое ошущение что ошибка в чем то другом потому что диалог будет в статусе CONVERSING
    EXEC [dbo].[sb_get];
    


    К сообщению приложен файл. Размер - 126Kb
    18 фев 18, 21:54    [21200345]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Как видно у нас вернулось сообщение что произошла ошибка открытия диалога, Transmission_status представления sys.transmission_queue содержит не слишком подробное описание, он только указывает что при отправке сообщения случилось некоторое событие которое не позволило доставить сообщение получателю, поэтому сообщение не будет отправлено и сообщение будет находиться в системной очереди пока диалог не будет закрыт.
    Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation.

    Более подробную информацию о том что же конкретно произошло мы получим в нашей очереди которая связана с сервисом инициатора, в данном случае [dbo].[//SB_test/Queues/Outbound]
    SELECT TRY_CONVERT(XML, [message_body]), * FROM [dbo].[//SB_test/Queues/Outbound] WITH(NOLOCK);
    

    И получаем причину ошибки:
    <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
      <Code>-8494</Code>
      <Description>You do not have permission to access the service '//SB_test/Services/note_service_inbound'.</Description>
    </Error>
    


    Как отлавливать ошибки, компонента я расскажу в самом конце потому что это не самое приятное занятие, требующее довольно продолжительное время в зависимости от типа возникающих ошибок.
    Сейчас же важно понимать что пользователь под которым мы проходим аутентификацию на удаленном сервере не смог получить доступа к конечной службе.
    Что же я забыл сделать на данном этапе что привело к такой ошибке? Конечно все дело в правах.

    Как видно из результата хранимой процедуры sb_get соединение между двумя серверами установлено, о чем свидетельствует строка представления sys.dm_broker_connections со статусом "LOGGED IN", следовательно само соединение между двумя экземплярами было установлено и ошибка не на транспортном уровне.
    Значит проблема на уровне прав пользователя обеспечивающего безопасность диалога.

    Забегая вперед скажу что сервер опевещает нас о данном инциденте событием Broker: Message Undeliverable на строне получателя с тектом:
    This message could not be delivered because the user with ID 0 in database ID 10 does not have permission to send to the service. Service name: '//SB_test/Services/note_service_inbound'.
    

    user ID 0 говорит нам о том что сервис инициатора диалога попытался авторизоваться в правами роли public, которая не имеет доступа к сервису очереди получателя.
    Текущую проблему бы решило разрешение прав SEND для роли public на сервис [//SB_test/Services/note_service_inbound] на ноутбуке инструкцией:
    grant send on service::[//SB_test/Services/note_service_inbound] to public
    


    Но мы же не хотим давать лишние права роли в которую входят все пользователи? Поэтому надо понять почему же при открытии диалога мы ломимся с правами роли вместо пользователя.

    И вот тут мы подходим к оперделению "удаленных привязок" (REMOTE BINDINGS)
    Для любой службы отправляющей сообщения за пределы инстанса мы можем указать привязки с какими учетными данными безопасности мы должны проходить проверки удостоверения безопасности на удаленных службах.
    Все дело в том что когда пересылка сообщений затрагивает не один инстанс отправляющая служба определяет какой сертификат использовать для обеспечения безопасности по привязке REMOTE BINDING.
    Это же описано в BOL:
    Service Broker uses a remote service binding to locate the certificate to use for a new conversation.
    The public key in the certificate associated with user_name is used to authenticate messages sent to the remote service and to encrypt a session key that is then used to encrypt the conversation.
    The certificate for user_name must correspond to the certificate for a user in the database that hosts the remote service.
    

    А так как я уже говорил что в случае пересылки сообщений между отдельными инстансами сообщение ДОЛЖНО быть защищено, нам необходимо определить сертификат которым мы будем подписывать наши отправляемые сообщения.

    Выполню инструкцию
    EXEC [dbo].[sb_clear];
    
    на обоих серверах что бы очистить проблемные диалоги.
    18 фев 18, 21:59    [21200349]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Чуть ранее я уже озаботился этим моментом, создал главный ключ базы, защитил его главным ключем службы, создал пользователя у которого есть право CONTROL на нашу отправляющуу службу [//SB_test/Services/main_service_outbound]
    Для этого пользователя мы создали сертификат и забекапили его в файл. Перекидываем сертификат пользователя-владельца службы получателя на основной комп, а сертификат владельца службы отправителя на ноут.

    на основном компе создаем пользователя для аутентификации службы ноута:
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = 'note_service_owner') CREATE USER [note_service_owner] WITHOUT LOGIN;
    IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE [name] = 'note_service_owner_cert') CREATE CERTIFICATE [note_service_owner_cert] AUTHORIZATION [note_service_owner] FROM FILE = 'C:\Users\felix_ff\note_service_owner_cert.cer';
    GRANT SEND ON SERVICE::[//SB_test/Services/main_service_outbound] TO [note_service_owner];
    GO
    

    А на ноутбуке проделываю зеркальную операцию для пользователя владельца-службы отправителя:
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [name] = 'main_service_owner') CREATE USER [main_service_owner] WITHOUT LOGIN;
    IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE [name] = 'main_service_owner_cert') CREATE CERTIFICATE [main_service_owner_cert] AUTHORIZATION [main_service_owner] FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\main_service_owner_cert.cer';
    GRANT SEND ON SERVICE::[//SB_test/Services/note_service_inbound] TO [main_service_owner];
    GO
    



    Теперь осталось создать привязку удаленной службы на основном компе инструкцией:
    CREATE REMOTE SERVICE BINDING [ToNote]
    AUTHORIZATION [dbo]
    TO SERVICE '//SB_test/Services/note_service_inbound'
    WITH USER = [note_service_owner]; --ANONYMOUS = ON
    

    Если раскоментить указание ANONYMOUS то действия осуществляемые на удаленном узле будет будут осуществленны из под контекста public роли (что в свою очередь обязывает нас раздавать дополнительные права, поэтому здесь мы это не используем)

    Удаленная привязка нужна только инициирующей службе, поэтому на ноутбуке нам не обязательно создавать ее, если мы не будем потом с ноутбука пытать первыми отправить сообщения на другую машину.

    Как только мы создали привязку давайте заново попробуем отправить сообщение и посмотреть что у нас получится, даже отправим парочку сообщений в различными параметрами опции ENCRYPTION
    USE [SB_test]
    DECLARE @conversation_handle UNIQUEIDENTIFIER;
    BEGIN DIALOG CONVERSATION @conversation_handle
    FROM SERVICE [//SB_test/Services/main_service_outbound] TO SERVICE '//SB_test/Services/note_service_inbound'
    ON CONTRACT [//SB_test/Contracts/test_contract]
    WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @conversation_handle MESSAGE TYPE [//SB_test/MessageTypes/test_msg] ('test message without message security');

    BEGIN DIALOG CONVERSATION @conversation_handle
    FROM SERVICE [//SB_test/Services/main_service_outbound] TO SERVICE '//SB_test/Services/note_service_inbound'
    ON CONTRACT [//SB_test/Contracts/test_contract]
    WITH ENCRYPTION = ON;

    SEND ON CONVERSATION @conversation_handle MESSAGE TYPE [//SB_test/MessageTypes/test_msg] ('test message with message security');
    GO
    WAITFOR DELAY '00:00:05.000'; --задержка сделана специально, потому что если сразу запросить статусы в sys.conversation_endpoints может сложиться мнимое ошущение что ошибка в чем то другом потому что диалог будет в статусе CONVERSING
    EXEC [dbo].[sb_get];

    К сообщению приложен файл. Размер - 126Kb
    18 фев 18, 22:03    [21200354]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    вот блин не тот скриншот в предыдущем посте приложил
    +
    (жалко что посты изменять нельзя)
    , вот правильный

    К сообщению приложен файл. Размер - 144Kb
    18 фев 18, 22:06    [21200359]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    И как только у нас есть привязка сейчас как видно все отлично: сообщния доставлены на удаленный инстанс, наши диалоги находятся в состоянии CONVERSING.

    Мы можем получить сообщение на ноутбуке, и потом отправить ответ после чего закроем диалог со стороны получателя.
    Замечу важный момент как видно с моего скрина поля ok и ik для диалога на стороне получателя заполнены, следовательно диалог использует сертификат для подписи сообщений даже если мы открывали диалог с параметром ENCRYPTION OFF как я говорил ранее.

    К сообщению приложен файл. Размер - 70Kb
    18 фев 18, 22:08    [21200363]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364


    К сообщению приложен файл. Размер - 79Kb
    18 фев 18, 22:09    [21200365]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Выполнив данную инструкцию:
    SELECT TRY_CONVERT(XML, [message_body]), * FROM [dbo].[//SB_test/Queues/Inbound] WITH(NOLOCK);
    DECLARE @h UNIQUEIDENTIFIER, @message_type_name SYSNAME, @msg VARCHAR(MAX);
    WHILE 1=1 BEGIN
         BEGIN TRANSACTION;
         WAITFOR (
    	        RECEIVE TOP(1) @h = [conversation_handle],
    			               @message_type_name = [message_type_name]
                FROM [dbo].[//SB_test/Queues/Inbound]
    	 ), TIMEOUT 1000
    	 IF @@ROWCOUNT <= 0 BEGIN
    	     ROLLBACK TRANSACTION;
    		 BREAK;
    	 END;
    	 IF @message_type_name NOT IN ('http://schemas.microsoft.com/SQL/ServiceBroker/Error', 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog', 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer') BEGIN
    	     SET @msg = 'answer on conversation ' + CONVERT(VARCHAR(36), @h);
    	     SEND ON CONVERSATION @h MESSAGE TYPE [//SB_test/MessageTypes/test_msg] (@msg);
    	 END;
    	 END CONVERSATION @h;
    	 COMMIT TRANSACTION;
    END;
    GO
    

    Я вычитал все входящие сообщения из очереди ноута и отправил ответные сообщения после чего закрыл диалоги.
    На стороне компа у нас теперь видно ответы:

    К сообщению приложен файл. Размер - 106Kb
    18 фев 18, 22:11    [21200368]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Теперь что касается отлова возможных ошибок возникающих в компоненте, всегда необходимо понимать на какой стадии возникла ошибка, была ли она на транспортном уровне или на уровне комуникации между службами.
    Sql Server имеет массив классов событий привязанных к компоненту Service Broker, это касается как SqlTrace так и XEvents событий.

    Для этого можно запустить трассировку классов событий Broker, Errors and Warnings и Audit: Broker обычно при возникновении ошибок они дают исчерпывающую информацию что может быть причиной не доставки сообщения компоненту. Вся сложность в том что трассу надо отслеживать не на одном инстансе а на нескольких, потому что может получится ситуация что один инстанс отправляет сообщения а другой инстанс не может их расшифровать но события показывающие данную ошибку будут только на втором инстансе, на первый же будет приходить только отлуп в виде довольно скудного сообщения.

    Так же можно использовать сеансы событий XEvent для отлова ошибок, они обычно начинаются с названий Broker => Broker Conversation, Broker_corrupted_message и.т.д.

    Что касается системных представлений: Если доставка осуществляется между экземплярами необходимо:
    а) посмотреть в представление sys.conversation_endpoints поле state может сразу сказать открылся ли диалог нормально, если state = ER то обычна причина ошибки открытия лежит в нашей отправляющей очереди сообщением
    б) если диалог в статусе CONVERSING но мы не видим сообщений в принимающей очереди необходимо посмотреть представление sys.dm_broker_connections оно скажет нам поднялся ли физический канал между конечными точками и нет ли проблем с аутентификацией между серверами.
    в) смотрим наличие сообщений в sys.transmission_queue и поле transmission_status, если оно пустое значит сообщение еще не было отправлено, необходимо включать трассировку ошибок на принимающей стороне, да и на отправляющей тоже.
    г) смотрим наличие сообщений в отправляющей очереди и вообще проверяем статусы обоих очередей в представлении sys.service_queues у обоих очередей должен быть выставлен бит is_receive_enabled, is_enqueue_enabled

    К сообщению приложен файл. Размер - 91Kb
    18 фев 18, 22:22    [21200384]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    На этом закругляюсь, сразу прощу прощения я чето не очень догнал как файлы картинок-вложений запихнуть в спойлеры.

    Сергей Гавриленко
    если можно Вас попросить подредактировать мои посты что бы картинки не убили у кого-нибудь траффик на телефоне.
    18 фев 18, 22:26    [21200390]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    uaggster
    Member

    Откуда:
    Сообщений: 826
    felix_ff, огромное спасибо.
    Добавил в закладки, буду пытаться скурить.

    На первый взгляд - самое дельное и подробное руководство из всех, что я видел.
    Спасибо!
    19 фев 18, 07:47    [21200661]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    ShIgor
    Member

    Откуда: Нижний Новгород
    Сообщений: 2333
    felix_ff,

    супер! жаль что я это прошел на год раньше через собственные спотыкания.
    чуть позже дополню как у меня происходит обмен сертификатами между серверами без ручного перекладывания файлов, да и вообще без файлов.
    13 дек 18, 11:41    [21762669]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    Андрей Попов
    Member

    Откуда:
    Сообщений: 5
    felix_ff, спасибо за ответы в теме, на сегодня вы мой бог )

    Сделал все как написано и настроил общение между удаленным сервером и сервером в офисе + настроил общение между БД внутри офиса. Начал по аналогии настраивать следующий удаленный сервер, а их около 10 и получаю ошибку "Connection attempt failed with error: '10060(Попытка установить соединение была безуспешной, т.к. от другого компьютера за требуемое время не получен нужный отклик, или было разорвано уже установленное соединение из-за неверного отклика уже подключенного компьютера.)'."

    Первое, подумал, что некорректно настроил. Настроил следующий, и такая же ошибка. Где-то есть подвох, которого не понимаю. Как найти ошибку?

    Спасибо
    14 окт 19, 09:40    [21993419]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    Андрей Попов
    Member

    Откуда:
    Сообщений: 5
    и теперь вообще все поломалось, в профайлере пишет

    An exception occurred while enqueueing a message in the target queue. Error: 33009, State: 2. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'ServiceBroker'. You should correct this situation by resetting the owner of database 'ServiceBroker' using the ALTER AUTHORIZATION statement.

    Идентификатор безопасности владельца базы данных, записанный в базе данных master, отличается от идентификатора безопасности владельца базы данных, записанного в базе данных "ServiceBroker". Устраните это различие, сбросив владельца базы данных "ServiceBroker" с помощью инструкции ALTER AUTHORIZATION.


    ServiceBroker - БД для обмена сообщениями
    14 окт 19, 10:17    [21993451]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7748
    Андрей Попов,

    Вы можете инициализировать брокер через ALTER DATABASE SET NEW_BROKER, но надо понимать, что вы удалите все переговоры conversation и маршруты, если такие были определены. Смотрите справку по этой команде.

    Утилита ssbdiagnose также в помощь.

    Вы восстанавливали резервную копию базы на другом сервере?
    14 окт 19, 12:45    [21993620]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    Андрей Попов
    Member

    Откуда:
    Сообщений: 5
    Владислав Колосов
    Вы восстанавливали резервную копию базы на другом сервере?

    Нет, настраиваю общение с нуля. Настроил на одном сервере - все ОК. Перешел к следующему и все поломал.
    15 окт 19, 06:59    [21994239]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    Андрей Попов
    Member

    Откуда:
    Сообщений: 5
    "Главный" сервер base1.
    Есть сервер srvshop40 и есть srvshop2.
    Первым был настроен srvshop40 и обмен между ним и base1 работает.
    Далее настраиваю по аналогии srvshop2.

    Думаю, что нашел ошибку, но не понял, как ее исправить:

    выполняю код отправки (порезанный, см. суть):

        BEGIN DIALOG CONVERSATION @conversation_handle
            FROM SERVICE [base1_ServiceOut]
            TO SERVICE 'srvshop2_ServiceIn','87F90E7A-EAED-4079-B5DD-E0711A8AB743'
            ON CONTRACT [ExtraCharge_contract]
            WITH ENCRYPTION = OFF;
    
        SEND ON CONVERSATION @conversation_handle MESSAGE TYPE [ExtraCharge_msg] (@XmlData);
    


    и поймал момент соединения (см. картинку).
    Почему то используется пользователь srvshop40_user, а не srvshop2_user.
    Где я неправ?

    К сообщению приложен файл. Размер - 23Kb
    16 окт 19, 03:33    [21995059]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7748
    Андрей Попов,

    вы ID брокера жестко задаете, в этом проблема.
    16 окт 19, 11:58    [21995322]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    ShIgor
    Member

    Откуда: Нижний Новгород
    Сообщений: 2333
    Владислав Колосов,

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

    Андрей Попов,
    у вас безопасность на основе сертификатов?
    16 окт 19, 13:16    [21995401]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    Андрей Попов
    Member

    Откуда:
    Сообщений: 5
    Владислав Колосов
    Андрей Попов,

    вы ID брокера жестко задаете, в этом проблема.

    Нет. В примере в этом трэде не было указания ID, и я изначально также сделал. Не работает. Потом добавил, безрезультатно.

    Я концептуально не понимаю связь между пользователем, которому разрешено подключение к endpoint и пользователем сервиса.
    На главном base1 сервере создана одна точка endpoint и два пользователя srvshop40_user и srvshop2_user с сертификатами из файлов соответствующих серверов. Юзерам дан доступ к точке.
    Отправка идет через сервисы и там уже два пользователя srvshop40_service_owner и srvshop2_service_owner.
    Есть маршруты и remote binding для xxx_service_owner.
    Как скуль должен понять, что при использовании сервиса для отправки для srvshop2 нужно использовать сертификат пользователя srvshop2_user?
    16 окт 19, 13:21    [21995405]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    ShIgor
    Member

    Откуда: Нижний Новгород
    Сообщений: 2333
    Андрей Попов,

    немного ушли в сторону.

    предположим, что у вас в центре уже есть 'сертификат центра' и endpoint с аутентификацией по 'сертификату центра'

    на периферийной точке
    создать 'сертификат точки'
    создать endpoint с аутентификацией по 'сертификату точки'
    создать логин (которым будет цеплятся центр, он един на всех точках)
    создать пользователя для логина
    создать сертификат для пользователя из 'сертификата центра'
    предоставить права на соединение с endpoint логину

    на центре
    создать 'логин точки' (для каждой точки свой)
    создать 'пользователя точки' для 'логина точки'
    создать 'сертификат для пользователя точки' из 'сертификата точки'
    предоставить права на соединение с endpoint 'логину точки'

    если все сделано так, то никаких сервис биндинг в этом случае не нужно.
    16 окт 19, 13:47    [21995439]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    ShIgor
    Member

    Откуда: Нижний Новгород
    Сообщений: 2333
    я обещал выложить процедуру как я создаю транспорт на основе сертификатов и обмениваюсь ими без файлов.
    выкладываю как есть, без всяких комментов и правок. это рабочий код, которым пользуюсь уже 2 года раз 10 в месяц.
    сбоев еще не было.

    create procedure [sbm].[usp_create_transport](@host nvarchar(256) = null, @ref_tt_str nvarchar(32) = null)
    as
    set nocount on
    
      declare @instance nvarchar(256) = '\sqlexpress'
      declare @db_name nvarchar(256) = 'db_sup_kkm_stage'
    
      if @host is null and @ref_tt_str is null return
    
      if @ref_tt_str is null and @host is not null
        select @ref_tt_str = ref_tt_str, @instance = isnull([ИнстансSQL], @instance), @db_name = isnull([ИмяБД], @db_name) from sbm.uv_dimTT where [СерверХост] = @host
      else if @ref_tt_str is not null and @host is null
    	select @host = [СерверХост], @instance = isnull([ИнстансSQL], @instance), @db_name = isnull([ИмяБД], @db_name) from sbm.uv_dimtt where ref_tt_str = @ref_tt_str
    
      declare @packedhostname nvarchar(256) 
      set @packedhostname = sbm.ufn_get_packedHostName(@host)
      
      declare @pLinkedServer nvarchar(256) = 'supkkm_' + @packedhostname;
      declare @pServerName nvarchar(256) = @host + @instance
      exec sbm.usp_createlinkedserver @pLinkedServer, @pServerName, N'master'
    
      declare @sql_str nvarchar(max)
      
      declare @target_cert varchar(max)
      set @sql_str = 'select @target_cert = tcert
        from openrowset(
          ''SQLOLEDB'', ''Server=SRV-MASTER-SBM;Database=master;Uid=xxxxxx;Pwd=xxxxxx;Timeout=10;Network Library=dbmssocn;'',
          ''select convert(varchar(max), certencoded(cert_id(''''cer_sup_kkm_target_30171001'''')), 1) tcert'')
      '
      exec sp_executesql @stmt = @sql_str, @params = N'@target_cert varchar(max) output', @target_cert = @target_cert output
    
      set @sql_str = 'exec (''' + 
        replace('use [master];
    	if not exists (select * from sys.symmetric_keys where name = ''##ms_databasemasterkey##'')
    	  create master key encryption by password = ''--<<Очень-Страшно-Сложный-Мастер-Пароль-Чтоб-Никто-Не-Догадался>>--'';
    	create certificate cer_@@packedhostname@@ with subject = ''cer_@@packedhostname@@ certificate'', start_date = ''20171001'', expiry_date = ''30171001'';
    	create endpoint ep_sup_kkm state = started as tcp ( listener_port = 4022 ) for service_broker (authentication = certificate cer_@@packedhostname@@, encryption = disabled);
    	create login lg_sup_kkm with password = ''--<<Очень-Страшно-Сложный-Совсем-Не-Мастер-Пароль-Чтоб-Никто-Не-Догадался>>--'';
    	create user usr_sup_kkm for login lg_sup_kkm;
    	create certificate cer_sup_kkm_target_30171001 authorization usr_sup_kkm from binary = @@target_cert@@;
    	grant connect on endpoint::ep_sup_kkm to lg_sup_kkm;'
    	, '''', '''''') +
      ''') AT [@@pLinkedServer@@]'
    
      set @sql_str = replace(@sql_str, '@@ref_tt_str@@', @ref_tt_str)
      set @sql_str = replace(@sql_str, '@@packedhostname@@', @packedhostname)
      set @sql_str = replace(@sql_str, '@@target_cert@@', @target_cert)
      set @sql_str = replace(@sql_str, '@@pLinkedServer@@', @pLinkedServer)
    
      exec sp_executesql @sql_str  -- execute at remote
      
      declare @remote_cert varchar(max)
      set @sql_str = 'select @remote_cert = rcert
        from openrowset(
          ''SQLOLEDB'',  ''Server=' + @host + @instance + ';Database=master;Uid=xxxxxx;Pwd=xxxxxx;Timeout=10;Network Library=dbmssocn;'',
          ''select convert(varchar(max), certencoded(cert_id(''''cer_' + @packedhostname +''''')), 1) rcert'')
      '
      exec sp_executesql @stmt = @sql_str, @params = N'@remote_cert varchar(max) output', @remote_cert = @remote_cert output
    
      set @sql_str = 'exec (''' + 
        replace('use [master];
    	create login lg_@@packedhostname@@ with password = ''--<<Очень-Страшно-Сложный-Совсем-Не-Мастер-Пароль-Чтоб-Никто-Не-Догадался>>--'';
    	create user usr_@@packedhostname@@ for login lg_@@packedhostname@@;
    	create certificate cer_@@packedhostname@@ authorization usr_@@packedhostname@@ from binary = @@remote_cert@@;
        grant connect on endpoint::ep_sup_kkm to lg_@@packedhostname@@;' 
    	, '''', '''''') +
      ''')'
     
      set @sql_str = replace(@sql_str, '@@host@@', @host)
      set @sql_str = replace(@sql_str, '@@db_name@@', @db_name)
      set @sql_str = replace(@sql_str, '@@ref_tt_str@@', @ref_tt_str)
      set @sql_str = replace(@sql_str, '@@packedhostname@@', @packedhostname)
      set @sql_str = replace(@sql_str, '@@remote_cert@@', @remote_cert)
      set @sql_str = replace(@sql_str, '@@pLinkedServer@@', @pLinkedServer)
    
      exec sp_executesql @sql_str -- execute at local
      
      exec sbm.usp_droplinkedserver @pLinkedServer
    
    go
    
    16 окт 19, 14:14    [21995476]     Ответить | Цитировать Сообщить модератору
     Re: Как обнаружить ошибку при работе service broker?  [new]
    felix_ff
    Member

    Откуда: Moscow
    Сообщений: 1364
    Андрей Попов
    и теперь вообще все поломалось, в профайлере пишет

    An exception occurred while enqueueing a message in the target queue. Error: 33009, State: 2. The database owner SID recorded in the master database differs from the database owner SID recorded in database 'ServiceBroker'. You should correct this situation by resetting the owner of database 'ServiceBroker' using the ALTER AUTHORIZATION statement.

    Идентификатор безопасности владельца базы данных, записанный в базе данных master, отличается от идентификатора безопасности владельца базы данных, записанного в базе данных "ServiceBroker". Устраните это различие, сбросив владельца базы данных "ServiceBroker" с помощью инструкции ALTER AUTHORIZATION.


    ServiceBroker - БД для обмена сообщениями


    Добрый день,

    судя по ошибке у вас база с именем 'ServiceBroker' была восстановлена из бэкапа, при этом владелец базы отличается от того который был на другом сервере, для начала Вам необходимо устранить данную проблему.
    сравните
    select owner_sid from sys.databases where name = 'ServiceBroker'
    select sid from sys.server_principals where name = 'имя логина владельца БД'
    

    скорее всего они будут разные, если так то выполните

    alter authorization on database::[serivceBroker] to <some_new_owner_login>;
    
    16 окт 19, 15:48    [21995662]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: 1 2 3      [все]
    Все форумы / Microsoft SQL Server Ответить