Test-SqlConnection.ps1

function Test-SQLDatabase
{
    param(
        [Parameter(Position = 0, Mandatory = $True, ValueFromPipeline = $True)] [string] $Server,
        [Parameter(Position = 1, Mandatory = $True)] [string] $Database,
        [Parameter(Position = 2, Mandatory = $True, ParameterSetName = 'SQLAuth')] [string] $Username,
        [Parameter(Position = 3, Mandatory = $True, ParameterSetName = 'SQLAuth')] [string] $Password,
        [Parameter(Position = 2, Mandatory = $True, ParameterSetName = 'WindowsAuth')] [switch] $UseWindowsAuthentication
    )

    # connect to the database, then immediatly close the connection. If an exception occurrs it indicates the conneciton was not successful.
    process
    {
        $dbConnection = New-Object System.Data.SqlClient.SqlConnection
        if (!$UseWindowsAuthentication)
        {
            $dbConnection.ConnectionString = "Data Source=$Server; uid=$Username; pwd=$Password; Database=$Database;Integrated Security=False"
            $authentication = "SQL ($Username)"
        }
        else
        {
            $dbConnection.ConnectionString = "Data Source=$Server; Database=$Database;Integrated Security=True;"
            $authentication = "Windows ($env:USERNAME)"
        }
        try
        {
            $connectionTime = Measure-Command { $dbConnection.Open() }
            $Result = @{
                Connection  = 'Successful'
                ElapsedTime = $connectionTime.TotalSeconds
                Server      = $Server
                Database    = $Database
                User        = $authentication
            }
        }
        # exceptions will be raised if the database connection failed.
        catch
        {
            $Result = @{
                Connection  = 'Failed'
                ElapsedTime = $connectionTime.TotalSeconds
                Server      = $Server
                Database    = $Database
                User        = $authentication
            }
        }
        Finally
        {
            # close the database connection
            $dbConnection.Close()
            #return the results as an object
            $outputObject = New-Object -Property $Result -TypeName psobject
            Write-Output $outputObject
        }
    }
}

Test-SQLDatabase -Server '127.0.0.1,1433' -Database master -Username sa -Password Password123