Backup-DbaDbCertificate - Support Custom Backup File Name

[serenefiresiren]

Summarize Functionality

SQL Command allows specifying the file name when backing up the service master key. Backup-DbaDbCertificate currently automatically sets the filename. It would be nice to have the option here as well.

Relates to #9483 , #9484

Is there a command that is similiar or close to what you are looking for?

Yes

Technical Details

SQL

USE [YourDB];   
BACKUP CERTIFICATE YourCertName vTO 
FILE = 'C:\temp\CertificateFilename.cer'
WITH PRIVATE KEY (
			FILE = 'C:\temp\CertificatePriveKeyFilename.pvk'
			,ENCRYPTION BY PASSWORD = 'NewPassword');

Existing File name command, Line 167, 178

$fullCertName = Join-DbaPath -SqlInstance $server -Path $actualPath -ChildPath "$fileinstance-$dbname-$certName$Suffix"
 $exportPathKey = "$fullCertName.pvk"

Suggested change adding an option Filename parameter. Potentially add both a filename param for private key file name and certificate key file name.

param (
        [parameter(Mandatory, ParameterSetName = "instance")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [parameter(ParameterSetName = "instance")]
        [object[]]$Certificate,
        [parameter(ParameterSetName = "instance")]
        [object[]]$Database,
        [parameter(ParameterSetName = "instance")]
        [object[]]$ExcludeDatabase,
        [Security.SecureString]$EncryptionPassword,
        [Security.SecureString]$DecryptionPassword,
        [System.IO.FileInfo]$Path,
       **[string]$CertChildPath,
            [Alias("CertificateFileName")]
            [string]$PvkChildPath,
            [Alias("PrivateKeyFileName")]**
        [string]$Suffix,
        [parameter(ValueFromPipeline, ParameterSetName = "collection")]
        [Microsoft.SqlServer.Management.Smo.Certificate[]]$InputObject,
        [switch]$EnableException 
    )
#
#
#
# pseudo
$CertificateChildPath = $CerChildPath or if null then "$fileinstance-$dbname-$certName$Suffix"
$fullCertName = Join-DbaPath -SqlInstance $server -Path $actualPath -ChildPath $CertificateChildPath 

$exportPathCert = $fullCertName + (If right 4 <> '.cer' Then ".cer' )
$exportPathKey =  $PvkFileName + (If Right 4 <> '.pvk' then '.pvk') or if null then "$fullCertName.pvk" 

[wsmelton]

Is there any difference to you just running Rename-Item after the command?

[0x7FFFFFFFFFFFFFFF]

While using Rename-Item after the command is a possible workaround, integrating custom filename support directly into Backup-DbaDbCertificate makes more sense for consistency and usability reasons.

Backup-DbaDatabase, another backup command in the same module, allows users to specify a custom backup file name. It would be logical and consistent for Backup-DbaDbCertificate to offer the same capability. This alignment in functionality between similar commands within the dbatools module would provide a more intuitive and consistent user experience.

[wsmelton]

Backup of a databases is the most common thing a DBA does for automation and something that is done frequently, along with pipeline the objects and querying for the given file name is possible with database backups. That is not a comparable command or functionality to backing up other types available in SQL Server because they are done as one-offs, or very infrequently (e.g., you create a cert key and back it up you are generally not going to backup it up again).

Our module puts more consideration into ensure that database backup scenarios are covered that may occur in the wild. Repeating this though in every command possible adds more code and logic for us to maintain testing with to ensure functionality works across versions of both SQL Server and PowerShell. While it is only a few lines per command, that adds up. Particularly because a one-line command in PowerShell can be used to rename the item however a user might want is available I'm not in favor of adding this parameter support.

[0x7FFFFFFFFFFFFFFF]

The issue with the current implementation of Backup-DbaDbCertificate is more nuanced than simply not allowing custom filenames. The problem lies in how the default naming convention obfuscates the original certificate name, which can lead to difficulties during restoration (most of the time when we are restoring certificates, we want to keep their original names).

For example, if we run:

$inst = "mn-cls-06w.test1.mydomain.com"
$cred = New-Object System.Management.Automation.PSCredential -ArgumentList "test", ("cerasdfasdfsad234)_*&sdf4l5" | ConvertTo-SecureString -AsPlainText -Force)
Backup-DbaDbCertificate -SqlInstance $inst -Certificate "abc_cde_20230314" -Path c:\temp -EncryptionPassword $cred.Password -Confirm:$false
Backup-DbaDbCertificate -SqlInstance $inst -Certificate "cde_efg_20230314" -Path c:\temp -EncryptionPassword $cred.Password -Confirm:$false

We will get these files:

mn-cls-06w.test1.mydomain.com-master-abc_cde_20230314.cer
mn-cls-06w.test1.mydomain.com-master-abc_cde_20230314.pvk
mn-cls-06w.test1.mydomain.com-master-cde_efg_20230314.cer
mn-cls-06w.test1.mydomain.com-master-cde_efg_20230314.pvk

The original certificate name is embedded within a longer string that includes the server name, database name and certificate name. This makes it difficult to reliably identify the certificate by its original name. Considering when restoring certificates, people often want to maintain the original certificate name. The current naming scheme doesn't facilitate this easily.