Add-DbaRegServer: Settings "Encrypt Connection" and "Trust server certificate" not saved when using CMS

[fm-knopfler]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Using Add-DbaRegServer to register an instance to a Central Management Server, it's connection settings aren't saved. When registering to a Local Server Group, connection settings are saved. This is visible in SSMS.

Steps to Reproduce

$cmsInstance = "server01.example.org\MGMT" 
$server = "server04.example.org\SQL001"

# Local Server Groups, connection settings are applied
Connect-dbaInstance -SqlInstance $server -EncryptConnection -TrustServerCertificate | Add-DbaRegServer -Name "TST01" -Group "Test"

# CMS, connections settings not applied
Connect-dbaInstance -SqlInstance $server -EncryptConnection -TrustServerCertificate | Add-DbaRegServer -Name "TST01" -SqlInstance $cmsInstance -Group "Test"

Please confirm that you are running the most recent version of dbatools

2.1.11

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name Value

---

PSVersion 5.1.17763.5576
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.17763.5576
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU25) (KB5033688) - 15.0.4355.3 (X64) Jan 30 2024 17:02:22 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

.NET Framework Version

PSChildName Version

---

Client 4.8.03761 Full 4.8.03761 Client 4.0.0.0

[460163]

I have the exact same issue, but I am trying to register the server on a remote CMS. When I use the code below, the Trust Server Certificate is not enabled.

dbtools v2.1.14 Powershell v7.4.2

Here is the code: $ConnString = New-DbaConnectionString -TrustServerCertificate -SqlInstance $InstanceName Add-DbaRegServer -SqlInstance $RegInstance -SqlInstance $InstanceName -ConnectionString $ConnString -Group $Group

[StanDaMan0505]

Any update on this issue?

[460163]

@StanDaMan0505: It appears to be a limitation of the product. I have submitted a ticket with Microsoft, and they have confirmed that this process must be completed manually. There are no stored procedures or PowerShell commands available to configure additional connection parameters. At present, only basic connection information can be added to CMS.

[aRickInTheMud]

I'll second that it is a limitation. The entries are stored in [msdb].[dbo].[sysmanagement_shared_registered_servers_internal], and there are no other columns to store connection parameters, just the name of the registered server, the "server_name" which is what is actually used in the connection string, and a description. I haven't figured out why the registered server smo objects have all those extra settings if they can't store them anywhere.

[niphlod]

IMHO we should park this till SMO catches up, and if msdb doesn't get any new column, even then it'll work just if CMS is "vNext" (even 2022 doesn't have any new columns in there, AFAIK)

[fm-knopfler]

IMHO we should park this till SMO catches up, and if msdb doesn't get any new column, even then it'll work just if CMS is "vNext" (even 2022 doesn't have any new columns in there, AFAIK)

Yeah, I don't think there's anything we can do.

[niphlod]

closing this, if and when needed we can always reopen.