Test Database Connectivity

Is there an easy way to test the connectivity to a MS SQL Server instance from a client (without loading any SQL assemblies) with PowerShell?

MS Sql: Servername\Instance Port 1433

How can I test the connectivity to the server with PowerShell from a normal client?

4 Answers

Use the SqlConnection class to test a connection. You don't have to load any SQL assemblies.

Helper function:

function Test-SQLConnection { [OutputType([bool])] Param ( [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true, Position=0)] $ConnectionString ) try { $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString; $sqlConnection.Open(); $sqlConnection.Close(); return $true; } catch { return $false; } } 

Usage example:

Test-SQLConnection "Data Source=localhost;database=myDB;User ID=myUser;Password=myPassword;" 

This is basically the same as Martin's answer, only the connection string is build from the parameters, and the time taken to connect is measured.

e.g:

Test-SQLDatabase -Server SQLServer -Database SomeDB -Username SQLUser -Password password 

or

Test-SQLDatabase -Server Server1\SQLExpress -Database SomeDB -UseWindowsAuthentication 

.

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 } } } 

That depends on what you actually want to test. If you just want to verify that you can connect to the port on the remote host something like this will do:

$server = 'servername' $port = 1433 $tcp = New-Object Net.Sockets.TcpClient if ([void]$tcp.Connect($server, $port)) { 'connected' } else { 'not connected' } $tcp.Dispose() 

If you want to verify that a connection to an SQL Server instance can be established you'll need something like this:

$dbhost = 'servername' $dbinst = 'instance' $dbname = 'master' $username = ... $password = ... $cs = "Server=$dbhost\$dbinst;Database=$dbname;User Id=$username;" + "Password=$password;" $cn = New-Object -COM 'ADODB.Connection' $cn.ConnectionString = $cs try { $cn.Open() if ($cn.State -eq 1) { 'connected' $cn.Close() } else { 'not connected' } } catch { 'not connected' } 

I've used the ConnectionState enum to check the database connection state.

Documentation on this enum can be found here

It can be accessed with the following: [System.Data.ConnectionState]::Open

Other options are Broken, Closed, Connecting, Executing, and Fetching.

Example:

class Database { [System.Data.SqlClient.SqlConnection]$Connection [void]Connect { $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = $connectionString $sqlConnection.Open() $this.Connection = $sqlConnection } [bool]IsConnected { return $this.Connection.State -eq [System.Data.ConnectionState]::Open } }