[ALM Accelerator - BUG]: failed pipeline on Set Dataverse AAD Group Teams

[J3ffr0y3d]

Describe the issue

Since we updated to the March release, whenever we either select importing a solution or committing a solution, we get a failure on Set Dataverse AAD Group Teams in the pipeline.
The AAD group team in question already exists in the environment, and is set in the customDeploymentSettings.json in the config files in the branch.

Error :

WARNING: The names of some imported commands from the module 'Microsoft.PowerApps.AuthModule' include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb. WARNING: The names of some imported commands from the module 'Microsoft.PowerApps.Administration.PowerShell' include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb. dataverseHost - org040ee32c.crm1.dynamics.com aadGroupTeamConfiguration - D:\a\1\outAadGroupTeamConfiguration.json config - @{aadGroupTeamName=TXT - ITtest; aadGroupTeamBusinessUnitId=12345678-1234-1234-1234-12345678; aadSecurityGroupId=87654321-4321-4321-4321-87654321; dataverseSecurityRoleNames=System.Object[]} aadGroupTeamName - TXT - ITtest businessUnitId - 12345678-1234-1234-1234-12345678 aadId - 87654321-4321-4321-4321-87654321 securityRoleNames - Environment Maker Checking if AAD group already mapped to DV teams in the target. 'Teams with AAD' Query - teams?$select=teamid,name&$filter=(azureactivedirectoryobjectid eq '87654321-4321-4321-4321-87654321') Given AAD team with id - 87654321-4321-4321-4321-87654321 is already mapped to the Dataverse team - TXT - ITtest Existing team - TXT - ITtest matching with aadGroupTeamName - TXT - ITtest. Proceeding with Security Role refresh Checking and fetching the Team by name TXT - ITtest in the target environment Teams Query - teams?$select=teamid&$filter=(name eq 'TXT+-+ITtest') Team with name TXT - ITtest found. Updating the existing team - TXT - ITtest Failed to Execute Command - Update : RequestID=a978226c-9d96-4465-84cf-2e479ad80207 : Updating team : 5639f6ad-203b-ec11-b6e5-6045bd8b80d9 duration=00:00:03.3457678 ExceptionMessage = Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 547 Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 547 => Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 547 => Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 547 => Exception output:: System.Data.SqlClient.SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY constraint "business_unit_teams". The conflict occurred in database "db_crmcoreemea_20210827_07581202_09c6", table "dbo.BusinessUnitBase", column 'BusinessUnitId'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1
wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean
callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String
resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior,
Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds,
Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean
returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName,
Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Crm.Core.DataServices.Connection.QueryBlocking.CrmCommand.ExecuteNonQuery()
at Microsoft.Crm.Core.DataServices.Connection.CrmSqlDataAccessHelper.LockConnectionAndExecuteMethod[TResult](IDbConn
ection connection, Func1 executeMethod) at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetryAbstract[TResult](Func1 executeMethod, IDbCommand
command, IConnectionRetryPolicy retryPolicy)
at Microsoft.Crm.CrmDbConnection.<>c__DisplayClass235_0.b__0()
at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId,
ActivityType activityType, Func1 func, IEnumerable1 additionalCustomProperties)
at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType
activityType, Func1 func) at Microsoft.Crm.Platform.Server.DataEngine.SqlDataAccessManager.ExecuteNonQuery(IDbCommand command, ISqlExecutionContext context, Int32 sourceLineNumber, String memberName, String sourceFilePath) at Microsoft.Crm.Platform.Server.DataEngine.EntityCrudManager.Update(IBusinessEntity entity, FilterExpression filter, ExecutionContext context) at Microsoft.Crm.BusinessEntities.BusinessProcessObject.DoUpdate(IBusinessEntity entity, FilterExpression filter, ExecutionContext context) ClientConnectionId:4f170601-6db8-4b05-b541-335bf6adbbbe Error Number:547,State:0,Class:16 ClientConnectionId before routing:0ba22c8f-3b9e-4486-88a9-b1c8fa93afe7 Routing Destination:e84303b66771.tr25057.westeurope1-a.worker.database.windows.net,11059 Error(s) details: Class: 0x10 LineNumber: 1 Message: The UPDATE statement conflicted with the FOREIGN KEY constraint "business_unit_teams". The conflict occurred in database "db_crmcoreemea_20210827_07581202_09c6", table "dbo.BusinessUnitBase", column 'BusinessUnitId'. Number: 547 Procedure: Server: e84303b66771.tr25057.westeurope1-a.worker.database.windows.net,11059 Source: .Net SqlClient Data Provider State: 0 ToString(): System.Data.SqlClient.SqlError: The UPDATE statement conflicted with the FOREIGN KEY constraint "business_unit_teams". The conflict occurred in database "db_crmcoreemea_20210827_07581202_09c6", table

Expected Behavior

No response

What component are you experiencing the issue with?

ALM Accelerator Pipelines

What solution version are you using?

March 2023 Release

Steps To Reproduce

1. Click on the "Commit Solution" Button in ALM.
2. select target branch, enter notes, choose scope.
3. select "commit Solution"
4. Click on Processing icon --> Navigates to Azure Devops

Anything else?

No response

[RajeevPentyala]

@J3ffr0y3d Thanks for reaching out. From the error message, noticed that its failing to update the "TXT - ITtest" team columns. To troubleshoot the issue, could you confirm following points?

• Are you trying to update the 'Business Unit' of 'TXT - ITtest'?
  

• Is it an intermittent issue or persisting?

[J3ffr0y3d]

hi @RajeevPentyala we are not trying to update 'TXT - ITtest' as it already there and correct, its always added to the config file "customDeploymentSettings.json" of any new solution.
The error occured everytime time you run the import pipeline or deploy pipeline.
In order for the pipelines to complete without error, i had to remove the "AadGroupTeamConfiguration" entry from the config file "customDeploymentSettings.json".

[RajeevPentyala]

@J3ffr0y3d Could not reproduce the error at our end. When you say, "its always added to the config file "customDeploymentSettings.json" of any new solution". I would assume "AadGroupTeamConfiguration" node of customDeploymentSettings.json file, populates with configuration, even though you had not provided any.
Can you check and confirm if you see any entries under 'Group Teams'.

If its not a first commit, please open 'ALM Accelerator for Power Platform Administration' and go to 'user Setting' and make sure you don't have "AadGroupTeamConfiguration" related data.

[J3ffr0y3d]

hi, i can confirm there is no entries under the groups teams section, no entries in the user settings for "adGroupTeamConfiguration". the only entry we had which we added in manually ourselves was in the "customDeploymentSettings.json". ( as i have said above the group already excists in the Environment)

when you say "I would assume "AadGroupTeamConfiguration" node of customDeploymentSettings.json file, populates with configuration, even though you had not provided any." No it doesn't populate any configuration as we dont specify any in the "group teams". However when we commit a solution none of the config files in validation, Prod, test, populate the 'values' specified from the other settings e.g. in the "customdeploymentSettings.json" we have to add the value's under "EnvironmentVariables": "activateAsUser": "test@tes.com", "sortOrder": "1", "activate": "true" and in the the "deploymentsettings.json" the""EnvironmentVariables": "value" & "ConnectionReferences": "ConnectionId" & "ConnectionOwner" aren't populated with the values set in the deployment Settings (user settings).

However all the setting are populated in the variables of the pipeline. but not the aadgroup team as theres none provided.

hope that all makes sense, and helps in some way.
thanks

[SAEED2ALAHMARI]

Hello, how do I end this repeated failure in this project

[RajeevPentyala]

Hello, how do I end this repeated failure in this project

@SAEED2ALAHMARI Can you confirm the version of 'ALM Accelerator' build you were using?.

[RajeevPentyala]

Closing this issue due to lack of updates for over a month. Please open a new issue for any further concerns.

[CoEStarterKitBot]

@J3ffr0y3d This has been fixed in the latest release. Please install the latest version of the toolkit following the instructions for installing updates. Note that if you do not remove the unmanaged layers as described there you will not receive updates from us.