Functions/Test-SqlConnection.ps1

<#
    .SYNOPSIS
        Test the SQL connection to a target SQL Server.
 
    .DESCRIPTION
        Test the connection to a SQL Server. Currently only the .NET SqlClient
        provider is supported.
 
    .INPUTS
        None.
 
    .OUTPUTS
        SqlServerFever.TestConnectionResult. Result of the test connection.
 
    .EXAMPLE
        PS C:\> Test-SqlConnection -ComputerName 'SQL01'
        Test the SQL connection to SQL01 with Windows authentication.
 
    .EXAMPLE
        PS C:\> Test-SqlConnection -ComputerName 'SQL01' -Credential 'sa'
        Test the SQL connection to SQL01 with SQL authentication.
 
    .EXAMPLE
        PS C:\> Test-SqlConnection -ComputerName 'SQL01' -Database 'Demo0'
        Test the SQL connection to SQL01 to the database Demo0.
 
    .EXAMPLE
        PS C:\> Test-SqlConnection -ComputerName 'SQL01' -Encrypt
        Test the SQL connection to SQL01 with encryption.
 
    .LINK
        https://github.com/claudiospizzi/SqlServerFever
#>

function Test-SqlConnection
{
    [CmdletBinding()]
    [OutputType([System.Boolean])]
    [OutputType([System.Management.Automation.PSCustomObject])]
    param
    (
        # SQL instance name.
        [Parameter(Mandatory = $true)]
        [System.String]
        $SqlInstance,

        # SQL Login. If not specified, use integrated security.
        [Parameter(Mandatory = $false)]
        [System.Management.Automation.PSCredential]
        $SqlCredential,

        # Database to connect, be default master.
        [Parameter(Mandatory = $false)]
        [System.String]
        $Database = 'master',

        # The SQL client provider to use for the test.
        [Parameter(Mandatory = $false)]
        [ValidateSet('SqlClient')]
        [System.String]
        $Provider,

        # Option to enable encryption for the SQL Server connection.
        [Parameter(Mandatory = $false)]
        [Switch]
        $Encrypt,

        # Option to return only true or false.
        [Parameter(Mandatory = $false)]
        [Switch]
        $Quiet
    )

    $ErrorActionPreference = 'Stop'

    # Default connection string with target server and database.
    $connectionString = 'Data Source={0}; Initial Catalog={1}' -f $SqlInstance, $Database

    # Depending on the credential parameter, append the user id and password or
    # the integrated security note.
    if ($PSBoundParameters.ContainsKey('SqlCredential'))
    {
        $connectionString += '; User ID={0}; Password={1}' -f $SqlCredential.UserName, $SqlCredential.GetNetworkCredential().Password
    }
    else
    {
        $connectionString += '; Integrated Security=true'
    }

    # Finally, force an encrypted connection.
    if ($Encrypt.IsPresent)
    {
        $connectionString += '; Encrypt=true'
    }

    # Remove the passwort on the verbose output.
    $connectionStringDisplay = $connectionString
    if ($PSBoundParameters.ContainsKey('SqlCredential'))
    {
        $connectionStringDisplay = $connectionString -replace ($SqlCredential.GetNetworkCredential().Password -as [System.String]), '***'
    }

    Write-Verbose "SQL Connection String: $connectionStringDisplay"

    $result = $null

    try
    {
        $sqlConnection = New-Object -TypeName 'System.Data.SqlClient.SqlConnection' -ArgumentList $connectionString
        $sqlConnection.Open()

        try
        {
            $sqlCommandSession = New-Object -TypeName 'System.Data.SqlClient.SqlCommand' -ArgumentList "SELECT @@SPID, SYSTEM_USER, USER, @@SERVERNAME, @@SERVICENAME, @@VERSION, (SELECT create_date FROM sys.databases WHERE name = 'tempdb')", $sqlConnection
            $sqlReaderSession = $sqlCommandSession.ExecuteReader()

            if ($sqlReaderSession.Read())
            {
                if ($Quiet.IsPresent)
                {
                    return $true
                }
                else
                {
                    $result = [PSCustomObject] @{
                        PSTypeName       = 'SqlServerFever.TestConnectionResult'
                        ConnectionString = $connectionStringProtected
                        Id               = $sqlReaderSession[0]
                        Login            = $sqlReaderSession[1]
                        User             = $sqlReaderSession[2]
                        Protocol         = ''
                        Encryption       = ''
                        Server           = $sqlReaderSession[3]
                        Instance         = $sqlReaderSession[4]
                        Version          = ($sqlReaderSession[5] -as [System.String]).Split("`n")[0]
                        StartDate        = $sqlReaderSession[6]
                        Uptime           = [System.DateTime]::Now - $sqlReaderSession[6]
                    }
                }
            }
        }
        catch
        {
            if (-not $Quiet.IsPresent)
            {
                throw $_
            }
        }
        finally
        {
            if ($null -ne $sqlReaderSession)
            {
                $sqlReaderSession.Close()
                $sqlReaderSession.Dispose()
            }
        }

        if (-not $Quiet.IsPresent)
        {
            try
            {
                $sqlCommandConnection = New-Object -TypeName 'System.Data.SqlClient.SqlCommand' -ArgumentList "SELECT auth_scheme, encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID", $sqlConnection
                $sqlReaderConnection = $sqlCommandConnection.ExecuteReader()

                if ($sqlReaderConnection.Read())
                {
                    $result.Protocol   = $sqlReaderConnection[0]
                    $result.Encryption = $sqlReaderConnection[1]
                }
            }
            catch
            {
                Write-Warning "Error occured while getting connection information: $_"
            }
            finally
            {
                if ($null -ne $sqlReaderConnection)
                {
                    $sqlReaderConnection.Close()
                    $sqlReaderConnection.Dispose()
                }
            }
        }
    }
    catch
    {
        if (-not $Quiet.IsPresent)
        {
            throw $_
        }
    }
    finally
    {
        if ($null -ne $sqlConnection)
        {
            $sqlConnection.Close()
            $sqlConnection.Dispose()
        }
    }

    if ($Quiet.ISPresent)
    {
        return $false
    }
    elseif ($null -ne $result)
    {
        return $result
    }
}