SqlLogin: Get-TargetResource "thinks" login exists, when it doesn't.

[arcotek-ltd]

Details of the scenario you tried and the problem that is occurring:
Using xSQLServerLogin thus:

xSQLServerLogin SomeUser
        {
            Name = "SomeUser" 
            LoginType = "WindowsUser"
            SQLServer = $env:ComputerName
            SQLInstanceName = $Node.SqlInstanceName
        }

DSC reports the user is present:

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 MYAZURESERVER with user sid S-1-5-21-23454367-3675323-456572457-4353.
VERBOSE: [MYAZURESERVER]: LCM:  [ Start  Set      ]
VERBOSE: [MYAZURESERVER]: LCM:  [ Start  Resource ]  [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase]
VERBOSE: [MYAZURESERVER]: LCM:  [ Start  Test     ]  [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase]
VERBOSE: [MYAZURESERVER]:                            [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase] Connected to SQL instance 'MYAZURESERVER'.
VERBOSE: [MYAZURESERVER]:                            [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase] Getting SQL logins...
VERBOSE: [MYAZURESERVER]:                            [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase] 2017-09-06_10-01-02: Getting the login 'MyAdDomain\SomeUser' from 'MYAZURESERVER\MSSQLSERVER'
VERBOSE: [MYAZURESERVER]:                            [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase] login: [MyAdDomain\SomeUser].
VERBOSE: [MYAZURESERVER]:                            [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase] 2017-09-06_10-01-02: The login 'MyAdDomain\SomeUser' is Present from the 'MYAZURESERVER\MSSQLSERVE
R' instance.
VERBOSE: [MYAZURESERVER]: LCM:  [ End    Test     ]  [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase]  in 0.1640 seconds.
VERBOSE: [MYAZURESERVER]: LCM:  [ Skip   Set      ]  [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase]
VERBOSE: [MYAZURESERVER]: LCM:  [ End    Resource ]  [[xSQLServerLogin]SomeUser::[hSqlConfig]MySqlDatabase]
VERBOSE: [MYAZURESERVER]: LCM:  [ End    Set      ]
VERBOSE: [MYAZURESERVER]: LCM:  [ End    Set      ]    in  0.4620 seconds.
VERBOSE: Operation 'Invoke CimMethod' complete.
VERBOSE: Time taken for configuration job to complete is 0.518 seconds

But the user is not and if I run Get-TargetResource, it reports correctly that it is not present:

PS C:\Users\paul> $name
MyAdDomain\SomeUser

PS C:\Users\paul> $SqlServer
MYAZURESERVER

PS C:\Users\paul> $sqlinstancename
MSSQLSERVER

PS C:\Users\paul> Get-TargetResource -Name $name -SQLServer $SqlServer -SQLInstanceName $sqlinstancename
VERBOSE: 
VERBOSE: 2017-09-06_10-12-22: Getting the login 'MyAdDomain\SomeUser' from 'MYAZURESERVER\MSSQLSERVER'
VERBOSE: 2017-09-06_10-12-22: The login 'MyAdDomain\SomeUser' is Absent from the 'MYAZURESERVER\MSSQLSERVER' instance.

Name                           Value                                                                                                                                                                                            
----                           -----                                                                                                                                                                                            
Name                           MyAdDomain\SomeUser                                                                                                                                                                       
Ensure                         Absent                                                                                                                                                                                           
LoginType                                                                                                                                                                                                                       
SQLServer                      MYAZURESERVER                                                                                                                                                                                        
SQLInstanceName                MSSQLSERVER                                                                                                                                                                                      
Disabled                                      

If I call your function Connect-SQL directly, you can see there are only two users:

PS C:\Users\paul> $a = Connect-SQL
VERBOSE: 

PS C:\Users\arw> $a.logins | Select Name

Name                  
----                  
Builtin\Administrators
sa      

The same result if I run SELECT * from master.sys.server_principals from SSMS. (There are SERVER_ROLE types returned, but not "SomeUser").

The DSC configuration that is using the resource (as detailed as possible):
I've reduced it down to just the above and I'm still seeing the same result.

The whole configuration. Can't get any more simple:

Configuration TestSQLLogin
{
    Import-DscResource -ModuleName xSqlServer

    Node "MYAZURESERVE"
    {
        xSQLServerLogin SomeUser
        {
            Name = "myaddomain\SomeUser" 
            LoginType = "WindowsUser"
            SQLServer = "MYAZURESERVER" 
            SQLInstanceName = "MSSQLSERVER"
        }
    }
}

TestSQLLogin -OutputPath "C:\Configurations\TestSQLLogin" -Verbose

Start-DscConfiguration -Path "C:\Configurations\TestSQLLogin" -Wait -Force -Verbose -ComputerName "localhost"

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
Windows Server 2012 R2, SQL Server 2016,

PS C:\Users\paul> $PSVersionTable.PSVersion

Major  Minor  Build  Revision
-----  -----  -----  --------
5      1      14409  1005  

What module (SqlServer or SQLPS) and which version of the module the DSC Target Node is running:

PS C:\Users\paul> Get-Module -ListAvailable -Name SQLPS

    Directory: C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules

ModuleType Version    Name                                ExportedCommands                                                                                                                                                      
---------- -------    ----                                ----------------                                                                                                                                                      
Manifest   1.0        SQLPS                               {Backup-SqlDatabase, Save-SqlMigrationReport, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp...}     


PS C:\Users\paul> Get-Module -ListAvailable -Name SQLASCMDLETS

    Directory: C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules

ModuleType Version    Name                                ExportedCommands                                                                                                                                                      
---------- -------    ----                                ----------------                                                                                                                                                      
Manifest   2.0        SQLASCMDLETS                        {Add-RoleMember, Backup-ASDatabase, Invoke-ASCmd, Invoke-ProcessCube...}  

No SqlServer module on this server.

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
8.1.0.0 Master, except I removed all the resources with very long names so it works!

Happy to demo this issue to any MS employee.

[johlju]

Thanks for reporting this and thanks for the detailed error report! I see the logic is flawed in the function Test-TargetResource.

It never sets $testPassed to $false if Get-TargetResource does not return anything. Seems the unit tests didn't catch this one either.

https://github.com/PowerShell/xSQLServer/blob/63c2b526dacf41e3eac092cab8a88b57c2e0d36e/DSCResources/MSFT_xSQLServerLogin/MSFT_xSQLServerLogin.psm1#L367-L376

If nobody beats me to it I will look into fixing this issue.

[SphenicPaul]

This issue is also in respect of the old, xSqlServer module (rather than the SqlServerDsc module) so might also be less relevant/applicable now.

Additionally, as per comments in PR #1652, the Get-TargetResource should always return a value (a hashtable) and there are number of existing, integration tests (and more being added as part of the PR) that confirm SQL Logins are being added to (and updated on) a SQL instance as expected.

Can this issue be closed?

[johlju]

Closing this, if this is still an issue please reopen or comment and we reopen.