A script for quickly testing a SQL Server Db Connection; useful when debugging potential connectivity issues (beyond just Test-NetConnection)

Test-SqlConnection.ps1

Function Test-SqlConnection {
    [CmdletBinding(DefaultParameterSetName = 'ByConnectionString')]
    Param (
        [Parameter(ParameterSetName = 'ByConnectionString', Mandatory)]
        [string]$ConnectionString
        ,
        [Parameter(ParameterSetName = 'AuthSqlUser', Mandatory)]
        [Parameter(ParameterSetName = 'AuthAdCredentials', Mandatory)]
        [Alias('DbInstance')]
        [string]$ComputerName 
        ,
        [Parameter(ParameterSetName = 'AuthSqlUser', Mandatory)]
        [Parameter(ParameterSetName = 'AuthAdCredentials', Mandatory)]
        [Alias('DbCatalog')]
        [string]$DatabaseName
        ,
        [Parameter(ParameterSetName = 'AuthSqlUser', Mandatory)]
        [System.Management.Automation.PSCredential]
        [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty
        ,
        [Parameter(ParameterSetName = 'AuthAdCredentials', Mandatory)] # currently I've only implemented this for current user; not any AD user; 
        [Switch]$UseTrustedConnection
        ,
        [Parameter()]
        [Switch]$BoolOnly
    )
    if ($PSCmdlet.ParameterSetName -ne 'ByConnectionString') {
        [System.Data.SqlClient.SqlConnectionStringBuilder]$bob = [System.Data.SqlClient.SqlConnectionStringBuilder]::new() # I've gone SQL specific; not System.Data.Common.DbConnectionStringBuilder
        # properties aren't recognised for some reason; instead accessing as a dictionary (see `[System.Data.SqlClient.SqlConnectionStringBuilder]::new().Keys` for valid values)
        $bob['Data Source'] = $ComputerName #$bob.DataSource = $ComputerName
        $bob['Initial Catalog'] = $DatabaseName #$bob.InitialCatalog = $DatabaseName
        $bob['Integrated Security'] = $UseTrustedConnection.IsPresent #$bob.IntegratedSecurity = $UseTrustedConnection.IsPresent
        if ($PSCmdlet.ParameterSetName -eq 'AuthSqlUser') {
            $bob['User ID'] = $Credential.UserName #$bob.UserID = $Credential.UserName
            $bob['Password'] = $Credential.GetNetworkCredential().Password # $bob.Password = $Credential.GetNetworkCredential().Password # note: uses plaintext; not secure string; since connectionstring is plaintext anyway
        }
        $ConnectionString = $bob.ConnectionString
    }
    [PSCustomObject]$result = [PSCustomObject]@{ConnectionString = $ConnectionString;OK=$false;ErrorMessage=$null}
    try {
        [System.Data.SqlClient.SqlConnection]$connection = [System.Data.SqlClient.SqlConnection]::new($ConnectionString)
        $connection.Open()
        $result.OK = $true
    } catch {
        $result.ErrorMessage=$_.Exception.Message
    } finally {
        $connection.Dispose()
    }
    if ($BoolOnly.IsPresent) {
        $result.OK
    } else {
        $result
    }
}

Test-SqlConnection -ComputerName 'MyServer\OptionalInstanceName' -DatabaseName 'MyDb' -Credential ([PSCredential]::new('someSqlUsername', ('m7P@55w0rD' | ConvertTo-SecureString -AsPlainText -Force)))