'Cannot connect to SQL Database' error - please help

zack-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.