Update on IISWebAppDeploy package version broke WinRM - SQL Server DB Deployment task on TFS 2018

[RafBorrelli]

Hello,
today something changed on the WinRM - SQL Server DB Deployment task (IISWebAppDeploy package) which causes errors on TFS 2018 that was correctly deploying Dacpac files to SQL Server until yesterday.

This is the version showed in log today (1.4.0)
Loading module from path 'E:\agent2018-01\_work\_tasks\SqlDacpacDeploy_063c7649-4934-49e8-9eac-37121fe7e350\1.4.0\DeploymentSDK\InvokeRemoteDeployment.ps1'.

This is the version showed in the log of the same distribution task runned yesterday (1.3.12)
Loading module from path 'E:\agent2018-01\_work\_tasks\SqlDacpacDeploy_063c7649-4934-49e8-9eac-37121fe7e350\1.3.12\DeploymentSDK\InvokeRemoteDeployment.ps1'.

Log of the error

2023-04-05T16:46:47.5412403Z ##[debug]Entering script DeployToSqlServer.ps1
2023-04-05T16:46:47.5412719Z ##[debug]machinesList = DEV-DB01.dev.loc
2023-04-05T16:46:47.5413034Z ##[debug]adminUserName = AGSDOM\svc_tfs
2023-04-05T16:46:47.5413292Z ##[debug]winrmProtocol  = Http
2023-04-05T16:46:47.5413559Z ##[debug]testCertificate = true
2023-04-05T16:46:47.5413812Z ##[debug]TaskType = dacpac
2023-04-05T16:46:47.5414138Z ##[debug]dacpacFile = C:\[redacted].dacpac
2023-04-05T16:46:47.5414430Z ##[debug]sqlFile = 
2023-04-05T16:46:47.5414689Z ##[debug]targetMethod = server
2023-04-05T16:46:47.5414949Z ##[debug]serverName = [redacted]
2023-04-05T16:46:47.5415221Z ##[debug]databaseName = [redacted]
2023-04-05T16:46:47.5415483Z ##[debug]authscheme = Windows Authentication
2023-04-05T16:46:47.5415750Z ##[debug]sqlUsername = 
2023-04-05T16:46:47.5415996Z ##[debug]publishProfile = 
2023-04-05T16:46:47.5416899Z ##[debug]additionalArguments = /p:BlockOnPossibleDataLoss=False /p:DropObjectsNotInSource=True /p:DoNotDropObjectTypes=Logins;LinkedServerLogins;Assemblies;SearchPropertyLists;FullTextStoplists;Endpoints;PartitionSchemes;Users;FullTextCatalogs;Certificates;Contracts;Audits;SecurityPolicies;ExternalFileFormats;Filegroups;ErrorMessages;CryptographicProviders;ApplicationRoles;DatabaseRoles;ServerRoles;LinkedServers;Services;Synonyms;ClrUserDefinedTypes;UserDefinedDataTypes;MessageTypes;UserDefinedTableTypes;Aggregates;RoleMembership;ServerRoleMembership;RemoteServiceBindings;ServerTriggers;Permissions;AsymmetricKeys;ColumnMasterKeys;ColumnEncryptionKeys;SymmetricKeys;Queues;Credentials;DatabaseScopedCredentials;Signatures;PartitionFunctions;Defaults;EventNotifications;ExternalDataSources;BrokerPriorities;XmlSchemaCollections;Rules;Sequences;Routes;EventSessions;ServerAuditSpecifications;DatabaseAuditSpecifications
2023-04-05T16:46:47.5417772Z ##[debug]deployInParallel = true
2023-04-05T16:46:47.5418028Z ##[debug]inlineSql = 
2023-04-05T16:46:47.5418301Z ##[debug]Triming inputs for excess spaces, double quotes
2023-04-05T16:46:47.5467992Z ##[error]System.Management.Automation.RuntimeException: Additional arguments can't include separator characters '&', ';' and '|'. Please verify input. To learn more about argument validation, please check https://aka.ms/azdo-task-argument-validation ---> System.Management.Automation.RuntimeException: Additional arguments can't include separator characters '&', ';' and '|'. Please verify input. To learn more about argument validation, please check https://aka.ms/azdo-task-argument-validation
2023-04-05T16:46:47.5478165Z ##[debug]Processed: ##vso[task.logissue type=error;]System.Management.Automation.RuntimeException: Additional arguments can't include separator characters '&', ';' and '|'. Please verify input. To learn more about argument validation, please check https://aka.ms/azdo-task-argument-validation ---> System.Management.Automation.RuntimeException: Additional arguments can't include separator characters '&', ';' and '|'. Please verify input. To learn more about argument validation, please check https://aka.ms/azdo-task-argument-validation
2023-04-05T16:46:47.5478692Z    --- End of inner exception stack trace ---
2023-04-05T16:46:47.5478911Z    at System.Management.Automation.Runspaces.PipelineBase.Invoke(IEnumerable input)
2023-04-05T16:46:47.5479168Z    at System.Management.Automation.PowerShell.Worker.ConstructPipelineAndDoWork(Runspace rs, Boolean performSyncInvoke)
2023-04-05T16:46:47.5479427Z    at System.Management.Automation.PowerShell.Worker.CreateRunspaceIfNeededAndDoWork(Runspace rsToUse, Boolean isSync)
2023-04-05T16:46:47.5479724Z    at System.Management.Automation.PowerShell.CoreInvokeHelper[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)
2023-04-05T16:46:47.5480033Z    at System.Management.Automation.PowerShell.CoreInvoke[TInput,TOutput](PSDataCollection`1 input, PSDataCollection`1 output, PSInvocationSettings settings)
2023-04-05T16:46:47.5480320Z    at Microsoft.TeamFoundation.DistributedTask.Handlers.LegacyVSTSPowerShellHost.VSTSPowerShellHost.Main(String[] args)
2023-04-05T16:46:47.5485158Z ##[error]LegacyVSTSPowerShellHost.exe è stato completato. Codice restituito: -1.

Any hint on how workaround this error or rollback to the previous version?

[rmelnick79]

@RafBorrelli I had this issue as well today, I was able to work around it by separating my "DoNotDropObjectTypes" into an entry for each, for example yours would need to be changed to:

/p:DoNotDropObjectType=Logins /p:DoNotDropObjectType=LinkedServerLogins /p:DoNotDropObjectType=Assemblies ... and so on.

Note: when you change the format like this "/p:DoNotDropObjectTypes" would need to be changed to "/p:DoNotDropObjectType"

Luckily, I was only protecting 5 or so object types, but I imagine with the amount of Object Types you have listed it would take a bit of time.

Hope this helps! and that it gets fixed soon!!

[CptSandblaster]

We exclude a large number of objects in our tasks. I will try with the workaround above, but could we get an answer here as to why the task has been changed in such a way that it does not accept ; in the additional arguments? Even better would be that the task is reverted to accept ; again. Even according to the documentation the argument should specifically accept semi-colon separated values.

This change has broken all our sql pipelines.

I also tested changing ; to , for /p:DoNotDropObjectTypes, but that this not work as it clearly affected object types mentioned in the (now) comma-separated list. So it seems the only workaround is to change to individual /p:DoNotDropObjectType for every object.