PowerShell SQL Connection Test

In the interest of providing minimal surface area exposure to my servers I have taken on the common practice of not installing development tools on any of my Production servers. In almost all cases this is no big deal because I’d prefer to use SSMS from a remote central location anyways but there are a few situations where you would want to know if you can connect locally even though your remote connections fail. Below is a quick and easy PowerShell script for testing a SQL connection, it doesn’t have to be run locally but this is the situation where I like to use it.

Parameter:

This script takes a single string parameter for the server name. This function can easily be modified to accept an array of server names or even read from a text file server list.

Script Version:
[sql]param
(
[parameter(Mandatory=$TRUE,Position=0)]
[String] $Server
)

$connectionString = "Data Source=$Server;Integrated Security=true;Initial Catalog=master;Connect Timeout=3;"
$sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
trap
{
Write-Error "Cannot connect to $Server.";
continue
}
$sqlConn.Open()

if ($sqlConn.State -eq ‘Open’)
{
$sqlConn.Close();
"Opened successfully."
}[/sql]

Function Version:
[sql]Function Test-SQLConn ($Server)
{
$connectionString = "Data Source=$Server;Integrated Security=true;Initial Catalog=master;Connect Timeout=3;"
$sqlConn = new-object ("Data.SqlClient.SqlConnection") $connectionString
trap
{
Write-Error "Cannot connect to $Server.";
continue
}
$sqlConn.Open()

if ($sqlConn.State -eq ‘Open’)
{
$sqlConn.Close();
"Opened successfully."
}
}[/sql]