There is an Azure VM with an Oracle DB installed. I need to access that DB using port 1521 from an SSIS package running on an Azure data factory SSIS runtime. The relevant SSIS DB is in an Azure SQL Server.
For the time being my nsg rule defined as below.
- Source - Any
- Source port range - *
- Destination - IP Addresses
- Dest IP - Private IP of the Azure VM with Oracle
- Service - Custom
- Dest Port - 1521
- Protocol - TCP
Since the source is defined as any there is a security risk associated with that.
I tried using
- Source - Service Tag
- Source Service Tag - DataFactory or sql.NorthEurope
and
- Source - IP address
- IP address - gateway IP range for North Europe
But all those give TNS timeout. Only the rule mentioned on top is working fine so far.
How can I define the source so that only the Azure SQL server or SSIS runtime will be able to access the Oracle DB on the VM? It seems like the Azure SQL server only has a fully qualified DNS name (FQDN) [xyz.database.windows.net] but not a public IP associated with that.
Check below link.
It says when using SQL Server Integration Services (SSIS) in Azure Data Factory, you should join your Azure-SSIS integration runtime (IR) to an Azure virtual network in the following scenarios:
You want to connect to on-premises data stores from SSIS packages that run on your Azure-SSIS IR without configuring or managing a self-hosted IR as proxy.
You want to host SSIS catalog database (SSISDB) in Azure SQL Database with IP firewall rules/virtual network service endpoints or in SQL Managed Instance with private endpoint.
You want to connect to Azure resources configured with virtual network service endpoints from SSIS packages that run on your Azure-SSIS IR.
You want to connect to data stores/resources configured with IP firewall rules from SSIS packages that run on your Azure-SSIS IR.
https://learn.microsoft.com/en-us/azure/data-factory/join-azure-ssis-integration-runtime-virtual-network#access-to-data-sources-protected-by-ip-firewall-rule