SQL Failed to connect in datafactory linked service

Question

SQL Failed to connect in datafactory linked service

Ravichandran, Gopi Krishna 161

Hi, We are experiencing SQL failed to connect error when we do a test connection using "managed integration runtime" in the Azure data factory. We have two environments (Test and Prod) and it works well for test and fails for Prod. Both are configured with 'System Assigned managed identity'. ADF identities are given a 'Contributor' Role permissions on the SQL databases, but still, it pops up the attached error. Is there any way we can diagnose and fix this? Screenshot 2023-04-25 at 16.01.23.png

HimanshuSinha 19,637 Reputation points Microsoft Employee Moderator

2023-04-26T21:52:08.0266667+00:00

Hello @Ravichandran, Gopi Krishna ,

Thanks for the question and using MS Q&A platform.

Looking at the error it seems to me that the ADF/Linked service is not able to connect to the production DW instance, but as you mentioned that it works for Test. Have you checked if the production ASQL is in VNET ? If I were you I could have tried to find as to how is the network configuration different for production vs test .

Thanks

Himanshu

Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.
Ravichandran, Gopi Krishna 161 Reputation points

2023-04-27T08:42:24.7+00:00

Hi HimanshuSinha-msft,

The SQL DB is not in VNET and is using a service endpoint without VNET integration. But this has been working with a Test environment of ADF with the same setup. Is this something to do with ' ADF managed identity' ?