Issue with Self Hosted Integration setup in Synapse ADF

Question

Hi Community,

We are using Synapse due to Fabric not available in a GCC tenant. Most of the setup has gone great. One issue:

I have setup a Self Hosted Integration Runtime instance to connect to on-prem SQL database servers. When testing the connection I get this error:

Only one valid authentication should be used for BIReporting2. SQLAuthentication is invalid. One of user/password is missing.

I have tried using Version 2.0 and 1.0, but get the same error. I have tested the SHIR server can reach the SQL database server.

What is strange when setting up the LinkedService the test connection is successful.

Any help will be greatly appreciated.

Thanks,

DK Hughes

Answer 1

The error appears to be raised during linked-service configuration validation, before an actual connection to SQL Server is attempted. Microsoft documents the related SqlServerInvalidLinkedServiceCredentialMissing condition as an invalid SQL Server linked service with missing credentials. Therefore, changing firewall rules, reinstalling the self-hosted integration runtime or switching between connector versions is unlikely to address this specific message. [learn.microsoft.com]

Since the linked-service test succeeds, I would compare the values used during the successful test with those resolved during the failing operation. In particular, check whether BIReporting2 uses a parameterised userName, password, Key Vault secret name or linked-service reference.

A common cause is that a value is available during the interactive test but resolves to an empty or null value when the pipeline, dataset or Data Flow runs.

Checking the resoved runtime config:

1. Open Monitor > Pipeline runs > Activity runs and inspect the failed activity’s Input and Output.
2. Confirm that the failing activity references the expected dataset and BIReporting2 linked service.
3. Check that every linked-service parameter receives a value from the dataset or pipeline.
4. If the password comes from Azure Key Vault, verify that the workspace managed identity can read the exact secret version being referenced.
5. Publish all changes and run the published pipeline. A Debug run and a triggered run can use different parameter values if unpublished changes or trigger parameters are involved.
6. If source and sink linked services use different integration runtimes, verify the runtime selected for the actual activity. For Copy activity, if either side uses a self-hosted IR, the Copy activity runs on that self-hosted IR. [learn.microsoft.com]

For SQL auth, the recommended SQL Server connector expects authenticationType to be SQL, a non-empty userName, and a password supplied either as a SecureString or through an Azure Key Vault reference. [learn.microsoft.com]

The existing thread already covers the Mapping Data Flow limitation. If this is a Data Flow, SHIR cannot execute it because SQL Server Mapping Data Flow supports Azure IR, whereas Copy, Lookup, Get Metadata, Script and Stored Procedure activities support SHIR. [learn.microsoft.com], [learn.microsoft.com]

If it is not a Data Flow, the most useful next detail would be the failing activity type and a redacted JSON definition ofBIReporting2, including its parameters and connectVia section. Do not include usernames, passwords, secret values or server names.

Help make this community better for everyone: if this answer resolved your issue, please accept it or leave an upvote. If not, share more details in a comment so we can continue the discussion and find the right solution