Enable TLS on sql server 2017: the powershell way

First create a certificate (thank you chatgpt):

$cert = New-SelfSignedCertificate `
  -CertStoreLocation "Cert:\LocalMachine\My" `
  -Subject "CN=MACHINE.DOMAINE.TLD" `
  -KeySpec KeyExchange `
  -KeyLength 2048 `
  -KeyExportPolicy Exportable `
  -NotAfter (Get-Date).AddYears(5) `
  -FriendlyName "Certificat SQL Server Dev" `
  -TextExtension @("2.5.29.19={critical}{text}")

One can now configure the SQL Server Instance:

SQL Server Configuration Manager > NetWork Configuration > Protocols > Certificate

Restart the instance - and watch it fail.

A certificate is born. A service stumbles. The path is clear: a key awaits its rightful bearer.

Translation: the instance does not have the right to access the private key of the certificate.

One important point here is that you get a CNG certificate and not a CSP one. With this in mind one can emit some more powershell commands (one in fact):

$cert.PrivateKey.Key

this will display the properties of the certificat and in particular: UniqueName

One can know that one is dealing with a CNG certificate because (at least) $cert.PrivateKey.CspKeyContainerInfo is empty. This was my first attempt to grant rights: it failed.

With UniqueName one can go to: C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys. Here one will find a file named after UniqueName. One can then access to the properties of the file and grant the access right to the sql server service account.

Et voilà

There are other ways:

• icacls
• winhttpcertcfg

The exposed way is efficient for SQL Server 2017 on Windows 10.