MSFT_SqlAlias.psm1

Import-Module -Name (Join-Path -Path (Split-Path (Split-Path $PSScriptRoot -Parent) -Parent) -ChildPath 'SqlServerDSCHelper.psm1') -Force

function Get-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Collections.Hashtable])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Name,

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

    $returnValue = @{
        Name = [System.String] $Name
        Protocol = [System.String] ''
        ServerName = [System.String] $ServerName
        TcpPort = [System.UInt16] 0
        UseDynamicTcpPort = [System.Boolean] $false
        PipeName = [System.String] ''
        Ensure = [System.String] 'Absent'
    }

    $protocolTcp = 'DBMSSOCN'
    $protocolNamedPipes = 'DBNMPNTW'

    Write-Verbose -Message "Getting the SQL Server Client Alias $Name"
    $itemValue = Get-ItemProperty -Path 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo' `
                                  -Name $Name `
                                  -ErrorAction SilentlyContinue

    if (((Get-CimInstance -ClassName win32_OperatingSystem).OSArchitecture) -eq '64-bit')
    {
        Write-Verbose -Message "64-bit Operating System. Also get the client alias $Name from Wow6432Node"

        $isWow6432Node = $true
        $itemValueWow6432Node = Get-ItemProperty -Path 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo' `
                                                 -Name $Name `
                                                 -ErrorAction SilentlyContinue
    }

    if ((-not $isWow6432Node -and $null -ne $itemValue ) -or `
       (($null -ne $itemValue -and $null -ne $itemValueWow6432Node) -and `
       ($isWow6432Node -and $itemValueWow6432Node."$Name" -eq $itemValue."$Name")))
    {
        $itemConfig = $itemValue."$Name" | ConvertFrom-Csv -Header 'Protocol','ServerName','TcpPort'
        if ($itemConfig)
        {
            if ($itemConfig.Protocol -eq $protocolTcp)
            {
                $returnValue.Ensure = 'Present'
                $returnValue.Protocol = 'TCP'
                $returnValue.ServerName = $itemConfig.ServerName
                if ($itemConfig.TcpPort)
                {
                    $returnValue.TcpPort = $itemConfig.TcpPort
                    $returnValue.UseDynamicTcpPort = $false
                }
                else
                {
                    $returnValue.UseDynamicTcpPort = $true
                    $returnValue.TcpPort = 0
                }
            }
            elseif ($itemConfig.Protocol -eq $protocolNamedPipes)
            {
                $returnValue.Ensure = 'Present'
                $returnValue.Protocol = 'NP'
                $returnValue.PipeName = $itemConfig.ServerName
            }
        }
        else
        {
            $returnValue.Ensure = 'Absent'
        }
    }
    else
    {
        $returnValue.Ensure = 'Absent'
    }

    $returnValue
}

function Set-TargetResource
{
    [CmdletBinding(SupportsShouldProcess)]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Name,

        [Parameter()]
        [ValidateSet('TCP','NP')]
        [System.String]
        $Protocol = 'TCP',

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

        [Parameter()]
        [System.UInt16]
        $TcpPort = 1433,

        [Parameter()]
        [System.Boolean]
        $UseDynamicTcpPort = $false,

        [Parameter()]
        [ValidateSet('Present','Absent')]
        [System.String]
        $Ensure = 'Present'
    )

    Write-Verbose -Message "Setting the SQL Server Client Alias $Name"

    if ($Protocol -eq 'NP')
    {
        $itemValue = "DBNMPNTW,\\$ServerName\PIPE\sql\query"
    }

    if ($Protocol -eq 'TCP')
    {
        $itemValue = "DBMSSOCN,$ServerName"
        if (!$UseDynamicTcpPort)
        {
            $itemValue += ",$TcpPort"
        }
    }

    $registryPath = 'HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo'
    $registryPathWow6432Node = 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo'

    if ($Ensure -eq 'Present')
    {
        Write-Verbose -Message "Adding the SQL Server Client Alias $Name"

        if ($PSCmdlet.ShouldProcess($Name, 'Setting the client alias'))
        {
            if (!(Test-Path -Path $registryPath))
            {
                New-Item -Path $registryPath | Out-Null
            }

            Set-ItemProperty -Path $registryPath -Name $Name -Value $itemValue | Out-Null
        }

        # If this is a 64-bit OS then also update Wow6432Node
        if (((Get-CimInstance -ClassName win32_OperatingSystem).OSArchitecture) -eq '64-bit')
        {
            if ($PSCmdlet.ShouldProcess($Name, 'Setting the client alias (32-bit)'))
            {
                if (!(Test-Path -Path $registryPathWow6432Node))
                {
                    New-Item -Path $registryPathWow6432Node | Out-Null
                }

                Set-ItemProperty -Path $registryPathWow6432Node -Name $Name -Value $itemValue | Out-Null
            }
        }
    }

    if ($Ensure -eq 'Absent')
    {
        Write-Verbose -Message "Removing the SQL Server Client Alias $Name"

        if ($PSCmdlet.ShouldProcess($Name, 'Remove the client alias'))
        {
            if (Test-Path -Path $registryPath)
            {
                Remove-ItemProperty -Path $registryPath -Name $Name
            }
        }

        # If this is a 64-bit OS then also remove from Wow6432Node
        if (((Get-CimInstance -ClassName win32_OperatingSystem).OSArchitecture) -eq '64-bit' `
              -and (Test-Path -Path $registryPathWow6432Node))
        {
            if ($PSCmdlet.ShouldProcess($Name, 'Remove the client alias (32-bit)'))
            {
                Remove-ItemProperty -Path $registryPathWow6432Node -Name $Name
            }
        }
    }
}

function Test-TargetResource
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    param
    (
        [Parameter(Mandatory = $true)]
        [ValidateNotNullOrEmpty()]
        [System.String]
        $Name,

        [Parameter()]
        [ValidateSet('TCP','NP')]
        [System.String]
        $Protocol = 'TCP',

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

        [Parameter()]
        [System.UInt16]
        $TcpPort = 1433,

        [Parameter()]
        [System.Boolean]
        $UseDynamicTcpPort = $false,

        [Parameter()]
        [ValidateSet('Present','Absent')]
        [System.String]
        $Ensure = 'Present'
    )

    Write-Verbose -Message "Testing the SQL Server Client Alias $Name"

    $result = $false

    $parameters = @{
        Name = $PSBoundParameters.Name
        ServerName = $PSBoundParameters.ServerName
    }

    $currentValues = Get-TargetResource @parameters

    if ($Ensure -eq $currentValues.Ensure)
    {
        if( $Ensure -eq 'Absent' )
        {
            $result = $true
        }
        else
        {
            Write-Verbose -Message 'Ensure is in the desired state. Verifying values.'

            if ($Protocol -eq $currentValues.Protocol)
            {
                if ($Protocol -eq 'NP' -and
                    $currentValues.PipeName -eq "\\$ServerName\PIPE\sql\query")
                {
                    $result = $true
                }
                elseif ($Protocol -eq 'TCP' -and
                    $UseDynamicTcpPort -and
                    $currentValues.ServerName -eq $ServerName)
                {
                    $result = $true
                }
                elseif ($Protocol -eq 'TCP' -and
                    -not $UseDynamicTcpPort -and
                    $currentValues.ServerName -eq $ServerName -and
                    $currentValues.TcpPort -eq $TcpPort)
                {
                    $result = $true
                }
            }
        }
    }

    if ($result)
    {
        Write-Verbose -Message 'In the desired state'
    }
    else
    {
        Write-Verbose -Message 'Not in the desired state'
    }

    return $result
}

Export-ModuleMember -Function *-TargetResource