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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Откуда:
    Сообщений: 809
    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
    Сообщений: 1341
    uaggster,

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

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

    Чтож как и обещал делюсь знаниями по настройке 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
    Сообщений: 1341


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

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

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

    Откуда: Moscow
    Сообщений: 1341
    Переходим непосредственно к настройке сначала работаем на основной машине (когда я буду выполнять какие то действия на ноуте буду отдельно писать)
    Поскольку у нас предполагается обмен данными между экземплярами нам необходимо обеспечить безопасность транспорта, что бы сообщения шифровались на выходе и 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
    Сообщений: 1341
    Далее давайте убедимся что оба инстанса могут видеть конечные точки друг друга:

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

    Откуда: Moscow
    Сообщений: 1341
    Отлично, нами были созданы сертификаты на обоих серверах и проверено что с каждого сервера прослушиваемый 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
    Сообщений: 1341
    Далее затронем аспект шифрования: конфигурация конечной точки в каждом экземпляре требует задания параметра 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
    Сообщений: 1341
    Теперь необходимо настроить маршруты.
    Необходимо понимать КАК компонент определяет куда ему направить сообщение, а это определяется вот таким правилом:
    - при поступлении инструкции 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
    Сообщений: 1341
    Теперь сразу покажу типичную ошибку которая может быть допущена на этой стадии:
    В текущий момент инфраструктура для обеспечения безопасности диалога у нас почти готова, но давайте посмотрим что же будет если я в текущий момент попытаюсь отправить сообщение в принимающую очередь на ноутбук?
    Сначала проверим что будет если попытаться послать сообщение в принимающей службе на ноутбуке с опцией 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
    Сообщений: 1341
    Как видно у нас вернулось сообщение что произошла ошибка открытия диалога, 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
    Сообщений: 1341
    Чуть ранее я уже озаботился этим моментом, создал главный ключ базы, защитил его главным ключем службы, создал пользователя у которого есть право 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
    Сообщений: 1341
    вот блин не тот скриншот в предыдущем посте приложил
    +
    (жалко что посты изменять нельзя)
    , вот правильный

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

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

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

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

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


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

    Откуда: Moscow
    Сообщений: 1341
    Выполнив данную инструкцию:
    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
    Сообщений: 1341
    Теперь что касается отлова возможных ошибок возникающих в компоненте, всегда необходимо понимать на какой стадии возникла ошибка, была ли она на транспортном уровне или на уровне комуникации между службами.
    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]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить