Network certificate commands - CNG (Key Storage Provider) keys: SQL Server loads them, dbatools cannot inspect them, and PowerShell 7 rejects every key

[andreasjordan]

Summary

While reviewing a blog article about SQL Server network certificates, we tested in the lab how SQL Server and the dbatools network certificate commands handle certificates whose private key lives in a CNG Key Storage Provider (KSP) instead of a legacy Cryptographic Service Provider (CSP). Two things came out of it:

1. SQL Server 2019, 2022 and 2025 load a KSP certificate and serve TLS with it when the thumbprint is set in the registry. The Microsoft documentation says KSP keys "aren't compatible with SQL Server", and dbatools (PR Set-DbaNetworkCertificate - Say why a private key is unsuitable #10671, written by us) repeats that as "refused by SQL Server itself". The check in dbatools is defensible because it follows the documentation, but the wording is wrong, and -Force cannot actually configure such a certificate.
2. The way the commands inspect the private key ($cert.PrivateKey cast to RSACryptoServiceProvider, then CspKeyContainerInfo) breaks in two situations: for every KSP key under Windows PowerShell 5.1, and for every key under PowerShell 7, where PrivateKey is always an RSACng.

Nothing here affects New-DbaComputerCertificate, which creates a CSP key with KeySpec = 1 exactly as the documentation asks. This issue collects the findings; a pull request will follow after the next release.

What the lab showed

Windows Server 2025, three instances on one host, each restarted with a fresh self-signed KSP certificate (New-SelfSignedCertificate -Provider "Microsoft Software Key Storage Provider", subject and SAN = host FQDN, Server Authentication EKU, RSA 2048, service account granted read on the key file under %ProgramData%\Microsoft\Crypto\Keys), thumbprint written to SuperSocketNetLib\Certificate:

Instance	Build	ERRORLOG after restart	encrypt_option with Encrypt=True
SQL Server 2019	15.0.4430.1	The certificate [Cert Hash(sha1) "E417..."] was successfully loaded for encryption.	TRUE
SQL Server 2022	16.0.4255.1	The certificate [Cert Hash(sha1) "6DB9..."] was successfully loaded for encryption.	TRUE
SQL Server 2025	17.0.4055.5	The certificate [Cert Hash(sha1) "0E91..."] was successfully loaded for encryption.	TRUE

With TrustServerCertificate=False the client failed with "The certificate chain was issued by an authority that is not trusted", which is the expected reaction to a self-signed certificate and shows the KSP certificate was the one presented. After removing the certificates and restarting, every instance went back to A self-generated certificate was successfully loaded for encryption.

Not tested: SQL Server 2017 and earlier, other operating systems, and SQL Server Configuration Manager itself. We only set the registry value.

What the documentation says

Certificate Requirements for SQL Server (dated 2026-02-27):

The certificate must be created using the KeySpec option of AT_KEYEXCHANGE. This requires a certificate that uses a legacy Cryptographic Storage Provider to store the private key.

Certificates created with a Key Storage Provider (KSP), such as the Microsoft Software Key Storage Provider, use KeySpec = 0 and aren't compatible with SQL Server.

The same page notes that since SQL Server 2019 "SQL Server Configuration Manager automatically validates all certificate requirements during the configuration phase itself". Our reading is that the incompatibility lives in Configuration Manager's validation, not in the engine, but we have not verified that. If anyone knows why Configuration Manager insists on a CSP key, or whether it really rejects KSP certificates, that would settle it.

Two more documented facts that matter for these commands, from Encrypt Connections by Importing a Certificate:

• "Starting with SQL Server 2022 CU22 and SQL Server 2025, the thumbprint of the certificate is no longer case sensitive. In versions prior to SQL Server 2022 CU22 and SQL Server 2025, SQL Server Configuration Manager only displays certificates imported through the registry if the thumbprint in the registry is an exact match for the thumbprint, including case sensitivity. If the thumbprint doesn't match the case exactly, the certificate isn't displayed in SQL Server Configuration Manager, but the certificate is still loaded and used by SQL Server." (Lab: upper and lower case both load on all three versions.) Set-DbaNetworkCertificate writes lower case, which is fine; the comment "to make it compat with SQL config" could cite this.
• "Certificate precedence": with an empty registry value, SQL Server picks a certificate from the store whose subject contains the FQDN and whose key the service account can read, before falling back to the self-generated one. Seen in the lab: after clearing the value with the test certificate still in the store, the ERRORLOG reported that certificate, not the self-generated one. Get-DbaNetworkConfiguration reports no certificate in that case, which is correct for the registry but not for what the engine uses.

Findings in dbatools

1. Set-DbaNetworkCertificate cannot find a KSP key file, so -Force never works for them

Set-DbaNetworkCertificate.ps1#L188-L196:

$keyPath = $env:ProgramData + "\Microsoft\Crypto\RSA\MachineKeys\"
if ($PSVersionTable.PSVersion.Major -ge 6) {
    $keyName = $cert.PrivateKey.Key.UniqueName
} else {
    $keyName = $cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
}

• The directory is hard-coded to RSA\MachineKeys. KSP key files live in %ProgramData%\Microsoft\Crypto\Keys.
• Under Windows PowerShell 5.1, $cert.PrivateKey is $null for a KSP key, so the expression yields an empty name without an exception, and the command throws "Can't find private key path".

Result: -Force, whose purpose is to configure a certificate that fails the suitability check, cannot configure a KSP certificate at all. Verified in the lab on 5.1 and 7.6:

	CSP key	KSP key
5.1: $cert.PrivateKey	RSACryptoServiceProvider	$null
5.1: .CspKeyContainerInfo.UniqueKeyContainerName	file name	empty, no exception
7.6: $cert.PrivateKey	RSACng	RSACng
7.6: .CspKeyContainerInfo...	empty (property does not exist)	empty
both: [RSACertificateExtensions]::GetRSAPrivateKey($cert)	RSACng	RSACng
both: $rsa.Key.UniqueName	file name in RSA\MachineKeys	file name in Crypto\Keys
both: $rsa.Key.Provider.Provider	Microsoft RSA SChannel Cryptographic Provider	Microsoft Software Key Storage Provider

GetRSAPrivateKey plus Key.UniqueName, with the directory chosen by provider, works for both key types in both editions.

2. Test-DbaNetworkCertificate and Get-DbaNetworkConfiguration reject every certificate under PowerShell 7 on the SQL Server host

Test-DbaNetworkCertificate.ps1#L213-L216 and Get-DbaNetworkConfiguration.ps1#L249-L250:

$privateKeyValid = $cert.PrivateKey -is [System.Security.Cryptography.RSACryptoServiceProvider] -and
$cert.PrivateKey.CspKeyContainerInfo.KeyNumber -eq [System.Security.Cryptography.KeyNumber]::Exchange

Under PowerShell 7, PrivateKey is an RSACng for every key, CSP keys included (table above). The scriptblock runs on the target host; Invoke-Command2 runs it in-process for the local host. So on PowerShell 7 on the SQL Server itself, no certificate is ever suitable, not even the one New-DbaComputerCertificate just created. Against a remote host the scriptblock runs in the 5.1 endpoint and the problem does not show, which is why PR #10671's lab verification on 7.6 did not catch it. This one is inferred from the type test, not from a full command run; the lab SQL hosts have no PowerShell 7.

3. Wording: "refused by SQL Server itself"

PR #10671 added, in Set-DbaNetworkCertificate and the PrivateKeyValid description of Test-DbaNetworkCertificate, that a KSP key "is refused by SQL Server itself" / "CNG / Key Storage Provider keys are not supported by SQL Server". The lab shows the engine loads and uses them on 2019, 2022 and 2025. The check can stay as it is, following the documentation, but the message should say "not supported according to Microsoft's certificate requirements" and point to -Force, which then has to work (finding 1).

4. New-DbaComputerCertificateSigningRequest defaults to KeyLength = 1024

New-DbaComputerCertificateSigningRequest.ps1#L111. A certificate issued from that request fails dbatools' own PublicKeyValid check, which requires 2048 bits. New-DbaComputerCertificate already defaults to 2048.

Proposed direction

• Keep the suitability rule aligned with the documentation (CSP key, AT_KEYEXCHANGE), because Configuration Manager validates the same rule and older versions were not tested.
• Rewrite the private key inspection on GetRSAPrivateKey: provider name decides CSP vs. KSP, KeyNumber comes from CngKey for CSP keys, Key.UniqueName plus the provider-specific directory gives the file path. Works on 5.1 and 7 for both key types.
• Make -Force really configure a KSP certificate, and correct the message and help text.
• Default the CSR key length to 2048.
• Optionally report in Get-DbaNetworkConfiguration when the registry value is empty but a store certificate matches the auto-selection rule.

The pull request will come after the next release, to give the release some room.

Reproduction

On a SQL Server host, as administrator, Windows PowerShell 5.1:

$splatCertificate = @{
    Subject           = "CN=$([System.Net.Dns]::GetHostEntry($env:COMPUTERNAME).HostName)"
    DnsName           = [System.Net.Dns]::GetHostEntry($env:COMPUTERNAME).HostName, $env:COMPUTERNAME
    CertStoreLocation = "Cert:\LocalMachine\My"
    KeyAlgorithm      = "RSA"
    KeyLength         = 2048
    Provider          = "Microsoft Software Key Storage Provider"
    KeyUsage          = "DigitalSignature", "KeyEncipherment"
    TextExtension     = @("2.5.29.37={text}1.3.6.1.5.5.7.3.1")
}
$cert = New-SelfSignedCertificate @splatCertificate
$null -eq $cert.PrivateKey                                       # True on 5.1
$cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName      # empty, no error
$rsa = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($cert)
$rsa.Key.UniqueName                                              # key file name, found under $env:ProgramData\Microsoft\Crypto\Keys
Set-DbaNetworkCertificate -SqlInstance $env:COMPUTERNAME -Thumbprint $cert.Thumbprint -Force   # "Can't find private key path"

Then grant the service account read on that key file, set the thumbprint in SuperSocketNetLib\Certificate, restart, and read the ERRORLOG.

created by Claude and reviewed by Andreas Jordan

[andreasjordan]

Configuration Manager tested: it accepts the KSP certificate too

The open question from the issue is answered. On the same host (Windows Server 2025, SQL Server 2022 instance), with one legacy CSP certificate and one KSP certificate in the computer store, both issued to the host FQDN with Server Authentication EKU and the service account granted read on both key files:

• SQL Server 2022 Configuration Manager (SQLServerManager16.msc) and SQL Server 2025 Configuration Manager (SQLServerManager17.msc) both list both certificates in the Certificate tab of the instance's protocol properties.
• Selecting the KSP certificate and applying produced no error.
• Configuration Manager wrote the thumbprint to SuperSocketNetLib\Certificate in lower case: 0549874ea5e8f861d916f2a1b7cccff9b5bb58d8. That is the answer to why Set-DbaNetworkCertificate lower-cases the value, and it matches the documented behaviour that Configuration Manager before SQL Server 2022 CU22 only displays a registry value with an exact case match.
• After a restart the ERRORLOG reported The certificate [Cert Hash(sha1) "0549874E..."] was successfully loaded for encryption.

So neither the engine (2019, 2022, 2025) nor the current Configuration Manager (2022, 2025) refuses a Key Storage Provider key. The statement in the Microsoft documentation that KSP certificates "aren't compatible with SQL Server" does not hold on this platform, and the dbatools message "refused by SQL Server itself" is wrong. What remains true is that the documentation still lists the CSP / AT_KEYEXCHANGE requirement, and that nothing older than SQL Server 2019 and nothing but Windows Server 2025 was tested.

For the pull request this shifts the direction: the suitability check should stop treating a KSP key as unsuitable by itself. What it can still verify is that the private key is an RSA key the service account can read, using GetRSAPrivateKey, which works for both provider generations in both PowerShell editions.

Everything was removed again afterwards; the instance is back on its self-generated certificate.

created by Claude and reviewed by Andreas Jordan

[andreasjordan]

Proposal: a -Provider parameter for New-DbaComputerCertificate, legacy CSP stays the default

What the command creates today

New-DbaComputerCertificate (and New-DbaComputerCertificateSigningRequest) writes a certreq configuration file with

ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
KeySpec = 1

so every certificate it creates has a legacy CSP key, on both paths: self-signed (RequestType = Cert) and the request to a certificate authority (RequestType = PKCS10, then certreq -submit and -accept). certreq generates the key locally in both cases, so the configuration file decides the key type, not the CA template.

Both key types work, verified in the lab

The Key Storage Provider variant of the configuration file differs in three lines:

ProviderName = "Microsoft Software Key Storage Provider"
KeyAlgorithm = RSA

and no ProviderType / KeySpec lines, which belong to the CSP world.

• Self-signed: certreq -new created and installed the certificate, printed the same Serial Number: block the command parses today, and the result had subject and SAN with FQDN and host name, Server Authentication EKU, key usage KeyEncipherment + DigitalSignature, key file under %ProgramData%\Microsoft\Crypto\Keys, provider "Microsoft Software Key Storage Provider".
• Certificate authority: the same request submitted to the lab's enterprise root CA (Windows Server 2025, certreq -submit -attrib "CertificateTemplate:WebServer", then certreq -accept -machine) was issued immediately. The WebServer template is a schema version 1 template whose pKIDefaultCSPs lists only the two legacy SChannel providers; the CA did not care which provider the client used. The issued certificate: issuer LabRootCA1, template WebServer, two years, chain valid, private key in the Key Storage Provider, key file under Crypto\Keys, HasPrivateKey = True and PrivateKey = $null under Windows PowerShell 5.1 as expected for a KSP key.

Proposed parameter

[ValidateSet("Microsoft RSA SChannel Cryptographic Provider", "Microsoft Software Key Storage Provider")]
[string]$Provider = "Microsoft RSA SChannel Cryptographic Provider"

The legacy CSP stays the default: it is what every supported SQL Server version accepts, it is what Microsoft's certificate requirements still name, and existing callers see no change. The configuration file writer switches the three lines above on the provider. The same parameter fits New-DbaComputerCertificateSigningRequest.

Order of work, because a KSP certificate from New-DbaComputerCertificate would today be refused by Test-DbaNetworkCertificate and could not even be forced (finding 1): first the private key handling in the network certificate commands, then this parameter.

Not tested yet, to be covered by the pull request: the remote path, where the command exports a PFX and imports it on the target with Add-DbaComputerCertificate. A PFX carries the provider name, so the key should land in the same provider on the target, but that is expectation, not evidence.

created by Claude and reviewed by Andreas Jordan

[andreasjordan]

Remote PFX path tested: the Key Storage Provider survives the transfer

The one part of the -Provider proposal still marked as untested is now covered. A self-signed KSP certificate for SQL03.ordix.local was created on the workstation with the certreq configuration from the previous comment, exported with $storedCert.Export(PFX, $password) as New-DbaComputerCertificate does, and imported on SQL03 in two ways:

• Variant A, the remote scriptblock of New-DbaComputerCertificate verbatim: New-Object X509Certificate2($bytes, $password, "Exportable, PersistKeySet") and X509Store.Add.
• Variant B, the real Add-DbaComputerCertificate -ComputerName SQL03 -Path ksp.pfx -SecurePassword ..., which imports into an X509Certificate2Collection, re-exports, and imports the collection on the target.

Both results on SQL03 (Windows PowerShell 5.1):

HasPrivateKey=True  PrivateKeyNullOn51=True  Provider=[Microsoft Software Key Storage Provider]
KeyFile: Crypto\Keys=True  MachineKeys=False  SAN=SQL03.ordix.local,SQL03  FriendlyName=[lab_ksp_pfx]

So the PFX carries the provider, the key lands in the Key Storage Provider on the target, and the friendly name and SAN come along. The proposal needs no special handling for the remote path; the same three configuration lines cover local, CA and remote. Everything was deleted again on both machines.

created by Claude and reviewed by Andreas Jordan