I'm working on my first data project with Azure Data Factory to copy data from our ERP server to an Azure SQL Database, but I'm running into some issues. While using the 'Copy Data Tool', I can set up my source and destination servers without any problems. However, during the deployment phase, specifically at the step 'Validating copy runtime environment', I keep getting an error:
`Fail to connect to AzureSQL_Database from Integration Runtime: ERP-IntegrationRuntime. Error message: Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '', Database: 'IntechAzureSQLDB', User: 'sqlserveradmin'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Login failed for user 'sqlserveradmin'., SqlErrorNumber=18456,Class=14,State=1,.`
I've double-checked my credentials, which work fine in SQL Server Management Studio (SSMS), and I've enabled the 'Allow Azure services and resources to access this server' option in my database settings. The error message mentions the Integration Runtime as 'ERP-IntegrationRuntime', which isn't the one I selected for this connection. My source uses this integration runtime option. Plus, my source database is connected through a self-hosted node for now, since the ADF IP would be blocked. Unfortunately, I don't have admin access to the cloud account; I only have access to the SQL database and ADF. Any ideas on what might be going wrong here?
3 Answers
It sounds like the main issue might be related to the integration runtime configuration. Double-check that the 'ERP-IntegrationRuntime' isn't accidentally set for your target copy. Also, make sure your Azure SQL linked service is configured to use the correct runtime. Sometimes the gateway might not have access to the right endpoint, so checking the settings there would be helpful too.
It looks like your SQL server could be rejecting the login because it doesn’t recognize the source IP where the requests are coming from. It might be worth asking your admin team for logs that show the actual source IP for the rejected logins. That way, you can see if it's getting blocked due to firewall rules.
Good point! If the IP is indeed blocked, they can help adjust the firewall settings to allow access.
Another thing to consider is whether the DNS resolution is directing traffic properly. If the DNS is resolving externally, check whether you permit access via public endpoints. You might need that configured for your connection to work properly.
Definitely check that out. Sometimes those small network configurations can cause big headaches in connection issues!
That makes sense! It’s always good to ensure the right runtime is selected, especially since you mentioned the differences in how your source and target are set up.