ConnectionFailureException when user running the command does not match the ConnectAsUserName

[alrazmi]

$Credential = Get-Credential -Message "Enter your login name" 
Import-Module -Name SqlServer
$ServerInstance = "SERVERNAME"
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerInstance
$Server.ConnectionContext.LoginSecure = $true
$Server.ConnectionContext.ConnectAsUser = $true
$Server.ConnectionContext.ConnectAsUserName = $Credential.GetNetworkCredential().UserName
$Server.ConnectionContext.ConnectAsUserPassword = $Credential.GetNetworkCredential().Password
try {$Server.ConnectionContext.Connect()} catch {$_.Exception | Format-List -Force}

Throws the following error, when the user running the command does not match the ConnectAsUserName

ErrorRecord                 : Exception calling "Connect" with "0" argument(s): "Failed to connect to server SERVERNAME."
WasThrownFromThrowStatement : False
TargetSite                  : Void CheckActionPreference(System.Management.Automation.Language.FunctionContext, System.Exception)
Message                     : Exception calling "Connect" with "0" argument(s): "Failed to connect to server SERVERNAME."
Data                        : {[System.Management.Automation.Interpreter.InterpretedFrameInfo, System.Management.Automation.Interpreter.InterpretedFrameInfo[]]}
InnerException              : Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server SERVERNAME.
                               ---> System.IO.FileNotFoundException: Could not load file or assembly 'System.Diagnostics.StackTrace, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The
                              system cannot find the file specified.
                              File name: 'System.Diagnostics.StackTrace, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'
                                 at Microsoft.Data.SqlClient.SqlConfigurableRetryLogicManager.get_ConnectionProvider()
                                 at Microsoft.Data.SqlClient.SqlConnection.get_RetryLogicProvider()
                                 at Microsoft.Data.SqlClient.SqlConnection.get_IsProviderRetriable()
                                 at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
                                 at Microsoft.Data.SqlClient.SqlConnection.Open()
                                 at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
                                 at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
                              --- End of stack trace from previous location ---
                                 at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
                                 at System.Security.Principal.WindowsIdentity.RunImpersonatedInternal(SafeAccessTokenHandle token, Action action)
                                 at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect()
                                 at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
                                 --- End of inner exception stack trace ---
                                 at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
                                 at CallSite.Target(Closure, CallSite, Object)
HelpLink                    :
Source                      : System.Management.Automation
HResult                     : -2146233087
StackTrace                  :    at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
                                 at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
                                 at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
                                 at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

Running Windows 11,

Name                           Version          Source                           ProviderName
----                           -------          ------                           ------------
SqlServer                      22.3.0           https://www.powershellgallery.c… PowerShellGet

[shueybubbles]

I guess to get the correct exception you need to have System.Diagnostics.StackTrace.dll available. Is it not installed anywhere? Is the PS module installed in the user profile only, instead of in the global module store? It might be that when we impersonate the ConnectAsUserName user it starts looking for the DLL in their user profile instead of the original profile.

[alrazmi]

@shueybubbles , Thank you for the response, here is the full list of packages installed. all of them are installed for allusers.

get-package | ft -auto

Name                                   Version Source                                                                                                                                            ProviderName
----                                   ------- ------                                                                                                                                            ------------
Microsoft.Data.SqlClient.SNI           6.0.1   C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.SNI.6.0.1\Microsoft.Data.SqlClient.SNI.6.0.1.nupkg                     NuGet
Microsoft.NETCore.Platforms            7.0.4   C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.NETCore.Platforms.7.0.4\Microsoft.NETCore.Platforms.7.0.4.nupkg                       NuGet
Microsoft.NETCore.Targets              5.0.0   C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.NETCore.Targets.5.0.0\Microsoft.NETCore.Targets.5.0.0.nupkg                           NuGet
System.Buffers                         4.6.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Buffers.4.6.0\System.Buffers.4.6.0.nupkg                                                 NuGet
System.Collections.Immutable           9.0.1   C:\Program Files\PackageManagement\NuGet\Packages\System.Collections.Immutable.9.0.1\System.Collections.Immutable.9.0.1.nupkg                     NuGet
System.Diagnostics.StackTrace          4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Diagnostics.StackTrace.4.3.0\System.Diagnostics.StackTrace.4.3.0.nupkg                   NuGet
System.IO                              4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.IO.4.3.0\System.IO.4.3.0.nupkg                                                           NuGet
System.IO.FileSystem                   4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.IO.FileSystem.4.3.0\System.IO.FileSystem.4.3.0.nupkg                                     NuGet
System.IO.FileSystem.Primitives        4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.IO.FileSystem.Primitives.4.3.0\System.IO.FileSystem.Primitives.4.3.0.nupkg               NuGet
System.Memory                          4.6.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Memory.4.6.0\System.Memory.4.6.0.nupkg                                                   NuGet
System.Numerics.Vectors                4.6.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Numerics.Vectors.4.6.0\System.Numerics.Vectors.4.6.0.nupkg                               NuGet
System.Reflection                      4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Reflection.4.3.0\System.Reflection.4.3.0.nupkg                                           NuGet
System.Reflection.Metadata             9.0.1   C:\Program Files\PackageManagement\NuGet\Packages\System.Reflection.Metadata.9.0.1\System.Reflection.Metadata.9.0.1.nupkg                         NuGet
System.Reflection.Primitives           4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Reflection.Primitives.4.3.0\System.Reflection.Primitives.4.3.0.nupkg                     NuGet
System.Runtime                         4.3.1   C:\Program Files\PackageManagement\NuGet\Packages\System.Runtime.4.3.1\System.Runtime.4.3.1.nupkg                                                 NuGet
System.Runtime.CompilerServices.Unsafe 6.1.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Runtime.CompilerServices.Unsafe.6.1.0\System.Runtime.CompilerServices.Unsafe.6.1.0.nupkg NuGet
System.Runtime.Handles                 4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Runtime.Handles.4.3.0\System.Runtime.Handles.4.3.0.nupkg                                 NuGet
System.Text.Encoding                   4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Text.Encoding.4.3.0\System.Text.Encoding.4.3.0.nupkg                                     NuGet
System.Threading.Tasks                 4.3.0   C:\Program Files\PackageManagement\NuGet\Packages\System.Threading.Tasks.4.3.0\System.Threading.Tasks.4.3.0.nupkg                                 NuGet
SqlServer                              22.3.0  https://www.powershellgallery.com/api/v2                                                                                                          PowerShellGet

[shueybubbles]

then you may need to turn on fusion logs using fslogvw to see why the DLL load is failing.