SQL Encryption challenges using Add-DBAComputerCertificate and Set-DbaNetworkCertificate

[reinisbz]

Hello,

I am trying to us use Add-DbaComputerCertificate and Set-DbaNetworkCertificate to replace an existing SQL Server encrypted connections certificate programmatically. I am running into challenges to get the certificate to load correctly so as to be usable by SQL Server. Forgive me if I don't have a supreme knowledge of Windows cert stores.

When I try:

Add-DbaComputerCertificate -ComputerName $InstanceName -Path $CertfilePath -SecurePassword $SecurePW

I find that the certificate does not have MachineKeySet = $True, and therefore it cannot be found by Set-DbaNetworkCertificate to be able to give it permissions to the SQL Server service account. From the documentation, it appears that should be the default though.

In order to force it to set MachineKeySet I have to include the -Flag Nonexportable parameter. At which point I seem to no longer have access to the key permissions, so then it Set-DbaNetworkCertificate fails because there is no private key available.

Set-DbaNetworkCertificate fails with "Stop-Function : Cannot process argument transformation on parameter 'ErrorRecord'. Cannot convert the "Can't find private key
path" value of type "Deserialized.System.Management.Automation.ErrorRecord" to type "System.Management.Automation.ErrorRecord[]".
At line:84767 char:84"

From that I see that it can't read the private key correctly, and when I try and find the cert in the Crypto folder on the filesystem, it's not there.

I have also tried adding the -Store and -Folder options, even though they should not be necessary.

I am using DBATools v2.8.2 and Powershell 5.1.

Can someone let me know if I'm missing something obvious?

---

Update: I thought I had figured it out by using a combination of Persistkeyset and NonExportable flag parameters, but it seems that it works once, but then the next time doesn't work, even though I used the same code. Either the Private Key is not associated with the cert, or the MachineKeyStore is false.

Is there a combo that works?

Thanks,

Reinis