SqlDatabase: Get-TargetResource returns a collation even when the database does not exist

[johlju]

Details of the scenario you tried and the problem that is occurring

Get-TargetResource returns a collation even when the database does not exist. This is becuase in Get-TargetResource the collation is always first set to the collation of the instance.

https://github.com/PowerShell/SqlServerDsc/blob/2d37a930d957d2bbe148d118a5065a2f6f33d29e/DSCResources/MSFT_SqlDatabase/MSFT_SqlDatabase.psm1#L68-L79

Verbose logs showing the problem

The integration tests shows this problem.

    Context When using configuration MSFT_SqlDatabase_RemoveDatabase2_Config
The 'Microsoft.PowerShell.Management' module was not imported because the 'Microsoft.PowerShell.Management' snap-in was already imported.
VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer APPVYR-WIN with user sid S-1-5-21-1930758936-441951309-2831684283-1000.
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Set      ]
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Resource ]  [[SqlDatabase]Integration_Test]
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Test     ]  [[SqlDatabase]Integration_Test]
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Determines the state of the database 'Database2' on the instance 'DSCSQLTEST'.
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Get the state of the database 'Database2' on the instance 'DSCSQLTEST'.
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module. (SQLCOMMON0024)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Importing PowerShell module 'SQLPS' with version '1.0' from path 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1'. (SQLCOMMON0025)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Connecting using the credential 'APPVYR-WIN\SqlAdmin' and the login type 'Integrated'. (SQLCOMMON0054)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Connected to SQL instance 'APPVYR-WIN\DSCSQLTEST'. (SQLCOMMON0018)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] There is a database 'Database2' present, and has the collation 'SQL_Latin1_General_Pref_CP850_CI_AS'.
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Expected the database 'Database2' to absent, but it was present.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Test     ]  [[SqlDatabase]Integration_Test]  in 2.5650 seconds.
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Set      ]  [[SqlDatabase]Integration_Test]
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Found PowerShell module Sqlps already imported in the session. (SQLCOMMON0026)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Connecting using the credential 'APPVYR-WIN\SqlAdmin' and the login type 'Integrated'. (SQLCOMMON0054)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Connected to SQL instance 'APPVYR-WIN\DSCSQLTEST'. (SQLCOMMON0018)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Removing the database 'Database2'.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Set      ]  [[SqlDatabase]Integration_Test]  in 0.3090 seconds.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Resource ]  [[SqlDatabase]Integration_Test]
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Set      ]
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Set      ]    in  3.0410 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 3.081 seconds
      [+] Should compile and apply the MOF without throwing 3.54s
VERBOSE: An LCM method call arrived from computer APPVYR-WIN with user sid S-1-5-21-1930758936-441951309-2831684283-1000.
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Get      ]      
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Get the state of the database 'Database2' on the instance 'DSCSQLTEST'.
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module. (SQLCOMMON0024)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Importing PowerShell module 'SQLPS' with version '1.0' from path 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1'. (SQLCOMMON0025)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Connecting using the credential 'APPVYR-WIN\SqlAdmin' and the login type 'Integrated'. (SQLCOMMON0054)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Connected to SQL instance 'APPVYR-WIN\DSCSQLTEST'. (SQLCOMMON0018)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] It does not exist a database named 'Database2'.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Get      ]  [[SqlDatabase]Integration_Test]  in 2.8280 seconds.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Get      ]    in  2.9830 seconds.
      [+] Should be able to call Get-DscConfiguration without throwing 3.03s
      [-] Should have set the resource and all the parameters should match 286ms
        Expected $null or empty, but got Finnish_Swedish_CI_AS.
        185:                 $resourceCurrentState.Collation | Should -BeNullOrEmpty
        at <ScriptBlock>, C:\projects\sqlserverdsc\Tests\Integration\MSFT_SqlDatabase.Integration.Tests.ps1: line 185
VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = TestConfiguration,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer APPVYR-WIN with user sid S-1-5-21-1930758936-441951309-2831684283-1000.
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Test     ]
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Resource ]  [[SqlDatabase]Integration_Test]
VERBOSE: [APPVYR-WIN]: LCM:  [ Start  Test     ]  [[SqlDatabase]Integration_Test]
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Determines the state of the database 'Database2' on the instance 'DSCSQLTEST'.
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Get the state of the database 'Database2' on the instance 'DSCSQLTEST'.
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Information: PowerShell module SqlServer not found, trying to use older SQLPS module. (SQLCOMMON0024)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Importing PowerShell module 'SQLPS' with version '1.0' from path 'C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1'. (SQLCOMMON0025)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Connecting using the credential 'APPVYR-WIN\SqlAdmin' and the login type 'Integrated'. (SQLCOMMON0054)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] Connected to SQL instance 'APPVYR-WIN\DSCSQLTEST'. (SQLCOMMON0018)
VERBOSE: [APPVYR-WIN]:                            [[SqlDatabase]Integration_Test] It does not exist a database named 'Database2'.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Test     ]  [[SqlDatabase]Integration_Test] True in 2.8080 seconds.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Resource ]  [[SqlDatabase]Integration_Test]
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Test     ]     Completed processing test operation. The operation returned True.
VERBOSE: [APPVYR-WIN]: LCM:  [ End    Test     ]    in  2.9620 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 3 seconds
      [+] Should return $true when Test-DscConfiguration is run 3.01s

Suggested solution to the issue

Remove the line

https://github.com/PowerShell/SqlServerDsc/blob/2d37a930d957d2bbe148d118a5065a2f6f33d29e/DSCResources/MSFT_SqlDatabase/MSFT_SqlDatabase.psm1#L71

The DSC configuration that is used to reproduce the issue (as detailed as possible)

Configuration MSFT_SqlDatabase_RemoveDatabaseUser4_Config
{
    Import-DscResource -ModuleName 'SqlServerDsc'

    node $AllNodes.NodeName
    {
        SqlDatabaseUser 'Integration_Test'
        {
            Ensure       = 'Absent'
            ServerName   = $Node.ServerName
            InstanceName = $Node.InstanceName
            DatabaseName = $Node.DatabaseName
            Name         = $Node.User4_Name

            PsDscRunAsCredential = New-Object `
                -TypeName System.Management.Automation.PSCredential `
                -ArgumentList @($Node.Username, (ConvertTo-SecureString -String $Node.Password -AsPlainText -Force))
        }
    }
}

SQL Server edition and version the target node is running

n/a

SQL Server PowerShell modules present on the target node

n/a

The operating system the target node is running

n/a

Version and build of PowerShell the target node is running

n/a

Version of the DSC module that was used ('dev' if using current dev branch)

dev