DSCResources/MSFT_xDatabaseLogin/MSFT_xDatabaseLogin.psm1

data LocalizedData
{
    # culture="en-US"
    ConvertFrom-StringData @'
    CreateDatabaseLoginError=Failed to create SQL Login '{0}'.
    TestDatabaseLoginError=Failed to test SQL Login '{0}'.
    CreateDatabaseLoginSuccess=Success: SQL Login '{0}' either already existed or has been successfully created.
    RemoveDatabaseLoginError=Failed to remove SQL Login '{0}'.
    RemoveDatabaseLoginSuccess=Success: SQL Login '{0}' either does not existed or has been successfully removed.
'@

}

Import-Module $PSScriptRoot\..\xDatabase_Common

function Get-TargetResource #Not yet working
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        
        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure = "Present",
        
        [parameter(Mandatory = $true)]
        [System.String]
        $LoginName,

        [System.String]
        $LoginPassword,

        [System.Management.Automation.PSCredential]
        $SqlConnectionCredential,

        [parameter(Mandatory = $true)]
        [string]
        [ValidateSet("SQL","Windows")]
        $SqlAuthType,
        
        [parameter(Mandatory = $true)]
        [System.String]
        $SqlServer

    )

    if($SqlAuthType -eq "SQL")
    {
         $Connection = Construct-SqlConnection -credentials $SqlConnectionCredential -sqlServer $SqlServer
    }
    else
    {
         $Connection = Construct-SqlConnection -sqlServer $SqlServer
    }

    [string]$loginNameQuery = "SELECT * from sys.sql_logins where name='$LoginName'"

    $PresentValue = $false

    if((ReturnSqlQuery -sqlConnection $connection -SqlQuery $loginNameQuery)[0] -gt 0)
    {
        $PresentValue = $true
    }


    $returnValue = @{
        Ensure = $PresentValue
        LoginName = $LoginName
        AuthType = $SqlAuthType
        SqlServer = $SqlServer
    }

    $returnValue

}

#TODO: handle absent case. example "DROP Login Toothy"

function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure = "Present",

        [parameter(Mandatory = $true)]
        [System.String]
        $LoginName,

        [System.String]
        $LoginPassword,
        
        [System.Management.Automation.PSCredential]
        $SqlConnectionCredential,

        [parameter(Mandatory = $true)]
        [string]
        [ValidateSet("SQL","Windows")]
        $SqlAuthType,
        
        [parameter(Mandatory = $true)]
        [System.String]
        $SqlServer
    )
    
    if($SqlAuthType -eq "SQL")
    {
         $Connection = Construct-SqlConnection -credentials $SqlConnectionCredential -sqlServer $SqlServer
    }
    else
    {
         $Connection = Construct-SqlConnection -sqlServer $SqlServer
    }

    if($Ensure -eq "Present")
    {
        try
        {
            # Create login if it does not already exist.
            [string]$SqlQuery = "if not exists(SELECT name FROM sys.sql_logins WHERE name='$LoginName') Begin create login $LoginName with password='$LoginPassword' END"

            $supressReturn = ExecuteSqlQuery -sqlConnection $connection -SqlQuery $SqlQuery

            Write-Verbose $($LocalizedData.CreateDatabaseLoginSuccess -f ${LoginName})
        
        }
        catch
        {
            $errorId = "CreateDatabaseLogin";
            $errorCategory = [System.Management.Automation.ErrorCategory]::InvalidResult
            $errorMessage = $($LocalizedData.CreateDatabaseLoginError -f ${LoginName})
            $exception = New-Object System.InvalidOperationException $errorMessage 
            $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $errorCategory, $null

            $PSCmdlet.ThrowTerminatingError($errorRecord);
        }
    }
    else # Ensure is absent so remove login.
    {
        try
        {
            # Create login if it does not already exist.
            [string]$SqlQuery = "if exists(SELECT name FROM sys.sql_logins WHERE name='$LoginName') Begin DROP LOGIN $LoginName END"

            $supressReturn = ExecuteSqlQuery -sqlConnection $connection -SqlQuery $SqlQuery

            Write-Verbose $($LocalizedData.RemoveDatabaseLoginSuccess -f ${LoginName})
        }
        catch
        {
            $errorId = "RemoveDatabaseLogin";
            $errorCategory = [System.Management.Automation.ErrorCategory]::InvalidResult
            $errorMessage = $($LocalizedData.RemoveDatabaseLoginError -f ${LoginName})
            $exception = New-Object System.InvalidOperationException $errorMessage 
            $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $errorCategory, $null

            $PSCmdlet.ThrowTerminatingError($errorRecord);
        }
    }
}


function Test-TargetResource #Not yet working
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [ValidateSet("Present","Absent")]
        [System.String]
        $Ensure = "Present",

        [parameter(Mandatory = $true)]
        [System.String]
        $LoginName,

        [System.String]
        $LoginPassword,

        [System.String]
        $DefaultDatabaseName,

        [System.Management.Automation.PSCredential]
        $SqlConnectionCredential,

        [parameter(Mandatory = $true)]
        [string]
        [ValidateSet("SQL","Windows")]
        $SqlAuthType,
        
        [parameter(Mandatory = $true)]
        [System.String]
        $SqlServer
    )

        try
        {
            if($SqlAuthType -eq "SQL")
        {
                $Connection = Construct-SqlConnection -credentials $SqlConnectionCredential -sqlServer $SqlServer
        }
        else
        {
                $Connection = Construct-SqlConnection -sqlServer $SqlServer
        }
        
        [string]$SqlQuery = "SELECT * from sys.sql_logins where name='$LoginName'"
        
        $LoginsReturnedByQuery = (ReturnSqlQuery -sqlConnection $connection -SqlQuery $SqlQuery)[0]

        if((($LoginsReturnedByQuery -gt 0) -and ($Ensure -eq "Present")) -or (($LoginsReturnedByQuery -eq 0) -and ($Ensure -eq "absent")))
        {
            $result = $true
        }
        else
        {
            $result = $false
        }

        return $result

    }
    catch
    {
        $errorId = "TestDatabaseLogin";
        $errorCategory = [System.Management.Automation.ErrorCategory]::InvalidResult
        $errorMessage = $($LocalizedData.TestDatabaseLoginError -f ${LoginName})
        $exception = New-Object System.InvalidOperationException $errorMessage 
        $errorRecord = New-Object System.Management.Automation.ErrorRecord $exception, $errorId, $errorCategory, $null

        $PSCmdlet.ThrowTerminatingError($errorRecord);
    }

}


Export-ModuleMember -Function *-TargetResource