SqlSecureConnection: Unable to specify certificate by using friendly name

[claudiospizzi]

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

[johlju]

Suggest adding a new parameter FriendlyName and use the command Assert-BoundParameter so the resource throws and exception if both parameters are provided in the configuration. Thumbprint should no longer be required. But one of Thumbprint or FriendlyName should be passed otherwise the resource should also throw an exception. Assert-BoundParameter can be used for that verification too. ~~Though, saw that there were a bug in the docs, says the wrong command name for that parameter set.~~ Fixed.

[bozho]

I'd suggest adding CommonName parameter instead of FriendlyName. In some cases (e.g. Let's Encrypt), we may not control/know exact certificate's friendly name.

Alternatively, have the resource allow matching the friendly name against a pattern.

In case of multiple certificate matches (with either solution), use the certificate with the latest expiration date.

[claudiospizzi]

What about adding the FriendlyName as @johlju suggested but also the CommonName as @bozho mentioned. But for this the Assert-BoundParameter can't validate mutual exclusion of 3 parameters, right?

[johlju]

We should use only common name as @bozho suggests.

[russellhart]

This will also solve the issue of certificate auto-rotation. Instead of hard pinning the thumbprint the next installed certificate can be used as soon as it's available. As mentioned choose the latest ValidTo, if ValidFrom and ValidTo are current.