Operation on target Copy data1 failed: ErrorCode=UserErrorPropertyInvalidOrMissing,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The property 'clientSecret' is invalid or missing.,Source=Microsoft.DataTransfer.ClientLibrary,'

Question

Hi,

I've been encountering this issue and was hoping to get help from microsoft team. Our team has a ADF pipeline that contains copy data activity. We have dev and production versions of data factories and are doing deployments using this architecture. In dev environment, it is working fine but not in production (clientSecret invalid or not found). I'm starting to think that this might be platform issue rather than configuration issue. The copy activity fetches data from api which perfectly works and fails on the sink side (Azure SQL database). The SQL Database linked service is connecting via connection string which is stored in a key vault.

Some other important things

• The key vault linked service works properly as this is a general link service for many jobs
• The SQL Database linked service passes the test connection
• The secret for the linked service being fetched during run time. This is because it ran in production without the secret being created in the vault and gave an error that that specific secret name is not found. After, creating in the vault, that error was not raised and got the clientSecret error instead.
• Works in dev, fails in production

Need some advice on this, thank you!

Answer 1

Hello Patrick !

Thank you for posting on Microsoft Learn Q&A.

That error is raised when a linked credential that uses service principal auth is evaluated at runtime and that can be:

• the Azure SQL linked service (if it uses AAD service principal auth)
• the Key Vault linked service/credential (if it uses service principal instead of MI)
• a factory credential asset that your linked services reference

Try to compare between DEV and PROD :

• Data Factory : managed identities
  • DEV: which identity is enabled (system-assigned? user-assigned?)
  • PROD: must match whichever your KV LS or credential expects
• Key Vault : access configuration
  • If using Access policies: add the PROD factory’s identity (or the UAMI you selected) with Get + List (Secrets).
  • If using RBAC: grant Key Vault Secrets User (or better: Key Vault Secrets Officer) to that identity at the vault scope.

Try to check the secret itself in key vault in prod since the secret name referenced by your LS/credential exists, is Enabled, Not before is in the past, Expiry not passed, and latest version contains the actual SP secret string (no quotes/JSON/line breaks).

If you parameterize the secret name, confirm the PROD parameter resolves to the expected name (not DEV).

Open the Azure SQL linked service JSON in PROD and check:

{
  "type": "AzureSqlDatabase",
  "typeProperties": {
    "connectionString": "@Microsoft.KeyVault(SecretUri=...)",   // or individual fields
    "authenticationType": "AADServicePrincipal",                // <- if present, you DO need clientId+clientSecret
    "servicePrincipalId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
    "tenant": "yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy",
    "servicePrincipalCredential": {
      "type": "AzureKeyVaultSecret",
      "store": { "referenceName": "<your-kv-linked-service>", "type": "LinkedServiceReference" },
      "secretName": "sql-sp-client-secret"                      // must exist in PROD KV
    }
  },
  "connectVia": { "referenceName": "AutoResolveIntegrationRuntime", "type": "IntegrationRuntimeReference" }
}

If authenticationType = AADServicePrincipal, make sure servicePrincipalCredential really points to a valid KV secret in PROD.

If you actually intend to use Managed Identity to SQL, switch to authenticationType = ManagedIdentity (no clientSecret needed), and ensure:

The PROD factory MI (or UAMI) is created as a user in the SQL DB:

-- at server level
-- Set an AAD admin at the SQL server, then in the database:
CREATE USER [<factory-identity-or-UAMI-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<name>];
ALTER ROLE db_datawriter ADD MEMBER [<name>];

SQL server firewall / private endpoints allow access from the IR used at runtime.