Can't get powershell to handle a SQL Server connection failure

Question

I am deliberately trying to log in to a SQL Server where I do not have a login to test some error handling with PowerShell 2.0 and SMO using SQL Server 2008 R2.

Here's my script:

param ([String]$instanceName);
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null;

$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ; 
$conn.LoginSecure = $true; 
$conn.ServerInstance = $instanceName ; 
$conn.NonPooledConnection = $true ;  

try {
    $serverInstance = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ; 
}

catch { 
    $err = $Error[0].Exception ; 
    write-host "Error caught: "  $err.Message ; 
    continue ; 
} ; 

Write-Output $serverInstance.Version;

the catch block does not get executed. Any ideas? I also tried to trap it using the trap function, but got the same result.

UPDATE 1

I have changed my script to the following and have got it to catch an exception on line

foreach($j in $serverInstance.Databases) {

If I comment out the foreach loop, the line below the foreach loop does not raise an exception, although it should (IMO).

param ([String]$instanceName);

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null; # load the SMO assembly
clear

 $ErrorActionPreference = "Stop";
    $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ; 
    $conn.LoginSecure = $false; 
    $conn.Login = "sa" ; 
    $conn.Password = "password" ; 
    $conn.ServerInstance = $instanceName ; 
    $conn.NonPooledConnection = $true ;  

try {
    $serverInstance = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ; 
    foreach($j in $serverInstance.Databases) { 
        write-host $j.name ; 
    } ; 
    Write-Output $serverInstance.Databases.Count;
}

catch [Microsoft.SqlServer.Management.Common.ConnectionFailureException] {
    $err = $Error[0].Exception ; 
    write-host "Error caught: "  $err.Message ; 
    while ( $err.InnerException )    {
        $err = $err.InnerException;
        Write-Host "Inner exception:" $err.InnerException.Message;
        };      
    return;
} ;

SMO opens a connection when required and nothing in your try block actually connects to the server. Does moving your Write-Output command into the try block cause an error? — Pondlife
– Pondlife, Commented Oct 25, 2012 at 15:59
@Pondlife moving the $serverInstance.Version to the try block does not cause an error. I get the same behaviour. — Mark Allison
– Mark Allison, Commented Oct 26, 2012 at 7:49
I've added an updated script to my question under heading UPDATE 1. — Mark Allison
– Mark Allison, Commented Oct 26, 2012 at 10:55

Mark Allison · Accepted Answer · 2012-10-26 11:01:13Z

1

I've found a workaround. Microsoft have confirmed this as a bug in SMO and they do not deem it important enough to fix for now. The workaround is detailed here:

https://connect.microsoft.com/SQLServer/feedback/details/636401/smo-is-inconsistent-when-raising-errors-for-login-faliures#

answered Oct 26, 2012 at 11:01

Mark Allison

7,25836 gold badges107 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chip Wood Over a year ago

Microsoft Connect is retired and the link no longer works

Collectives™ on Stack Overflow

Can't get powershell to handle a SQL Server connection failure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related