SqlSecureConnection fails to set ACL for certificates with private key stored in CNG

[General-Fault]

Problem description

The Get-CertificateAcl method used in SqlSecureConnection uses an obsolete property x509certificate2.privatekey to retrieve the certificate private key. This returns null for keys stored in the CNG store.

The recommended technique to retrieve a certificate private key in .Net/PowerShell is using the X509Certificate extension methods GetRSAPrivateKey, GetDSAPrivateKey and GetECDsaPrivateKey.

See this blog for some helpful information about the history.

To make matters worse, the SqlSecureConnection then restarts the service and waits for it to start. It ignores the failure of the service restart caused by the missing ACL and loops indefinitely waiting for the service to start. Fixed in #1890

Verbose logs

[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Determine if the Secure Connection is in the desired state.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Getting encryption settings for instance '************'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Found thumbprint of '', with Force Encryption set to 'False'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Certificate private key is located at 'C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Thumbprint was '' but expected '**************************'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] ForceEncryption was 'False' but expected 'True'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Certificate permissions are Configured.
[DEPLOY-TEST]: LCM:  [ End    Test     ]  [[SqlSecureConnection]EncryptConnections]  in 0.0620 seconds.
[DEPLOY-TEST]: LCM:  [ Start  Set      ]  [[SqlSecureConnection]EncryptConnections]
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Getting encryption settings for instance '************'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Found thumbprint of '', with Force Encryption set to 'False'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Certificate private key is located at 'C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Thumbprint was '' but expected '**************************'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] ForceEncryption was 'False' but expected 'True'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Certificate permissions are Configured.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Securing instance '************' with Thumbprint: '**************************' and Force Encryption: 'True'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Certificate private key is located at 'C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Adding read permissions to certificate '**************************' for account 'NT Service\MSSQL$************'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Certificate private key is located at 'C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Restarting SQL Server service for instance '************'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Preferred module SqlServer found. (SQLCOMMON0023)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Importing PowerShell module 'SqlServer' with version '22.3.0' from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.3.0\SqlServer.psm1'. (SQLCOMMON0025)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Connected to SQL instance 'localhost\************'. (SQLCOMMON0018)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Getting information about service 'SQL Server'. (SQLCOMMON0037)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] 'SQL Server' service is restarting. (SQLCOMMON0038)
Failed to start service 'SQL Server (************) (MSSQL$************)'.
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Waiting for instance localhost\************ to report status online, with a timeout value of 120 seconds. (SQLCOMMON0043)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated security. (SQLCOMMON0054)
[DEPLOY-TEST]:                            [[SqlSecureConnection]EncryptConnections] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
...... this repeats until Stop-DscConfiguration is executed .....

DSC configuration

SqlSecureConnection ScriptCenterDBSecureConnection {
    InstanceName = '************'
    ServiceAccount = 'NT Service\MSSQL$************'
    Ensure = 'Present'
    Thumbprint = '*********************'
    ForceEncryption = $true
}

Suggested solution

Several years ago, I had to do something similar. This is the .Net 4.8 Framework code that I used:

private AsymmetricAlgorithm GetCertPrivateKey(X509Certificate2 certificate)
{
    if (!certificate.HasPrivateKey)
    {
        Log.Entry.Warning(this, $": Private key not found for [{certificate.GetNameInfo(X509NameType.SimpleName, false)}].");
        return null;
    }

    const string rsa = "1.2.840.113549.1.1.1";
    const string dsa = "1.2.840.10040.4.1";
    const string ecc = "1.2.840.10045.2.1";

    try
    {
        switch (certificate.PublicKey.Oid.Value)
        {
            case rsa:
                return certificate.GetRSAPrivateKey();
            case dsa:
                return certificate.GetDSAPrivateKey();
            case ecc:
                return certificate.GetECDsaPrivateKey();
            default:
                Log.Entry.Warning(this, $": Unknown key algorithm [{certificate.PublicKey.Oid.Value}].");
                return certificate.PrivateKey;
        }
    }
    catch (Exception)
    {
        Log.Entry.Warning(this, $": Private key not accessible for [{certificate.GetNameInfo(X509NameType.SimpleName, false)}]. Make sure that [{Environment.UserName}] has read permissions on the private key.");
        return null;
    }
}

This uses the certificate Oid to determine which extension method should be used to retrieve the private key.

This of course would need to be translated into PowerShell.

SQL Server edition and version

Microsoft SQL Server 2017 (RTM-GDR) (KB5021127) - 14.0.2047.8 (X64)   Jan 25 2023 08:29:55   Copyright (C) 2017 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Enterprise LTSC 2019 10.0 <X64> (Build 17763: ) (Hypervisor) 

Microsoft SQL Server 2022 (RTM-GDR) (KB5046861) - 16.0.1135.2 (X64)   Oct 18 2024 15:31:58   Copyright (C) 2022 Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 26100: ) (Hypervisor)

SQL Server PowerShell modules

Name      Version Path
----      ------- ----
SqlServer 22.3.0  C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.3.0\SqlServer.psd1
SQLPS     14.0    C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

OsName               : Microsoft Windows 10 Enterprise LTSC
OsOperatingSystemSKU : 125
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

Note: this is also seen on Win11 LTSC 24H2 - but my test machine is a Win10 LTSC deployment.

PowerShell version

Name                           Value
----                           -----
PSVersion                      5.1.17763.2090
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.2090
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version Path
----         ------- ----
SqlServerDsc 15.1.1  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.1.1\SqlServerDsc.psd1

Note: Using 15.1.1 here due to dependent PowerSTIG DSC resources in my configuration. However, I have verified that the main branch and the latest preview 17.1.0-preview0007 has the same code and thus the same issue.

[General-Fault]

The infinite loop is an old problem that was corrected for case #1889 in PR #1890,