SqlSecureConnection: Unable to specify certificate by using friendly name

Problem description

When using the CertificateDsc module with the CertReq resource, an internal signed certificate can be issued on the fly. This certificate is not usable for the SqlSecureConnection, as the resource currently does not accept anything else than the thumbprint to specify the certificate. But the thumbprint is not available during compile time.

Suggestion: Add the option to use the friendly name to specify the certificate.

I can provide a PR.

Verbose logs

Not available, as this is a new feature.

DSC configuration

SqlSecureConnection "Solution Option 1"
{
    InstanceName    = 'MSSQLSERVER
    Thumbprint      = 'Certificate for MSSQLSERVER'   # Thumbprint accept a friendly name
    ForceEncryption = $false
    Ensure          = 'Present'
    ServiceAccount  = 'sa-sql'
}

SqlSecureConnection "Solution Option 2"
{
    InstanceName    = 'MSSQLSERVER
    Thumbprint      = ''
    FriendlyName    = 'Certificate for MSSQLSERVER'
    ForceEncryption = $false
    Ensure          = 'Present'
    ServiceAccount  = 'sa-sql'
}

Suggested solution

Option 1:
The thumbprint parameter should accept the friendly name of a certificate. In the code, the thumbprint will be dynamically detected.

Option 2:
Leave the thumbprint empty and add a new DSC property called FriendlyName to specify the friendly name. If specified, the thumbprint will be overwritten.

SQL Server edition and version

SQL Server 2022

SQL Server PowerShell modules

Name      Version    Path
----      -------    ----
SqlServer 22.0.59    C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.0.59\SqlServer.psd1

Operating system

OsName               : Microsoft Windows Server 2022 Standard
OsOperatingSystemSKU : StandardServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 2009
WindowsBuildLabEx    : 20348.1.amd64fre.fe_release.210507-1500
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

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

SqlServerDsc version

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

Start at the SqlSecureConnection DSC resource and trace how the Thumbprint property is resolved for CertificateDsc and CertReq scenarios. Decide how friendly-name selection should interact with the existing thumbprint behavior, then verify that certificates can be selected by friendly name while current thumbprint configurations continue to work.