0

I am trying to set up Multi Server Administration so that I can easily update multiple instances of the same database across our network with ease. Where I am getting lost is how to set up the SQL SERVER AGENT service on the target machines.

Right now the SQL SERVER AGENT services on the machines are set to log on as NTSERVICE\SQL SERVER AGENT.

My SQL Server Agent process is set to log in as LocalSystem

My overall goal here is to figure out why I can't set any target servers from my machine, as I always run into the following error:

TITLE: Microsoft.SqlServer.Smo
------------------------------

MSX enlist failed for JobServer 'MYPC'. 

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18206.0+((SSMS_Rel).191029-2112)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'MYPC'.) (Microsoft SQL Server, Error: 22026)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2027&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

When looking at the log on my machine I see the following error when trying to enlist the target machine:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

Improve this question

1 Answer 1

Reset to default
3

One weakness of MSX is that target servers uses Windows authentication and you can't change that.

I.e., your target Agent need a domain account as a service account and that account need to exist as a Windows login on the master SQL Server (and also be a user in the msdb database there and a member of the TargetServersRole).

Improve this answer
2
  • Thank you for the answer. I have to also add users logins into the "SSISDB"?
    – MaxB
    Commented Feb 5, 2021 at 13:20
  • 1
    Ssisdb has nothing to do with MSX, so it isn't needed for MSX.
    – Tibor Karaszi
    Commented Feb 5, 2021 at 14:10

Your Answer

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

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