SqlServerDsc

14.2.2-preview0002

DSCResources/DSC_SqlLogin/DSC_SqlLogin.psm1

$script:sqlServerDscHelperModulePath = Join-Path -Path $PSScriptRoot -ChildPath '..\..\Modules\SqlServerDsc.Common'
$script:resourceHelperModulePath = Join-Path -Path $PSScriptRoot -ChildPath '..\..\Modules\DscResource.Common'

Import-Module -Name $script:sqlServerDscHelperModulePath
Import-Module -Name $script:resourceHelperModulePath

$script:localizedData = Get-LocalizedData -DefaultUICulture 'en-US'

<#
    .SYNOPSIS
        Gets the specified login by name.
 
    .PARAMETER Name
        The name of the login to retrieve.
 
    .PARAMETER ServerName
        Hostname of the SQL Server to retrieve the login from. Default value is $env:COMPUTERNAME.
 
    .PARAMETER InstanceName
        Name of the SQL instance to retrieve the login from.
#>
function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [Parameter(Mandatory = $true)]
        [System.String]
        $Name,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $ServerName = $env:COMPUTERNAME,

        [Parameter(Mandatory = $true)]
        [System.String]
        $InstanceName
    )

    Write-Verbose -Message (
        $script:localizedData.GetLogin -f $Name, $ServerName, $InstanceName
    )

    $serverObject = Connect-SQL -ServerName $ServerName -InstanceName $InstanceName
    if ($serverObject)
    {
        $login = $serverObject.Logins[$Name]

        if ($login)
        {
            $ensure = 'Present'
        }
        else
        {
            $ensure = 'Absent'
        }
    }

    Write-Verbose -Message (
        $script:localizedData.LoginCurrentState -f $Name, $ensure, $ServerName, $InstanceName
    )

    $returnValue = @{
        Ensure          = $ensure
        Name            = $Name
        LoginType       = $login.LoginType
        ServerName      = $ServerName
        InstanceName    = $InstanceName
        Disabled        = $login.IsDisabled
        DefaultDatabase = $login.DefaultDatabase
    }

    if ($login.LoginType -eq 'SqlLogin')
    {
        $returnValue.Add('LoginMustChangePassword', $login.MustChangePassword)
        $returnValue.Add('LoginPasswordExpirationEnabled', $login.PasswordExpirationEnabled)
        $returnValue.Add('LoginPasswordPolicyEnforced', $login.PasswordPolicyEnforced)
    }

    return $returnValue
}

<#
    .SYNOPSIS
        Creates a login.
 
    .PARAMETER Ensure
        Specifies if the login to exist. Default is 'Present'.
 
    .PARAMETER Name
        The name of the login to retrieve.
 
    .PARAMETER LoginType
        The type of login to create. Default is 'WindowsUser'
 
    .PARAMETER ServerName
        Hostname of the SQL Server to create the login on. Default value is $env:COMPUTERNAME.
 
    .PARAMETER InstanceName
        Name of the SQL instance to create the login on.
 
    .PARAMETER LoginCredential
        The credential containing the password for a SQL Login. Only applies if the login type is SqlLogin.
 
    .PARAMETER LoginMustChangePassword
        Specifies if the login is required to have its password change on the next login. Only applies to SQL Logins. Default is $true.
 
    .PARAMETER LoginPasswordExpirationEnabled
        Specifies if the login password is required to expire in accordance to the operating system security policy. Only applies to SQL Logins. Default is $true.
 
    .PARAMETER LoginPasswordPolicyEnforced
        Specifies if the login password is required to conform to the password policy specified in the system security policy. Only applies to SQL Logins. Default is $true.
 
    .PARAMETER Disabled
        Specifies if the login is disabled. Default is $false.
 
    .PARAMETER DefaultDatabase
        Specifies the default database for the login.
#>
function Set-TargetResource
{
    [CmdletBinding()]
    param
    (
        [Parameter()]
        [ValidateSet('Present', 'Absent')]
        [System.String]
        $Ensure = 'Present',

        [Parameter(Mandatory = $true)]
        [System.String]
        $Name,

        [Parameter()]
        [ValidateSet(
            'WindowsUser',
            'WindowsGroup',
            'SqlLogin',
            'Certificate',
            'AsymmetricKey',
            'ExternalUser',
            'ExternalGroup'
        )]
        [System.String]
        $LoginType = 'WindowsUser',

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $ServerName = $env:COMPUTERNAME,

        [Parameter(Mandatory = $true)]
        [System.String]
        $InstanceName,

        [Parameter()]
        [System.Management.Automation.PSCredential]
        $LoginCredential,

        [Parameter()]
        [System.Boolean]
        $LoginMustChangePassword = $true,

        [Parameter()]
        [System.Boolean]
        $LoginPasswordExpirationEnabled = $true,

        [Parameter()]
        [System.Boolean]
        $LoginPasswordPolicyEnforced = $true,

        [Parameter()]
        [System.Boolean]
        $Disabled,

        [Parameter()]
        [System.String]
        $DefaultDatabase
    )

    $serverObject = Connect-SQL -ServerName $ServerName -InstanceName $InstanceName

    switch ( $Ensure )
    {
        'Present'
        {
            if ( $serverObject.Logins[$Name] )
            {
                $login = $serverObject.Logins[$Name]

                if ( $login.LoginType -eq 'SqlLogin' )
                {
                    if ( $login.PasswordExpirationEnabled -ne $LoginPasswordExpirationEnabled )
                    {
                        Write-Verbose -Message (
                            $script:localizedData.SetPasswordExpirationEnabled -f $LoginPasswordExpirationEnabled, $Name, $ServerName, $InstanceName
                        )

                        $login.PasswordExpirationEnabled = $LoginPasswordExpirationEnabled
                        Update-SQLServerLogin -Login $login
                    }

                    if ( $login.PasswordPolicyEnforced -ne $LoginPasswordPolicyEnforced )
                    {
                        Write-Verbose -Message (
                            $script:localizedData.SetPasswordPolicyEnforced -f $LoginPasswordPolicyEnforced, $Name, $ServerName, $InstanceName
                        )

                        $login.PasswordPolicyEnforced = $LoginPasswordPolicyEnforced
                        Update-SQLServerLogin -Login $login
                    }

                    # Set the password if it is specified
                    if ( $LoginCredential )
                    {
                        Write-Verbose -Message (
                            $script:localizedData.SetPassword -f $Name, $ServerName, $InstanceName
                        )

                        Set-SQLServerLoginPassword -Login $login -SecureString $LoginCredential.Password
                    }
                }

                if ( $PSBoundParameters.ContainsKey('Disabled') -and ($login.IsDisabled -ne $Disabled) )
                {
                    if ( $Disabled )
                    {
                        Write-Verbose -Message (
                            $script:localizedData.SetDisabled -f $Name, $ServerName, $InstanceName
                        )

                        $login.Disable()
                    }
                    else
                    {
                        Write-Verbose -Message (
                            $script:localizedData.SetEnabled -f $Name, $ServerName, $InstanceName
                        )

                        $login.Enable()
                    }
                }

                if ( $PSBoundParameters.ContainsKey('DefaultDatabase') -and ($login.DefaultDatabase -ne $DefaultDatabase) )
                {
                    $login.DefaultDatabase = $DefaultDatabase
                    Update-SQLServerLogin -Login $login
                }
            }
            else
            {
                # Some login types need additional work. These will need to be fleshed out more in the future
                if ( @('Certificate', 'AsymmetricKey', 'ExternalUser', 'ExternalGroup') -contains $LoginType )
                {
                    $errorMessage = $script:localizedData.LoginTypeNotImplemented -f $LoginType
                    New-NotImplementedException -Message $errorMessage
                }

                if ( ( $LoginType -eq 'SqlLogin' ) -and ( -not $LoginCredential ) )
                {
                    $errorMessage = $script:localizedData.LoginCredentialNotFound -f $Name
                    New-ObjectNotFoundException -Message $errorMessage
                }

                Write-Verbose -Message (
                    $script:localizedData.CreateLogin -f $Name, $LoginType, $ServerName, $InstanceName
                )

                $login = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Login' -ArgumentList $serverObject, $Name
                $login.LoginType = $LoginType

                switch ($LoginType)
                {
                    'SqlLogin'
                    {
                        # Verify the instance is in Mixed authentication mode
                        if ( $serverObject.LoginMode -notmatch 'Mixed|Normal' )
                        {
                            $errorMessage = $script:localizedData.IncorrectLoginMode -f $ServerName, $InstanceName, $serverObject.LoginMode
                            New-InvalidOperationException -Message $errorMessage
                        }

                        $login.PasswordPolicyEnforced = $LoginPasswordPolicyEnforced
                        $login.PasswordExpirationEnabled = $LoginPasswordExpirationEnabled
                        if ( $LoginMustChangePassword )
                        {
                            $LoginCreateOptions = [Microsoft.SqlServer.Management.Smo.LoginCreateOptions]::MustChange
                        }
                        else
                        {
                            $LoginCreateOptions = [Microsoft.SqlServer.Management.Smo.LoginCreateOptions]::None
                        }

                        New-SQLServerLogin -Login $login -LoginCreateOptions $LoginCreateOptions -SecureString $LoginCredential.Password -ErrorAction Stop
                    }

                    default
                    {
                        New-SQLServerLogin -Login $login
                    }
                }

                # we can only disable the login once it's been created
                if ( $Disabled )
                {
                    Write-Verbose -Message (
                        $script:localizedData.SetDisabled -f $Name, $ServerName, $InstanceName
                    )

                    $login.Disable()
                }

                # set the default database if specified
                if ( $PSBoundParameters.ContainsKey('DefaultDatabase') )
                {
                    $login.DefaultDatabase = $DefaultDatabase
                    Update-SQLServerLogin -Login $login
                }
            }
        }

        'Absent'
        {
            if ( $serverObject.Logins[$Name] )
            {
                Write-Verbose -Message (
                    $script:localizedData.DropLogin -f $Name, $ServerName, $InstanceName
                )

                Remove-SQLServerLogin -Login $serverObject.Logins[$Name]
            }
        }
    }
}

<#
    .SYNOPSIS
        Tests to verify the login exists and the properties are correctly set.
 
    .PARAMETER Ensure
        Specifies if the login is supposed to exist. Default is 'Present'.
 
    .PARAMETER Name
        The name of the login.
 
    .PARAMETER LoginType
        The type of login. Default is 'WindowsUser'
 
    .PARAMETER ServerName
        Hostname of the SQL Server to create the login on. Default value is $env:COMPUTERNAME.
 
    .PARAMETER InstanceName
        Name of the SQL instance.
 
    .PARAMETER LoginCredential
        The credential containing the password for a SQL Login. Only applies if the login type is SqlLogin.
 
    .PARAMETER LoginMustChangePassword
        Specifies if the login is required to have its password change on the next login. Only applies to SQL Logins. Default is $true.
 
    .PARAMETER LoginPasswordExpirationEnabled
        Specifies if the login password is required to expire in accordance to the operating system security policy. Only applies to SQL Logins. Default is $true.
 
    .PARAMETER LoginPasswordPolicyEnforced
        Specifies if the login password is required to conform to the password policy specified in the system security policy. Only applies to SQL Logins. Default is $true.
 
    .PARAMETER Disabled
        Specifies if the login is disabled. Default is $false.
 
    .PARAMETER DefaultDatabase
        Specifies the default database for the login.
#>
function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [Parameter()]
        [ValidateSet('Present', 'Absent')]
        [System.String]
        $Ensure = 'Present',

        [Parameter(Mandatory = $true)]
        [System.String]
        $Name,

        [Parameter()]
        [ValidateSet('WindowsUser',
            'WindowsGroup',
            'SqlLogin',
            'Certificate',
            'AsymmetricKey',
            'ExternalUser',
            'ExternalGroup'
        )]
        [System.String]
        $LoginType = 'WindowsUser',

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $ServerName = $env:COMPUTERNAME,

        [Parameter(Mandatory = $true)]
        [System.String]
        $InstanceName,

        [Parameter()]
        [System.Management.Automation.PSCredential]
        $LoginCredential,

        [Parameter()]
        [System.Boolean]
        $LoginMustChangePassword = $true,

        [Parameter()]
        [System.Boolean]
        $LoginPasswordExpirationEnabled = $true,

        [Parameter()]
        [System.Boolean]
        $LoginPasswordPolicyEnforced = $true,

        [Parameter()]
        [System.Boolean]
        $Disabled,

        [Parameter()]
        [System.String]
        $DefaultDatabase
    )

    Write-Verbose -Message (
        $script:localizedData.TestingConfiguration -f $Name, $ServerName, $InstanceName
    )

    # Assume the test will pass
    $testPassed = $true

    $getParams = @{
        Name         = $Name
        ServerName   = $ServerName
        InstanceName = $InstanceName
    }

    $loginInfo = Get-TargetResource @getParams

    if ( $Ensure -ne $loginInfo.Ensure )
    {
        Write-Verbose -Message (
            $script:localizedData.WrongEnsureState -f $Name, $loginInfo.Ensure, $Ensure
        )

        $testPassed = $false
    }

    if ( $Ensure -eq 'Present' -and $($loginInfo.Ensure) -eq 'Present' )
    {
        if ( $LoginType -ne $loginInfo.LoginType )
        {
            Write-Verbose -Message (
                $script:localizedData.WrongLoginType -f $Name, $loginInfo.LoginType, $LoginType
            )

            $testPassed = $false
        }

        if ( $PSBoundParameters.ContainsKey('Disabled') -and ($loginInfo.Disabled -ne $Disabled) )
        {
            if ($Disabled)
            {
                Write-Verbose -Message (
                    $script:localizedData.ExpectedDisabled -f $Name
                )
            }
            else
            {
                Write-Verbose -Message (
                    $script:localizedData.ExpectedEnabled -f $Name
                )
            }

            $testPassed = $false
        }

        if ( $PSBoundParameters.ContainsKey('DefaultDatabase') -and ($loginInfo.DefaultDatabase -ne $DefaultDatabase) )
        {
            Write-Verbose -Message (
                $script:localizedData.WrongDefaultDatabase -f $Name, $loginInfo.DefaultDatabase, $DefaultDatabase
            )

            $testPassed = $false
        }

        if ( $LoginType -eq 'SqlLogin' )
        {
            if ( $LoginPasswordExpirationEnabled -ne $loginInfo.LoginPasswordExpirationEnabled )
            {
                if ($LoginPasswordExpirationEnabled)
                {
                    Write-Verbose -Message (
                        $script:localizedData.ExpectedLoginPasswordExpirationEnabled -f $Name
                    )
                }
                else
                {
                    Write-Verbose -Message (
                        $script:localizedData.ExpectedLoginPasswordExpirationDisabled -f $Name
                    )
                }

                $testPassed = $false
            }

            if ( $LoginPasswordPolicyEnforced -ne $loginInfo.LoginPasswordPolicyEnforced )
            {
                if ($LoginPasswordPolicyEnforced)
                {
                    Write-Verbose -Message (
                        $script:localizedData.ExpectedLoginPasswordPolicyEnforcedEnabled -f $Name
                    )
                }
                else
                {
                    Write-Verbose -Message (
                        $script:localizedData.ExpectedLoginPasswordPolicyEnforcedDisabled -f $Name
                    )
                }

                $testPassed = $false
            }

            # If testPassed is still true and a login credential was specified, test the password
            if ( $testPassed -and $LoginCredential )
            {
                $userCredential = [System.Management.Automation.PSCredential]::new($Name, $LoginCredential.Password)

                try
                {
                    Connect-SQL -ServerName $ServerName -InstanceName $InstanceName -SetupCredential $userCredential -LoginType 'SqlLogin' | Out-Null
                }
                catch
                {
                    # Check to see if the parameter of $Disabled is true
                    if ($Disabled)
                    {
                        <#
                            An exception occurred and $Disabled is true, we neeed
                            to check the error codes for expected error numbers.
                            Recursively search the Exception variable and inner
                            Exceptions for the specific numbers.
                            18470 - Username and password are correct, but
                            account is disabled.
                            18456 - Login failed for user.
                        #>
                        if ((Find-ExceptionByNumber -ExceptionToSearch $_.Exception -ErrorNumber 18470))
                        {
                            Write-Verbose -Message (
                                $script:localizedData.PasswordValidButLoginDisabled -f $Name
                            )
                        }
                        elseif ((Find-ExceptionByNumber -ExceptionToSearch $_.Exception -ErrorNumber 18456))
                        {
                            Write-Verbose -Message (
                                '{0} {1}' -f
                                ($script:localizedData.PasswordValidationFailed -f $Name),
                                ($script:localizedData.PasswordValidationFailedMessage -f $_.Exception.message)
                            )

                            # The password was not correct, password validation failed
                            $testPassed = $false
                        }
                        else
                        {
                            # Something else went wrong, rethrow error
                            $errorMessage = $script:localizedData.PasswordValidationError
                            New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
                        }
                    }
                    else
                    {
                        Write-Verbose -Message (
                            $script:localizedData.PasswordValidationFailed -f $Name
                        )

                        $testPassed = $false
                    }
                }
            }
        }
    }

    return $testPassed
}

<#
    .SYNOPSIS
        Alters a login.
 
    .PARAMETER Login
        The Login object to alter.
 
    .NOTES
        This function allows us to more easily write mocks.
#>
function Update-SQLServerLogin
{
    param
    (
        [Parameter(Mandatory = $true)]
        [Microsoft.SqlServer.Management.Smo.Login]
        $Login
    )

    try
    {
        $originalErrorActionPreference = $ErrorActionPreference
        $ErrorActionPreference = 'Stop'

        $Login.Alter()
    }
    catch
    {
        $errorMessage = $script:localizedData.AlterLoginFailed -f $Login.Name
        New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
    }
    finally
    {
        $ErrorActionPreference = $originalErrorActionPreference
    }
}

<#
    .SYNOPSIS
        Creates a login.
 
    .PARAMETER Login
        The Login object to create.
 
    .PARAMETER LoginCreateOptions
        The LoginCreateOptions object to use when creating a SQL login.
 
    .PARAMETER SecureString
        The SecureString object that contains the password for a SQL login.
 
    .EXAMPLE
        CreateLogin -Login $login -LoginCreateOptions $LoginCreateOptions -SecureString $LoginCredential.Password -ErrorAction Stop
 
    .EXAMPLE
        CreateLogin -Login $login
 
    .NOTES
        This function allows us to more easily write mocks.
#>
function New-SQLServerLogin
{
    [CmdletBinding(DefaultParameterSetName = 'WindowsLogin')]
    param
    (
        [Parameter(Mandatory = $true, ParameterSetName = 'WindowsLogin')]
        [Parameter(Mandatory = $true, ParameterSetName = 'SqlLogin')]
        [Microsoft.SqlServer.Management.Smo.Login]
        $Login,

        [Parameter(Mandatory = $true, ParameterSetName = 'SqlLogin')]
        [Microsoft.SqlServer.Management.Smo.LoginCreateOptions]
        $LoginCreateOptions,

        [Parameter(Mandatory = $true, ParameterSetName = 'SqlLogin')]
        [System.Security.SecureString]
        $SecureString
    )

    switch ( $PSCmdlet.ParameterSetName )
    {
        'SqlLogin'
        {
            try
            {
                $originalErrorActionPreference = $ErrorActionPreference
                $ErrorActionPreference = 'Stop'

                $login.Create($SecureString, $LoginCreateOptions)
            }
            catch [Microsoft.SqlServer.Management.Smo.FailedOperationException]
            {
                if ( $_.Exception.InnerException.InnerException.InnerException -match 'Password validation failed' )
                {
                    $errorMessage = $script:localizedData.CreateLoginFailedOnPassword -f $Login.Name
                    New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
                }
                else
                {
                    $errorMessage = $script:localizedData.CreateLoginFailed -f $Login.Name
                    New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
                }
            }
            catch
            {
                $errorMessage = $script:localizedData.CreateLoginFailed -f $Login.Name
                New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
            }
            finally
            {
                $ErrorActionPreference = $originalErrorActionPreference
            }
        }

        'WindowsLogin'
        {
            try
            {
                $originalErrorActionPreference = $ErrorActionPreference
                $ErrorActionPreference = 'Stop'

                $login.Create()
            }
            catch
            {
                $errorMessage = $script:localizedData.CreateLoginFailed -f $Login.Name
                New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
            }
            finally
            {
                $ErrorActionPreference = $originalErrorActionPreference
            }
        }
    }
}

<#
    .SYNOPSIS
        Drops a login.
 
    .PARAMETER Login
        The Login object to drop.
 
    .NOTES
        This function allows us to more easily write mocks.
#>
function Remove-SQLServerLogin
{
    param
    (
        [Parameter(Mandatory = $true)]
        [Microsoft.SqlServer.Management.Smo.Login]
        $Login
    )

    try
    {
        $originalErrorActionPreference = $ErrorActionPreference
        $ErrorActionPreference = 'Stop'

        $Login.Drop()
    }
    catch
    {
        $errorMessage = $script:localizedData.DropLoginFailed -f $Login.Name
        New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
    }
    finally
    {
        $ErrorActionPreference = $originalErrorActionPreference
    }
}

<#
    .SYNOPSIS
        Changes the password of a SQL Login.
 
    .PARAMETER Login
        The Login object to change the password on.
 
    .PARAMETER SecureString
        The SecureString object that contains the password for a SQL login.
 
    .NOTES
        This function allows us to more easily write mocks.
#>
function Set-SQLServerLoginPassword
{
    param
    (
        [Parameter(Mandatory = $true)]
        [Microsoft.SqlServer.Management.Smo.Login]
        $Login,

        [Parameter(Mandatory = $true)]
        [System.Security.SecureString]
        $SecureString
    )

    try
    {
        $originalErrorActionPreference = $ErrorActionPreference
        $ErrorActionPreference = 'Stop'

        $Login.ChangePassword($SecureString)
    }
    catch [Microsoft.SqlServer.Management.Smo.FailedOperationException]
    {
        if ( $_.Exception.InnerException.InnerException.InnerException -match 'Password validation failed' )
        {
            $errorMessage = $script:localizedData.SetPasswordValidationFailed -f $Login.Name
            New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
        }
        else
        {
            $errorMessage = $script:localizedData.SetPasswordFailed -f $Login.Name
            New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
        }
    }
    catch
    {
        $errorMessage = $script:localizedData.SetPasswordFailed -f $Login.Name
        New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
    }
    finally
    {
        $ErrorActionPreference = $originalErrorActionPreference
    }
}

Export-ModuleMember -Function *-TargetResource