Ostatnimi czasy intensywnie pracuję z Service Brokerem i jak to bywa w takim związku, poznaję jego humory i sposoby rozwiązywania problemów. W tym wpisie chcę udokumentować kilka objawów i rozwiązań z Service Brokerem:
No connection could be made because the target machine actively refused it
Rozwiązania szukałbym w uprawnieniach po obu stronach konwersacji, ale zdarza się że przyczyna jest cąłkiem inna. Uprawnienie connect musi być nadane dla loginu połączonego z certyfikatem używanym do uwierzytelnienia po obu stronach. Jeżeli obawiasz się, że błędnie nadajesz uprawnienia to możesz tymczasowo nadać uprawnienie connect dla public (ale nie zostawiaj tak na stałe)
W moim przypadku najczęściej problem brał się z tego, że podczas konfiguracji pomyliłem się w certyfikatach i łącząc się używałem innego niż ten, który był rzeczywiście przypisany do endpoint. Podczas debuggowania pomocna będzie sesja profilera (zobacz opis i screen poniżej)
This message could not be delivered because the destination queue has been disabled
Jeżeli procedura uruchamiana w momencie otrzymania wiadomości zawiera błąd, to kolejka z jaką jest powiązana usługa service broker może przełączyć kolejkę w stan „disabled”. Sprawdź więc, czy kolejka jest wyłączona, włącz ją i debuguj procedurę aktywowaną do obsługi message. Jeżeli kolejka natychmiast się wyłącza zrób tak: Odłącz procedurę aktywacyjną z kolejki, a potem uruchom procedurę ręcznie (w trybie debug, aby odnaleźć przyczynę). Po naprawieniu znowu połącz procedurę z kolejką i włącz kolejkę
Wyłączenie kolejki nie jest czymś, co przechodzi na serwerze niezauważone. Skoro procedura podpięta do kolejki ma błąd i powoduje wyłączenie kolejki, to informacje o napotkanym błędzie znajdziesz w errorlogu. W moim przypadku znalazłem tu na przykład, że ANSI_NULLS oraz QUOTED_IDENTIFIER nie są ustawione poprawnie do pracy z XML. Przed utworzeniem procedury obsługującej kolejkę trzeba było wpisać:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO
i dopiero dalej:
CREATE PROCEDURE....
This message could not be delivered because the user with ID 6 in database ID 20 does not have permission to send to the service. Service name: 'XXXX’.
Ten komunikat jest dość jasny. Z jakiegoś powodu brakło uprawnienia SEND na zdalny serwis. Zależnie od tego jak został zdefiniowany service broker
- najpierw budowane jest połączenie między endopointami i na tym etapie wymagane uprawnienie to CONNECT do zdalnego endpoint nadane lokalnemu loginowi(w przypadku uwierzytelnienia na certyfikatach uprawnienie CONNECT musi być nadane loginowi powiązanemu z certyfikatem lokalnym)
- następnie w ramach uwierzytelnionego połaczenia użytkownik lokalny wysyła message do zdalnego serwisu i tu tymagane jest dla niego uprawnienie SEND. Najwyraźniej właśnie tego uprawnienia brakowało
DNS lookup failed with error: '11001(No such host is known.)’
Jeśli konwersacja jest nawiązywana między instancjami SQL, to musiałeś również zdefiniować routing. Skoro uzyskujesz taki błąd to najprawdopobniej coś jest nie tak z routingiem. Przyjrzyj się nazwie usługi service brokera, adresowi DNS i numerowi portu na jakim nasłuchuje usługa. Czynność powtórz dwa razy rozdzielając kawą między jednym a drugim testem i wiem co piszę!
Brak błędu – konwersacja wisi w sys,transmission_queue – brak błędu i brak wyniku
Najcięższy przypadek, bo… prawie działa. Najtrudniej w tym przypadku jest znaleźć błąd, a kiedy go już masz, to dalej będzie dobrze. Na odnalezienie błędu mogę polecić:
- Sprawdzić status widoczny w sys.transmission_queue (w bazie, w której są wysyłane wiadomości)
- 2 razy sprawdzić konfigurację krok po kroku
- Na obu partnerach konwersacji włączyć Profiler-a i zaznaczyć wszystkie zdarzenia z grupy Service Broker (można jeszcze ewentualnie zostawić włączone zdarzenia dla Login Audit). W zebranych wynikach szukaj zdarzeń jak na ilustracji poniżej:
zazwyczaj komunikaty są logowane parami i po podświetleniu drugiego komunikatu można odnaleźć więcej wskazówek o tym co się stało. Przypadki, które udało mi się zidentyfikować to:
- baza danych z wyłączonym service brokerem (baza po odtworzeniu ma gaszoną flagę SERVICE_BROKER_ENABLED i trzeba ją włączyć ręcznie)
- baza danych msdb z wyłączonym service brokerem – w tym przypadku problem również wziął się z odtworzenia tej systemowej bazy danych
An error occurred while receiving data: '10053(An established connection was aborted by the software in your host machine.)’.
Sejsa debuggera na docelowym serwerze wykryła następujące problemy:
The message could not be delivered because it could not be classified. Enable broker message classification trace to see the reason for the failure.
Could not forward the message because forwarding is disabled in this SQL Server instance.
Przyczyna błedu nie jest mi do końca znana, bo nie wiem co się działo z serwerem/bazą wcześniej. Co jednak istotne, komunikat wysyłany do serwera był rzeczywiście adresowany do niego, a nie gdzieś indziej, więc forward zupełnie nie powinien być wykonywany.
Przyczyną mogły byc route zdefiniowane w bazie msdb, bo to tam się rozstrzyga czy otrzymany komunikat ma byc dostarczany lokalnie czy przesylany dalej. Route byly jednak poprawne (wskazywaly adres local) i nawet ich odtworzenie (na wszelki wypadek) nie pomogło.
Dopiero „odświeżenie Service Brokera” rozwiązało problem:
USE master GO ALTER DATABASE MY_DB SET NEW_BROKER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE MY_DB SET ENABLE_BROKER GO
Te polecenia wygenerowały nowy GUID dla service brokera. Potem jeszcze raz odtworzyłem route w msdb i zaczeło działać od razu! Prawdopodobnie definicja route w msdb gdzieś/jakoś binduje do service broker po GUID a nie po nazwie i dlatego te kroki pomogły
Innym razem przy tym samym błedzie profiler znalazł:
Connection handshake failed. The login 'public’ does not have CONNECT permission on the endpoint. State 84.
W moim przypadku oznaczało, to że podczas instalacji, coś nie tak poszło z certyfikatami. Ponowna instalacja, gdzie uważnie sprawdzałem czy certyfikaty się generują czy nie, naprawiła problem!
