I am trying to do a lookup from a sql server table. I am able to connect to the sql server instance via MSSQL management studio, but in ADF I get the below error.
Cannot connect to SQL Database: '', Database: '', User: '****'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication., SqlErrorNumber=18452,Class=14,State=1, Activity ID: cd443ae3-b583-4c2f-87af-36013c3cefd9
I got the same error I followed this Article.it's working fine, some times errors may appear because of self-hosted IR.
I just added SQL database firewall rules and added client IP and allow Azure services and resources to access this server on the portal.
Please follow the below reference:
Reference:
https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-create-server-level-portal-quickstart?view=azuresql#create-a-server-level-ip-firewall-rule
https://www.mssqltips.com/sqlservertip/5812/connect-to-onpremises-data-in-azure-data-factory-with-the-selfhosted-integration-runtime-part-1/
https://www.youtube.com/watch?v=xgDn14hxsTM&t=1421s