Set-DbaNetworkCertificate CspKeyContainerInfo is Empty

jmpederson1 commented 9 months ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

WARNING: Read-only permissions could not be granted to certificate, unable to determine private key path. WARNING: [15:01:00][Set-DbaNetworkCertificate] New certificate will not take effect until SQL Server services are restarted for x

Steps to Reproduce

Set-DbaNetworkCertificate -Thumbprint $thumbprint

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

2.1.7

Other details or mentions

In this if statement:

if ($null -ne $cert.PrivateKey) {
     $keyPath = $env:ProgramData + "\Microsoft\Crypto\RSA\MachineKeys\"
     $keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
     $keyFullPath = $keyPath + $keyName
}

$cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName is empty. $cert.privatekey.key.uniquename returns the correct value.

Also, the certificate is not applied to the SQL instance.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name Value

PSVersion 7.4.1 PSEdition Core GitCommitId 7.4.1 OS Microsoft Windows 10.0.17763 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Server 2019 (RTM-CU24) (KB5031908) - 15.0.4345.5 (X64)

.NET Framework Version

.NET 8.0.1

jankpriv commented 9 months ago

I get the error "WARNING: Read-only permissions could not be granted to certificate, unable to determine private key path." When running "Set-DbaNetworkCertificate -SqlInstance $server -Thumbprint $CertThumbprint -RestartService" The certificate is imported to the remote SQL server with Exportable private key OK when not using the Core-version to run "Add-DbaComputerCertificate -ComputerName $server -Path $PFXFileFull -SecurePassword $SecurePassword", but I still get the above error when afterwards running the above SET-command.

When running the same Add-command in PS 7.4.1 I got the error: "[Add-DbaComputerCertificate] Can't import certificate. | X509Certificate is immutable on this platform. Use the equivalent constructor instead."

In Core I also tried constructing a X509 Certificate with this command : "$cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($PFXFileFull, $VaultJsonMysecret)" and passing it like this "Add-DbaComputerCertificate -ComputerName $server -Certificate $cert", and the error was the same (Immutable).

I run this from a computer with DBATools installed for both PS generations, in order to import and activate a third party certificate (obtained with Win-ACME) to all my SQL server (ForEach $Server etc.). the certificate is Wildcard, and works perfectly on the servers when adding them to SQL manually.

Update: When Adding the certificate from the PFX-file with -flag "-Flag NonExportable" the following SET-command is able to set the permissions and activate the certificate! But the error when using Core remains.

Am I missing something here - is there a more modern way of automating certificate renewals with DBATools?

By the way, Win-ACME renewal scripting seems perfect for automating SQL-certificates, as it allows a Powershell script to be run after each scheduled renewal (60 days). If only my PS script would work...

Please let me know if this should be posted somewhere else.

andreasjordan commented 6 months ago

Can you please check again with the latest version of dbatools?

The command was changed recently: https://github.com/dataplat/dbatools/pull/9342/files

dataplat / dbatools