SqlDatabaseRole: Tries to execute Set-TargetResource on a read-only secondary

[randomnote1]

Problem description

When a database is a read-only secondary in an Availability Group, the resource attempts to execute Set-TargetResource when it is not in the desired state.

Verbose logs

{"time": "2022-06-05T23:33:19.570+00:00", "type": "verbose", "message": "[SqlServer01]: LCM:  [ Start  Resource ]  [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance]  "},
  {"time": "2022-06-05T23:33:19.570+00:00", "type": "verbose", "message": "[SqlServer01]: LCM:  [ Start  Test     ]  [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance]  "},
  {"time": "2022-06-05T23:33:20.010+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Testing the desired state of the SQL database role 'ConfigService'."},
  {"time": "2022-06-05T23:33:20.010+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Getting properties of the SQL database role 'ConfigService'."},
  {"time": "2022-06-05T23:33:20.010+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)"},
  {"time": "2022-06-05T23:33:20.010+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Connecting as current user 'Contoso\DscExecutionAccount' using integrated security. (SQLCOMMON0054)"},
  {"time": "2022-06-05T23:33:20.010+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Connected to SQL instance 'SqlServer01'. (SQLCOMMON0018)"},
  {"time": "2022-06-05T23:33:20.010+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Ensure is set to Present. Either the role 'ConfigService' is missing and should be created, or members in the role are not in the desired state."},
  {"time": "2022-06-05T23:33:20.020+00:00", "type": "verbose", "message": "[SqlServer01]: LCM:  [ End    Test     ]  [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance]  in 0.4500 seconds."},
  {"time": "2022-06-05T23:33:20.020+00:00", "type": "verbose", "message": "[SqlServer01]: LCM:  [ Start  Set      ]  [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance]  "},
  {"time": "2022-06-05T23:33:20.432+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Setting properties of the SQL database role 'ConfigService'."},
  {"time": "2022-06-05T23:33:20.432+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Found PowerShell module SQLPS already imported in the session. (SQLCOMMON0026)"},
  {"time": "2022-06-05T23:33:20.432+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Connecting as current user 'Contoso\DscExecutionAccount' using integrated security. (SQLCOMMON0054)"},
  {"time": "2022-06-05T23:33:20.432+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Connected to SQL instance 'SqlServer01'. (SQLCOMMON0018)"},
  {"time": "2022-06-05T23:33:20.432+00:00", "type": "verbose", "message": "[SqlServer01]:                            [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance] Creating role 'ConfigService' in database 'OperationsManager'."},
  {"time": "2022-06-05T23:33:20.467+00:00", "type": "verbose", "message": "[SqlServer01]: LCM:  [ End    Set      ]  [[SqlDatabaseRole]OperationsManager-ConfigService::[SqlServerStandaloneInstance]SqlServerStandaloneInstance]  in 0.4470 seconds."},
  {"time": "2022-06-05T23:33:20.467+00:00", "type": "error", "message": "PowerShell DSC resource DSC_SqlDatabaseRole  failed to execute Set-TargetResource functionality with error message: System.InvalidOperationException: Failed to create role 'ConfigService' in database 'OperationsManager'. ---> System.Management.Automation.MethodInvocationException: Exception calling \"Create\" with \"0\" argument(s): \"Create failed for DatabaseRole 'ConfigService'. \" ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for DatabaseRole 'ConfigService'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: The target database ('OperationsManager') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.\r\n   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)\r\n   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)\r\n   --- End of inner exception stack trace ---\r\n   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)\r\n   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)\r\n   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext, Boolean executeForAlter)\r\n   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImplFinish(StringCollection createQuery, ScriptingPreferences sp)\r\n   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()\r\n   --- End of inner exception stack trace ---\r\n   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()\r\n   at CallSite.Target(Closure , CallSite , Object )\r\n   --- End of inner exception stack trace ---\r\n   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)\r\n   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)\r\n   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)\r\n   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)\r\n   --- End of inner exception stack trace --- "},

DSC configuration

SqlDatabaseRole 'OperationsManager-ConfigService'
{
    DatabaseName = 'OperationsManager'
    DependsOn = $databaseRoleDependencies
    Ensure = 'Present'
    InstanceName = $SqlInstanceName
    MembersToInclude = $databaseRoleMember.Members
    Name = $databaseRoleMember.Role
    PsDscRunAsCredential = $DscServiceAccountCredential
}

Suggested solution

Get-TargetResource should retrieve the IsUpdateable property from the Microsoft.SqlServer.Management.Smo.Database object. Test-TargetResource should return $false only if the user is not in the desired state and the IsUpdateable property is $true.

SQL Server edition and version

Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
	Apr 11 2022 16:24:07 
	Copyright (C) 2019 Microsoft Corporation
	Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

SQL Server PowerShell modules

Name  Version Path
----  ------- ----
SQLPS 15.0    E:\Program Files (x86)\Microsoft SQL Server\150\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                                                                                                                                                
----                           -----                                                                                                                                                
PSRemotingProtocolVersion      2.3                                                                                                                                                  
BuildVersion                   10.0.17763.2867                                                                                                                                      
PSVersion                      5.1.17763.2867                                                                                                                                       
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}                                                                                                                              
PSEdition                      Desktop                                                                                                                                              
CLRVersion                     4.0.30319.42000                                                                                                                                      
WSManStackVersion              3.0                                                                                                                                                  
SerializationVersion           1.1.0.1

SqlServerDsc version

Pre-release main branch