0

Within my environment we currently have a MSSQL installation which serves as the Master in a Multiserver Agent environment (MSX). Due to this server being within a tightly controlled network the necessary ports for MSX functionality have not been opened.

As shown in the image, when I try to enlist a target (TSX) to the master (MSX) I am given an error that the RFC server is not available, I am also unable to deploy multi-server jobs to targets.

What ports should I request to be opened from targets to the master and vice versa?

enter image description here

Improve this question
5
  • Look which ports are listened by CMS... 6400 by default AFAIR.
    – Akina
    Oct 15, 2019 at 7:38
  • @akina Do you recommend a way of seeing which ports are listening? The documentation is very limited in this regard.
    – Asher
    Oct 15, 2019 at 9:43
  • Yes. Or look at CMS settings - service port may be settable...
    – Akina
    Oct 15, 2019 at 9:47
  • 1
    CMS is a slightly different feature. This is about Multi-server job administration. The master server is considered the MSX and the target is the TSX. Check out: stackoverflow.com/q/26222524/1948808
    – AMtwo
    Oct 15, 2019 at 11:00
  • @AMtwo thanks for the link. Unfortunately the answer posted on that link remains unanswered. I know that for sure RPC endpoint mapper port 135 needs to be opened, but there is also a range of dynamic ports that RPC uses. This link seems to suggest a range of ports: blogs.technet.microsoft.com/fort_sql/2012/07/03/…
    – Asher
    Oct 15, 2019 at 12:03

2 Answers 2

Reset to default
1

When you attempt to enlist the Target (TSX) to the Master (MSX), the step that is failing is "Make sure the Agent service for '{MSX Server}' is running." This step simply checks to see that the SQL Server Agent is running on the MSX Server.

You can instead try to use msdb.dbo.sp_msx_enlist (documented here), which is run on the TSX to enlist it on the MSX. This may provide a more helpful error message, or it may even succeed.

If it succeeds, you can query msdb.dbo.systargetservers on the MSX to confirm that the server has been successfully enrolled, and is polling for instructions to download. Next, you can poll msdb.dbo.sysdownloadlist to see those instructions, and see if they are being consumed correctly by the TSX. There is an error_message column which should include download errors. Note, errors being logged here would be dependent on the TSX successfully connecting to the MSX--so your TSX would need to be able to connect to the MSX's DB Engine port (1433 by default) in order to even write the error messages.

Improve this answer
1
  • Thank you for your thorough answer. Port 1433 is open, I am able to enlist using the stored procedure and see the server in the systargerservers table. When querying the sysdownloadlist, I see the commands that are trying to be sent to the target, but the date_downloaded remains empty / status remains 0.
    – Asher
    Oct 16, 2019 at 6:36
1

You might want to open the following ports:

  1. the TCP port the "target" server is listening on, you can check that on the target server: select * from sys.dm_tcp_listener_states

  2. for the adding, it uses RPC (remote procedure call): open up ports : TCP & UDP : 139

  3. if you want to take SMB for backups (can but dislike) : TCP & UDP: 445

  4. check that the registery key for the instance sql agent on the target is set to 0 key to check: HKLM\Software\Microsoft\Microsoft SQL Server\ Your instance\SQLServerAgent\MsxEncryptChannelOptions

  5. If your target is a higher version than the MSX create the service account user of the target on the Master MSX prior to adding. check by: select * from sys.dm_server_services on the target server. Grant that found service account the role memberschip "targetserverrole" in the MSDB on the master MSX.

Improve this answer
1
  • Just to add onto this, I've encountered RPC unavailability errors with drops on port 135 too.
    – Asher
    Jul 5 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.