Forum Discussion
'Cannot connect to SQL Database' error - please help
Hi,
Our organisation is new to Azure Data Factory (ADF) and we're facing an intermittent error with our first Pipeline. Being intermittent adds that little bit more complexity to resolving the error.
The Pipeline has two activities:
1) Script activity which deletes the contents of the target Azure SQL Server database table that is located within our Azure cloud instance.
2) Copy data activity which simply copies the entire contents from the external (outside of our domain) third-party source SQL View and loads it to our target Azure SQL Server database table.
With the source being external to our domain, we have used a Self-Hosted Integration Runtime.
The Pipeline executes once per 24 hours at 3am each morning. I have been informed that this timing shouldn't affect/or by affected by any other Azure processes we have.
For the first nine days of Pipeline executions, the Pipeline successfully completed its executions. Then for the next nine days it only completed successfully four times. Now it seems to fail every other time. It's the same error message that is received on each failure - the received error message is below (I've replaced our sensitive internal names with Xs).
Operation on target scr__Delete stg__XXXXXXXXXX contents failed: Failed to execute script. Exception: ''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'XX-azure-sql-server.database.windows.net', Database: 'XX_XXXXXXXXXX_XXXXXXXXXX', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.,Source=Microsoft.DataTransfer.Connectors.MSSQL,''Type=Microsoft.Data.SqlClient.SqlException,Message=Server provided routing information, but timeout already expired.,Source=Framework Microsoft SqlClient Data Provider,''
To me, if this Pipeline was incorrectly configured then the Pipeline would never have successfully completed, not once. With it being intermittent, but becoming more frequent, suggests it's being caused by something other than its configuration, but I could be wrong - hence requesting help from you.
Please can someone advise on what is causing the error and what I can do to verify/resolve the error?
Thanks.
HelloAzureNewbie1
1.) When you test the connection from the Azure SQL Database linked service configuration page, does it consistently connect successfully?
2.) If you go to the logical SQL Server resource that contains the Azure SQL Database and go to Security -> Networking. Is "Allow Azure services and resources to access this server" checked on?
3.) On the same page, if you are using firewall rules, is the SHIR's IP added? Does this IP ever change?
Kind Regards,
Zackzack-sponaugle- thanks for the response. My answers to your questions are below.
1) Yes, it always returns a successful test connection.
2) I'm not sure what you mean by logical SQL Server. When I access the Azure SQL Server database via SSMS and expand the 'Security' node it doesn't have a 'Networking' node, it has the usual User, Roles, Schemas, etc... nodes.
3) The firewalls are out of my knowledge range. I'm a PBI developer and this is all new to me.
What I will say again is, this is intermittently erroring which is leading to confusion. I'm thinking if the 'Allow Azure services' wasn't checked on, or the 'SHIR IP added' wasn't so then the pipelines executions would never be successful. On this basis, I am assuming all is configured correctly.
Since posting this thread's opening post, I did this:
1) Took the two pipelines and broke out each individual activity in there own individual pipelines. I did this to see which particular activity is failing. I configured these new Pipelines and the pre-existing two pipelines to executes every fifteen minutes, and they all were successful - even the intermittently failing pipelines were successful.
2) I deleted the newly created Pipelines that contained the individual activities. I then set the original two Pipelines to executes every two hours and they were always successful.
3) I set the original two Pipelines to refresh at 8am each morning and they've gone back to be intermittently successful again.
I'm wondering if there is a process outside of my Pipeline build that is interfering with the execution (in particular, the delete from the target table activity) happening each morning.
The error message returned (the error I've pasted in the opening post) isn't guiding me to the cause.
I don't understand how the failures can be intermittent. I still need to get to the bottom of this issue.
