The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "UC4_SOURCE" was unable to begin a distributed transaction.

Products

• CA Automic Workload Automation - Automation Engine
• Automic SaaS
• CA Automic One Automation

Issue/Introduction

When running the UTF8 Action Pack for MSSQL Database, Step PCK.AUTOMIC_UTF-8.MIG-STEP05.COPY.INIT.DATA.* fails with the below error 

The operation could not be performed because OLE DB provider "MSOLEDBSQL" for linked server "UC4_SOURCE" was unable to begin a distributed transaction.
U02004025 Job-script execution was aborted.
U02012006 SQL Rollback executed.

Environment

Database: MSSQL (UTF8 Migration Action Pack)

Cause

This is due to various internal setup and services required by MSSQL, Please check out the below articles  

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/linked-servers/error-message-ole-db-provider 

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-7391-database-engine-error?view=sql-server-ver16 

Resolution

Fixed by:

• Open the Component Services console (dcomcnfg).
• Navigate to "Component Services" -> "Computers" -> "My Computer" -> "Distributed Transaction Coordinator" -> "Local DTC".
• Right-click on "Local DTC" and select "Properties".
• Go to the "Security" tab, and ensure the following settings are enabled:
  • Network DTC Access
  • Allow Remote Clients
  • Allow Remote Administration (optional)
  • Allow Inbound
  • Allow Outbound
  • No Authentication Required (or configure appropriately based on your security requirements)