Backup-DbaDbCertificate and Restore-DbaDbCertificate issue with certificate naming

0x7FFFFFFFFFFFFFFF commented 6 months ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?


writeErrorStream      : True
PSMessageDetails      :
Exception             : System.Exception: A certificate with name '06w.test1.mydomain.com' already exists or this certificate already has been
                        added to the database. ---> Microsoft.Data.SqlClient.SqlException: A certificate with name '06w.test1.mydomain.com'
                        already exists or this certificate already has been added to the database.
                           at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject,
                        DataSet fillDataSet, Boolean catchException)
                           at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes
                        executionType, Boolean retry)
                           --- End of inner exception stack trace ---
TargetObject          : inst2
CategoryInfo          : NotSpecified: (inst2:String) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Restore-DbaDbCertificate,Stop-Function
ErrorDetails          : A certificate with name '06w.test1.mydomain.com' already exists or this certificate already has been added to the
                        database.
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, <No file>: line 97886
                        at Restore-DbaDbCertificate<Process>, <No file>: line 71407
                        at <ScriptBlock>, <No file>: line 12
PipelineIterationInfo : {0, 1}

Steps to Reproduce

I encountered an issue when using the Backup-DbaDbCertificate and Restore-DbaDbCertificate functions from the dbatools module to backup and restore certificates on SQL Server instances.

Steps to reproduce:

On the source instance mn-cls-06w.test1.mydomain.com, I executed the following commands to backup two certificates:

$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

The backup process generated four 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

On the target instance inst2, I executed the following command to restore the first certificate:

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

The first certificate was restored successfully, but the certificate name was set to 06w.test1.mydomain.com.

When I tried to restore the second certificate using the following command:

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

I received the following error:

WARNING: [11:51:19][Restore-DbaDbCertificate] Exception calling "Create" with "4" argument(s): "Create failed for  '06w.test1.mydomain.com'. " |
A certificate with name '06w.test1.mydomain.com' already exists or this certificate already has been added to the database.

Please confirm that you are running the most recent version of dbatools

2.1.11

Other details or mentions

I think the problem is that we cannot specify an exact file name when backing up a certificate, and it's very hard for the restore certificate command to correctly parse the correct certificate name from the certificate file name (it's also hard for us). If we can specify a certificate file name when backing up, then we know how to parse the certificate name from it, so that we can pass the certificate name to the Name parameter of the Restore-DbaDbCertificate function. Alternatively, if the dbatools module is smart enough, it can mark the certificate name in the file name, like mn-cls-06w.test1.mydomain.com-master-[[[cde_efg_20230314]]].cer, so that we know cde_efg_20230314 is the certificate name.

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version


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

SQL Server Edition and Build number

Microsoft SQL Server 2022 (RTM-CU10-GDR) (KB5033592) - 16.0.4100.1 (X64)
        Nov 15 2023 16:11:29
        Copyright (C) 2022 Microsoft Corporation
        Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

.NET Framework Version

PSChildName                      Version
-----------                      -------
v2.0.50727                       2.0.50727.4927
v3.0                             3.0.30729.4926
Windows Communication Foundation 3.0.4506.4926
Windows Presentation Foundation  3.0.6920.4902
v3.5                             3.5.30729.4926
Client                           4.8.03761
Full                             4.8.03761
Client                           4.0.0.0

andreasjordan commented 6 months ago

Looking at the code of Restore-DbaDbCertificate, the name of the certificate will be extracted from the filename - but only if you restore to the same instance where you took the backup.

Please try to change the filenames and replace the source instance name with the target instance name. Then it should work.

andreasjordan commented 4 months ago

Will close this now, but can reopen if needed.

dataplat / dbatools