Multi Server Administration - Protocol issue

• DinoRS

  SSCrazy

  Points: 2683

  More actions

  September 25, 2019 at 1:48 pm

  #3683308

  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:

  Date9/25/2019 9:46:26 AM
  LogSQL Server (Current - 9/25/2019 9:46:00 AM)
  
  SourceLogon
  
  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.

• Andrey

  SSC Eights!

  Points: 840

  More actions

  September 25, 2019 at 2:08 pm

  #3683321

  for connection string try the following  :

  tcp:hostname,portname

  link:

  https://docs.microsoft.com/en-us/sql/tools/configuration-manager/creating-a-valid-connection-string-using-tcp-ip