Self hosted IR is like a bridge between ADF (Cloud service) and any offering which is within a VNET.
So to ans your queries:
- In case if the Azure SQL databases are hosted within a VNet, you might need a self hosted IR (alternative would be to whitelist the IP range of ADF region in which it is hosted)
In case of normal Azure SQL databases, with Allow Azure service property enabled om Server, there is no need of Self IR. ADF can auto connect to Azure SQL DB.
- As stated in the top definition, we would need Self IR in case if the source/sink is hosted within a Vnet and the Vnet doesnt allow whitelisting a range of public IPs (ADF region IPs)