SqlLogin: Attempting to disable and already disabled login throws an error

[JoshKingWork]

Problem description

I am attempting to upgrade to SqlServerDsc 16.3.1 and SqlServer 22.1.1 to support moving to SQL Server 2022. When using SqlLogin to disable an account it works on the first DSC run, but on subsequent runs it throws an error when trying to Test-TargetResource.

The stack trace has the reason:

Microsoft.Data.SqlClient.SqlException: Login failed for user 'sa'. Reason: The account is disabled.

The account is disabled, however the message I was expecting in the DSC logs is:

VERBOSE: [DBSERVERNAME]:                            [[SqlLogin]Disable_SALogin] The password for the login 'sa' is valid, but the login is        disabled. 

Instead I get the error in the log below.

Verbose logs

VERBOSE: [DBSERVERNAME]: LCM:  [ Start  Resource ]  [[SqlLogin]Disable_SALogin]                                                                   VERBOSE: [DBSERVERNAME]: LCM:  [ Start  Test     ]  [[SqlLogin]Disable_SALogin]                                                                   VERBOSE: [DBSERVERNAME]:                            [[SqlLogin]Disable_SALogin] Determines if the login 'sa' at the instance                      'DBSERVERNAME\DBINSTANCENAME' has the correct state.                                                                                              VERBOSE: [DBSERVERNAME]:                            [[SqlLogin]Disable_SALogin] Getting the login 'sa' from the instance                          'DBSERVERNAME\DBINSTANCENAME'.                                                                                                                    VERBOSE: [DBSERVERNAME]:                            [[SqlLogin]Disable_SALogin] Connecting as current user 'NT AUTHORITY\SYSTEM' using integrated  security. (SQLCOMMON0054)                                                                                                                         VERBOSE: [DBSERVERNAME]:                            [[SqlLogin]Disable_SALogin] Connected to SQL instance 'DBSERVERNAME\DBINSTANCENAME'.         (SQLCOMMON0018)                                                                                                                                    VERBOSE: [DBSERVERNAME]:                            [[SqlLogin]Disable_SALogin] The login 'sa' is Present at the instance                         'DBSERVERNAME\DBINSTANCENAME'.                                                                                                                    VERBOSE: [DBSERVERNAME]:                            [[SqlLogin]Disable_SALogin] Impersonate credential 'sa' with login type 'SqlLogin'.           (SQLCOMMON0056)                                                                                                                                    VERBOSE: [DBSERVERNAME]: LCM:  [ End    Test     ]  [[SqlLogin]Disable_SALogin]  in 0.6320 seconds.                                               PowerShell DSC resource DSC_SqlLogin  failed to execute Test-TargetResource functionality with error message: System.InvalidOperationException:    Password validation failed with an error. ---> System.Exception: System.InvalidOperationException: Failed to connect to SQL instance               'DBSERVERNAME\DBINSTANCENAME'. (SQLCOMMON0019) ---> System.Management.Automation.MethodInvocationException: Exception calling "Connect" with "0"  argument(s): "Failed to connect to server DBSERVERNAME\DBINSTANCENAME." ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException:    Failed to connect to server DBSERVERNAME\DBINSTANCENAME. ---> Microsoft.Data.SqlClient.SqlException: Login failed for user 'sa'. Reason: The      account is disabled.                                                                                                                                  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.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet  bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)                                                                                   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet     bulkCopyHandler, TdsParserStateObject stateObj)                                                                                                       at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)                                                               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 connectionOptions, SqlCredential credential, TimeoutTimer timeout)             at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions,               SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)                                         at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions,            SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance,                 SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback,                   ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo                  originalNetworkAddressInfo, Boolean applyTransientFaultHandling)                                                                                      at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object              poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)                                         at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject,                     DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)                                                            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, DbConnectionInternal& connection)                                                at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory                 connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)                                                                     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 ---                                                                                                         --- End of inner exception stack trace ---                                                                                                          + CategoryInfo          : InvalidOperation: (:) [], CimException                                                                                   + FullyQualifiedErrorId : ProviderOperationExecutionFailure                                                                                        + PSComputerName        : DBSERVERNAME

DSC configuration

SqlLogin Disable_SALogin {
            Ensure                         = 'Present'
            Name                           = 'sa'
            LoginType                      = 'SqlLogin'
            ServerName                     = $Node.NodeName
            InstanceName                   = $NewInstanceName
            LoginCredential                = $SAPwd
            Disabled             = $true
            DependsOn = "[SqlSetup]SqlSetup_$NewInstanceName"
        }

Suggested solution

It appears that this should be handled in https://github.com/dsccommunity/SqlServerDsc/blob/main/source/DSCResources/DSC_SqlLogin/DSC_SqlLogin.psm1#L586

                        if ((Find-ExceptionByNumber -ExceptionToSearch $_.Exception -ErrorNumber 18470))
                        {
                            Write-Verbose -Message (
                                $script:localizedData.PasswordValidButLoginDisabled -f $Name
                            )
                        }

Instead it is being handled in https://github.com/dsccommunity/SqlServerDsc/blob/main/source/DSCResources/DSC_SqlLogin/DSC_SqlLogin.psm1#L603

                        else
                        {
                            # Something else went wrong, rethrow error
                            $errorMessage = $script:localizedData.PasswordValidationError
                            New-InvalidOperationException -Message $errorMessage -ErrorRecord $_
                        }

I believe this is because the error being thrown in https://github.com/dsccommunity/SqlServerDsc/blob/main/source/Modules/SqlServerDsc.Common/SqlServerDsc.Common.psm1#L645 has been converted to a string which looses the InnerExceptions.

        $newObjectParameters = @{
            TypeName     = 'System.Management.Automation.ErrorRecord'
            ArgumentList = @(
                $invalidOperationException.ToString(),
                'CS0002',
                'InvalidOperation',
                $databaseEngineInstance
            )

I will submit a PR with this fix if it makes sense.

SQL Server edition and version

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)   Oct  8 2022 05:58:25   Copyright (C) 2022 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

Name      Version    Path
----      -------    ----
SqlServer 22.1.1     C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.1.1\SqlServer.psd1
SqlServer 21.1.18256 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psd1
SQLPS     16.0       C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\SQLPS\SQLPS.psd1

Operating system

OsName               : Microsoft Windows Server 2019 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsVersion       : 1809
WindowsBuildLabEx    : 17763.1.amd64fre.rs5_release.180914-1434
OsLanguage           : en-US
OsMuiLanguages       : {en-US}

PowerShell version

Name                           Value
----                           -----
PSVersion                      5.1.17763.4644
PSEdition                      Desktop
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.17763.4644
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1

SqlServerDsc version

Name         Version  Path
----         -------  ----
SqlServerDsc 16.3.1   C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\16.3.1\SqlServerDsc.psd1
SqlServerDsc 15.1.1   C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\15.1.1\SqlServerDsc.psd1
SqlServerDsc 13.2.0.0 C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\13.2.0.0\SqlServerDsc.psd1