search

dsccommunity / SqlServerDsc

This module contains DSC resources for deployment and configuration of Microsoft SQL Server.
MIT License
360 stars 225 forks source link

`Restart-SqlService`: Silently ignores errors that prevents the instance to go online #1891

Closed johlju closed 1 year ago

johlju commented 1 year ago

The Connect-SQL cmdlet is working as expected but this becomes an issue here when waiting for the restart. The ErrorActionPrefernce is masking that the Connect-SQL cmdlet is throwing an exception about the SSL connection which will never resolve itself. This results in $testConnectionServerObject always being null which ends up waiting for a loop control condition that will never occur.

Creating a new issue for this.

Originally posted by @johlju in https://github.com/dsccommunity/SqlServerDsc/issues/1888#issuecomment-1486417998

johlju commented 1 year ago

We could add a variable to save any errors that occur when ErrorAction is SilentlyContinue. That $connectSqlError could then be written out with Write-Warning.

Example: 

PS C:\Users\sqladmin> Connect-SQL -ServerName localhost -InstanceName sql2017 -Verbose -ErrorAction 'SilentlyContinue' -ErrorVariable connectSqlError
VERBOSE: Connecting as current user 'DSCADLAB\sqladmin' using integrated security. (SQLCOMMON0054)
PS C:\Users\sqladmin> $connectSqlError[2].FullyQualifiedErrorId
CS0002,Connect-SQL
PS C:\Users\sqladmin> $connectSqlError[1].FullyQualifiedErrorId
ConnectionFailureException
PS C:\Users\sqladmin> $connectSqlError[0].FullyQualifiedErrorId
PS C:\Users\sqladmin> $connectSqlError[1].FullyQualifiedErrorId -eq [Microsoft.SqlServer.Management.Common.ConnectionFailureException].Name
True
PS C:\Users\sqladmin> $connectSqlError[2].CategoryInfo

Category   : InvalidOperation
Activity   : Write-Error
Reason     : Exception
TargetName : localhost\sql2017
TargetType : String

PS C:\Users\sqladmin> $connectSqlError[1].CategoryInfo

Category   : NotSpecified
Activity   :
Reason     : MethodInvocationException
TargetName :
TargetType :

PS C:\Users\sqladmin> $connectSqlError[0].CategoryInfo
PS C:\Users\sqladmin> $connectSqlError[2].Exception
System.InvalidOperationException: Failed to connect to SQL instance 'localhost\sql2017'. (SQLCOMMON0019) ---> System.Management.Automation.MethodInvocationException: Exception calling "Connect" with "0" arg
ument(s): "Failed to connect to server localhost\sql2017." ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server localhost\sql2017. ---> Microsoft.Data.SqlClient
.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct a
nd that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean
withFailover, Boolean isFirstTransparentAttempt, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, Boolean useOriginalAddressInfo, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean
 withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connect
ionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassw
ord, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, Sec
ureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificat
eRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnect
ion, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions u
serOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions
userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbCon
nectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOp
tions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
   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 )
   --- End of inner exception stack trace ---
   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)
   --- End of inner exception stack trace ---
PS C:\Users\sqladmin> $connectSqlError[1].Exception
Exception calling "Connect" with "0" argument(s): "Failed to connect to server localhost\sql2017."
PS C:\Users\sqladmin> $connectSqlError[0].Exception
PS C:\Users\sqladmin> $connectSqlError
System error.
Exception calling "Connect" with "0" argument(s): "Failed to connect to server localhost\sql2017."
At C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.2.0\Modules\SqlServerDsc.Common\SqlServerDsc.Common.psm1:554 char:9
+         $sqlConnectionContext.Connect()
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ConnectionFailureException

Connect-SQL : System.InvalidOperationException: Failed to connect to SQL instance 'localhost\sql2017'. (SQLCOMMON0019) ---> System.Management.Automation.MethodInvocationException: Exception calling "Connect
" with "0" argument(s): "Failed to connect to server localhost\sql2017." ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server localhost\sql2017. ---> Microsoft.
Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance nam
e is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean
withFailover, Boolean isFirstTransparentAttempt, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, Boolean useOriginalAddressInfo, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean
 withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connect
ionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassw
ord, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, Sec
ureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificat
eRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnect
ion, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions u
serOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions
userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbCon
nectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOp
tions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
   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 )
   --- End of inner exception stack trace ---
   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)
   --- End of inner exception stack trace ---
At line:1 char:1
+ Connect-SQL -ServerName localhost -InstanceName sql2017 -Verbose -Err ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (localhost\sql2017:String) [Write-Error], Exception
    + FullyQualifiedErrorId : CS0002,Connect-SQL
johlju commented 1 year ago

Another option is to use try/catch or using ErrorVariable to look for the exception [Microsoft.SqlServer.Management.Common.ConnectionFailureException], and then try to continue to connect. But that assuming that is not part of the error we are trying to throw. The function Find-ExceptionByNumber could help here (or be extended).

PS C:\Users\sqladmin> Connect-SQL -ServerName localhost -InstanceName sql2017 -Verbose -ErrorAction 'SilentlyContinue' -ErrorVariable connectSqlError

PS C:\Users\sqladmin> $connectSqlError[1].Exception.InnerException -is [Microsoft.SqlServer.Management.Common.ConnectionFailureException]
True
PS C:\Users\sqladmin> $connectSqlError[1].Exception.InnerException.InnerException -is [Microsoft.Data.SqlClient.SqlException]
johlju commented 1 year ago

@Kreby as part of issue #1888 is it possible for you to change the code to output the error you are receiving in Connect-SQL

Changing the line

https://github.com/dsccommunity/SqlServerDsc/blob/c78042351574abc8cb029e83f5650e8af8d626e1/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1#L1020

to

$connectSqlError = $null
$testConnectionServerObject = Connect-SQL -ServerName $ServerName -InstanceName $InstanceName -ErrorAction 'SilentlyContinue' -ErrorVariable connectSqlError
Write-Verbose -Message ($connectSqlError | ConvertTo-Json | Out-String) -Verbose

Then run the configuration again the resulted in the issue reported in #1888.

Kreby commented 1 year ago

@johlju I'll give this a try once I'm done testing the changes for #1892 because with the changes from #1892 I won't see the error.

johlju commented 1 year ago

Much appreciated. Hopefully with that information we can figure out a good solution for this issue too. 🙂

johlju commented 1 year ago

I proposed a change for this issue here: https://github.com/dsccommunity/SqlServerDsc/pull/1895/files#diff-93e6cdced450f35fe738fddb8a5f672fa754acf1286a0a5116708f7212e64970

It will pass the error objects (converted to JSON) to the error that is thrown if the timeout value is reached. @Kreby you think this is sufficient?

Kreby commented 1 year ago

@johlju I was testing this on Friday. I think this will certainly work. The JSON object can be quite large or at least that was the case with the original proposal. I see you've included the -Depth 4, I've not tested that so it might prune the output down. Previously it included a ton of extra Error Object properties that were probably overkill. That being said it is safer to err on the side of caution.

Either way, adding the additional information when the timeout happens will be helpful for troubleshooting in the event something does occur. This way you can see more than just the timeout message without having to debug or edit the module to see what's happening.

johlju commented 1 year ago

I'm hoping this way it will show errors that wrongly happens so we could potentially add better logic in the future, when users see what actually is thrown. Normally we shouldn't see any errors at all, so better a long error when it happens then a to short one. 🙂

I remembered now that New-InvalidOperationException takes a ErrorRecord, so instead of converting the error record to JSON we could just pass it in to the command and it will render the error message using normal PowerShell error handling. I will update the PR.