Test Database Connectivity

Question

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?

Answer 1

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;" 

Answer 2

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