Based on your question, your source seems to be an onpremises database.
So you must be using a self hosted IR.
Can you confirm whether you are able to access the Azure SQL database from the server on which the SHIR is hosted?
Because whenever a SHIR is being used either in source or sink of a copy activity, the SHIR acts as a bridge between the source and the sink; so both the source and the sink must be accessible from within the server hosting the SHIR for the copy activity to be a success