tests/New-DbaLogin.Tests.ps1

$CommandName = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1","")
Write-Host -Object "Running $PSCommandpath" -ForegroundColor Cyan
. "$PSScriptRoot\constants.ps1"
. "$PSScriptRoot\..\internal\Connect-SqlInstance.ps1"
. "$PSScriptRoot\..\internal\Get-PasswordHash.ps1"
. "$PSScriptRoot\..\internal\Convert-HexStringToByte"

Describe "$commandname Integration Tests" -Tags "IntegrationTests" {
        
    $credLogin = 'credologino'
    $certificateName = 'DBAToolsPesterlogincertificate'
    $password = 'MyV3ry$ecur3P@ssw0rd'
    $securePassword = ConvertTo-SecureString $password -AsPlainText -Force
    $sid = '0xDBA700131337C0D30123456789ABCDEF'
    $server1 = Connect-SqlInstance -SqlInstance $script:instance1
    $server2 = Connect-SqlInstance -SqlInstance $script:instance2
    $servers = @($server1, $server2)
    $computerName = $server1.NetName
    $winLogin = "$computerName\$credLogin"
    $logins = "claudio", "port", "tester", "certifico", $winLogin
    
    #cleanup
    foreach ($instance in $servers) {
        foreach ($login in $logins) {
            if ($l = Get-DbaLogin -SqlInstance $instance -Login $login) {
                $results = $instance.Query("IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '$login') EXEC sp_who '$login'")
                foreach ($spid in $results.spid) {
                    $null = $instance.Query("kill $spid")
                }
                if ($c = $l.EnumCredentials()) {
                    $l.DropCredential($c)
                }
                $l.Drop()
            }
        }
    }
    
    #create Windows login
    $computer = [ADSI]"WinNT://$computerName"
    try {
        $user = [ADSI]"WinNT://$computerName/$credLogin,user"
        if ($user.Name -eq $credLogin) {
            $computer.Delete('User',$credLogin)
        }
    }
    catch {<#User does not exist#>}
    
    $user = $computer.Create("user", $credLogin)
    $user.SetPassword($password)
    $user.SetInfo()
    
    #create credential
    $null = New-DbaCredential -SqlInstance $server1 -Name $credLogin -CredentialIdentity $credLogin -Password $securePassword -Force
    
    #create master key if not exists
    if (!($mkey = Get-DbaDatabaseMasterKey -SqlInstance $server1 -Database master)) {
        $null = New-DbaDatabaseMasterKey -SqlInstance $server1 -Database master -Password $securePassword -Confirm:$false
    }
    
    #create certificate
    if ($crt = $server1.Databases['master'].Certificates[$certificateName]) {
        $crt.Drop()
    }
    $null = New-DbaDatabaseCertificate $server1 -Name $certificateName -Password $null

    Context "Create new logins" {
        It "Should be created successfully - Hashed password" {
            $results = New-DbaLogin -SqlInstance $server1 -Login tester -HashedPassword (Get-PasswordHash $securePassword $server1.VersionMajor)
            $results.Name | Should Be "tester"
            $results.DefaultDatabase | Should be 'master'
            $results.IsDisabled | Should be $false
            $results.PasswordExpirationEnabled | Should be $false
            $results.PasswordPolicyEnforced | Should be $false
            $results.LoginType | Should be 'SqlLogin'
        }
        It "Should be created successfully - password, credential and a custom sid " {
            $results = New-DbaLogin -SqlInstance $server1 -Login claudio -Password $securePassword -Sid $sid -MapToCredential $credLogin
            $results.Name | Should Be "claudio"
            $results.EnumCredentials() | Should be $credLogin
            $results.DefaultDatabase | Should be 'master'
            $results.IsDisabled | Should be $false
            $results.PasswordExpirationEnabled | Should be $false
            $results.PasswordPolicyEnforced | Should be $false
            $results.Sid | Should be (Convert-HexStringToByte $sid)
            $results.LoginType | Should be 'SqlLogin'
        }
        It "Should be created successfully - password and all the flags" {
            $results = New-DbaLogin -SqlInstance $server1 -Login port -Password $securePassword -PasswordPolicy -PasswordExpiration -DefaultDatabase tempdb -Disabled -Language Nederlands
            $results.Name | Should Be "port"
            $results.Language | Should Be 'Nederlands'
            $results.EnumCredentials() | Should be $null
            $results.DefaultDatabase | Should be 'tempdb'
            $results.IsDisabled | Should be $true
            $results.PasswordExpirationEnabled | Should be $true
            $results.PasswordPolicyEnforced | Should be $true
            $results.LoginType | Should be 'SqlLogin'
        }
        It "Should be created successfully - Windows login" {
            $results = New-DbaLogin -SqlInstance $server1 -Login $winLogin
            $results.Name | Should Be "$winLogin"
            $results.DefaultDatabase | Should be 'master'
            $results.IsDisabled | Should be $false
            $results.LoginType | Should be 'WindowsUser'
        }
        It "Should be created successfully - certificate" {
            $results = New-DbaLogin -SqlInstance $server1 -Login certifico -MapToCertificate $certificateName
            $results.Name | Should Be "certifico"
            $results.DefaultDatabase | Should be 'master'
            $results.IsDisabled | Should be $false
            $results.LoginType | Should be 'Certificate'
        }

        It "Should be copied successfully" {
            $results = Get-DbaLogin -SqlInstance $server1 -Login tester | New-DbaLogin -SqlInstance $server2 -Disabled:$false -Force
            $results.Name | Should Be "tester"
            
            $results = Get-DbaLogin -SqlInstance $server1 -Login claudio,port | New-DbaLogin -SqlInstance $server2 -Force -PasswordPolicy -PasswordExpiration -DefaultDatabase tempdb -Disabled -Language Nederlands -NewSid -LoginRenameHashtable @{claudio = 'port'; port = 'claudio'} -MapToCredential $null
            $results.Name | Should Be @("port", "claudio")
            
            $results = Get-DbaLogin -SqlInstance $server1 -Login tester | New-DbaLogin -SqlInstance $server1 -LoginRenameHashtable @{tester = 'port'} -Force -NewSid
            $results.Name | Should Be "port"
        }
        
        It "Should retain its same properties" {
            
            $login1 = Get-Dbalogin -SqlInstance $script:instance1 -login tester
            $login2 = Get-Dbalogin -SqlInstance $script:instance2 -login tester
            
            $login2 | Should Not BeNullOrEmpty
            
            # Compare values
            $login1.Name | Should Be $login2.Name
            $login1.Language | Should Be $login2.Language
            $login1.EnumCredentials() | Should be $login2.EnumCredentials()
            $login1.DefaultDatabase | Should be $login2.DefaultDatabase
            $login1.IsDisabled | Should be $login2.IsDisabled
            $login1.PasswordExpirationEnabled | Should be $login2.PasswordExpirationEnabled
            $login1.PasswordPolicyEnforced | Should be $login2.PasswordPolicyEnforced
            $login1.Sid | Should be $login2.Sid
        }
        
        It "Should not have same properties because of the overrides" {
            
            $login1 = Get-Dbalogin -SqlInstance $script:instance1 -login claudio
            $login2 = Get-Dbalogin -SqlInstance $script:instance2 -login port
            
            $login2 | Should Not BeNullOrEmpty
            
            # Compare values
            $login1.Language | Should Not Be $login2.Language
            $login1.EnumCredentials() | Should Not Be $login2.EnumCredentials()
            $login1.DefaultDatabase | Should Not be $login2.DefaultDatabase
            $login1.IsDisabled | Should Not be $login2.IsDisabled
            $login1.PasswordExpirationEnabled | Should Not be $login2.PasswordExpirationEnabled
            $login1.PasswordPolicyEnforced | Should Not be $login2.PasswordPolicyEnforced
            $login1.Sid | Should Not be $login2.Sid
        }
    }
    Context "Connect with a new login" {
        It "Should login with newly created Sql Login, get instance name and kill the process" {
            $cred = New-Object System.Management.Automation.PSCredential ("tester", $securePassword)
            $s = Connect-SqlInstance -SqlInstance $script:instance1 -SqlCredential $cred
            $s.Name | Should Be $script:instance1
            $results = $server1.Query("IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '$($cred.UserName)') EXEC sp_who '$($cred.UserName)'")
            $results | Should Not BeNullOrEmpty
            foreach ($spid in $results.spid) {
                { Invoke-SqlCmd2 -ServerInstance $script:instance1 -Query "kill $spid" -ErrorAction Stop} | Should Not Throw
            }
        }
    }
    
    Context "No overwrite" {
        $null = Get-DbaLogin -SqlInstance $server1 -Login tester | New-DbaLogin -SqlInstance $server2 -WarningVariable warning 3>&1
        It "Should not attempt overwrite" {
            $warning | Should Match "Login tester already exists"
        }
    }
    
    foreach ($instance in $servers) {
        foreach ($login in $logins) {
            if ($l = Get-DbaLogin -SqlInstance $instance -Login $login) {
                $results = $instance.Query("IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '$login') EXEC sp_who '$login'")
                foreach ($spid in $results.spid) {
                    $null = $instance.Query("kill $spid")
                }
                if ($c = $l.EnumCredentials()) {
                    $l.DropCredential($c)
                }
                $l.Drop()
            }
        }
    }

    $computer.Delete('User',$credLogin) 
    $server1.Credentials[$credLogin].Drop()
    $server1.Databases['master'].Certificates[$certificateName].Drop()
    if (!$mkey) {
        $null = Remove-DbaDatabaseMasterKey -SqlInstance $script:instance1 -Database master -Confirm:$false
    }
}