This powershell creates a helper function that tests connection to SQL database. In my situation, found this in order to check if port and protocols needed for SQL connections were being allowed from different vlans.
Usage:
Test-SQLDatabase -Server SQLServer -Database SomeDB -Username SQLUser -Password password
Credit to: https://stackoverflow.com/a/38784435 (Rob Holme)
#
# Usage:
# Test-SQLDatabase -Server SQLServer -Database SomeDB -Username SQLUser -Password password
#
# Credit to: https://stackoverflow.com/a/38784435 (Rob Holme)
#
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
}
}
}
