Backup-DbaDbMasterKey tries to create a backup file with invalid characters

[rgl]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

I received no errors, which is completely unexpected.

Instead, it tried to create the backup using a file named NP:.$SQLSERVER-master-masterkey which has an invalid character (colon).

Steps to Reproduce

$sqlInstance = Connect-DbaInstance `
    -SqlInstance .\SQLSERVER `
    -AppendConnectionString "HostNameInCertificate=db.test"
Backup-DbaDbMasterKey `
    -SqlInstance $sqlInstance `
    -SecurePassword $securePassword

Which outputted:

ComputerName : test
InstanceName : SQLSERVER
SqlInstance  : test\SQLSERVER
Database     : master
Path         : D:\SQLServer\Data\MSSQL16.SQLSERVER\MSSQL\Backup\NP:.$SQLSERVER-master-masterkey
Status       : Success

But listing the resulting file, we can see a zero byte length file, with a partial name NP (instead of the invalid filename NP:.$SQLSERVER-master-masterkey):

dir D:\SQLServer\Data\MSSQL16.SQLSERVER\MSSQL\Backup

    Directory: D:\SQLServer\Data\MSSQL16.SQLSERVER\MSSQL\Backup

Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----       19/07/2023     12:34              0 NP

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

2.0.4

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe)

PowerShell Host Version

Name                           Value
----                           -----
PSVersion                      5.1.17763.3770
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.3770
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) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

.NET Framework Version

PSChildName Version
----------- -------
Client      4.8.03761
Full        4.8.03761
Client      4.0.0.0

[andreasjordan]

Can you try to not use named pipes but TCP/IP? Using -SqlInstance $env:COMPUTERNAME\SQLSERVER should help.

[rgl]

@andreasjordan, maybe I'm missing something, but what is the difference between .\SQLSERVER and $env:COMPUTERNAME\SQLSERVER when running in the same machine? Using that syntax, how do you deduce the used protocol?

[andreasjordan]

That is part of the used datatype for that parameter. It is a custom type with a lot of functionallity. Source code: https://github.com/dataplat/dbatools.library/blob/main/project/dbatools/Parameter/DbaInstanceParameter.cs

[rgl]

Thanks for pointing that C# class, it cleared things up!

And Indeed, using $env:COMPUTERNAME\SQLSERVER worked around the problem. It now correctly creates the backup files. Thank You!

Despite the workaround, I think this is a bug, and it should have saved the files or raised an exception.

FWIW, I wasn't expecting a difference between .\SQLSERVER and $env:COMPUTERNAME\SQLSERVER, as they should be the same when we connect from the same machine where SQL Server is installed on.

[andreasjordan]

I agree that .\SQLSERVER would be a nice shortcut. Maybe @FriedrichWeinmann can give more background information.

The "impossible" filename when using named pipes is indeed a bug so I will let this issue open and try to find some time to implement a fix for that.

[andreasjordan]

@FriedrichWeinmann - as you are currently working on the library: Can you have alook at this issue?

[andreasjordan]

Let me ping you again on this one, @FriedrichWeinmann ...