Hi SSC Community,
I am looking into enabling Multi Server Administration, essentially I'd like to try out to run Ola's Maintenance for 3 environments on a different schedule to run Backups.
Catch #1: I don't want to set MsxEncryptChannelOptions to 0 as we do have a CA and the desired Master Server has a valid certificate including full chain of trust up to the CA.
I've tried to add a Target Server to MSX which led me to an error and a (at least to me) very interesting Error Message:
Date 9/25/2019 9:46:26 AM
Log SQL Server (Current - 9/25/2019 9:46:00 AM)
Source Logon
Message
Login failed for user 'Domain\SQL_Agent_Account'. Reason: Could not find a login matching the name provided. [CLIENT: <named pipe>]
The server is using a non default port (41433) which I specified when adding the master server in the connection string of the target server. Both servers have valid and working SPNs so I am wondering: Can I force SQL Server to use TCP/IP on both Master / Target Servers in regards to Multi Server Administration and if so, how?
Yes the domain account is in MSDB and has the TargetServersRole permission set to it.