question

PhilipORourke-7866 avatar image
0 Votes"
PhilipORourke-7866 asked PhilipORourke-7866 commented

ip addressess and ports which require to be open to allow write from self hosted integration service directly to azure sql database

Hi,

We are able to write data to azure sql database from an on-premise oracle database by using self hosted integration runtime and using azure data factory.

The self hosted integration runtime software is installed and running on a virtual machine within on-premise network which already has some communication to azure to read power bi datasets (i.e. port 443).

We therefore have dataflow which reads from on-premise oracle to azure blob storage (i.e. uses port 443) then read the contents of azure blob storage into an azure sql database (i.e. uses port 1433 within azure network).

What we would like to do is write directly from on-premise oracle to azure sql database using azure data factory without having the intermediate staging step.

If we allow our machine running the self hosted integration runtime to access azure on ports 1433,1434,14000-14999)

for uk south region microsoft public ip addresses would that be enough.

Is there anything else that would be required to allow this to happen (e.g. other i.p. addresses or ports)

uk south microsoft public ip addresses
51.140.184.11
, 51.105.64.0
, 51.140.144.36
, 51.105.72.32
51.105.64.32/29
, 51.105.72.32/29
, 51.140.144.32/29

Thanks

azure-data-factory
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @philiporourke-7866,

Thanks for using Microsoft Q&A !!
If I understand correctly, you are trying to copy data from on-premise oracle to azure sql database using Dataflow.
I believe you are configuring everything required to connect your SHIR to Azure including the ports but can you check again if you have configured everything correctly as mentioned over Ports and firewalls section of the documentation.
Also, could you please let us know if you are getting any errors while pulling the data?

Thanks
Saurabh

0 Votes 0 ·

Hi SaurabhSharma,

Thanks for reply.

Your understanding is correct.

Tried to run and got message below when tried to pull data direct from oracle source to azure sink.

Thanks

197094-image.png


0 Votes 0 ·
image.png (54.6 KiB)

Hi @philiporourke-7866,

Can you please check if the network of oracle and Azure SQL DB are two different networks? If they are in different, then there is no way to copy data directly. The only option is to copy data from Oracle to a staging blob and then copy the blob to Azure SQL DB.

Thanks
Saurabh

0 Votes 0 ·

Hi Saurabh,

The oracle database resides on company on premise network. I was under the impression that the purpose of the self-hosted integration runtime was to act as a ridge between the on-premise network and the azure cloud network hosting the azure sql database.

The copy from oracle to blob then blob to azure works as uses port 443 as I understand it.

Is it not the case if just open port 1433 then the intermediate step of copying to blob wouldn't be required and could simply copy direct from oracle to azure?

Thanks

0 Votes 0 ·

Hi @philiporourke-7866,

I believe you are correct and opening port 1433 would work in case of copying data from on-premise to Azure SQL Database. Can you please check if you have allowed outbound communication on port 1433 for both Windows firewall and your corporate firewall ?
Also, you need to add your SHIR hosted machine IP address to the firewall settings of the SQL Database, if that works?

Thanks
Saurabh


1 Vote 1 ·

Hi Saurabh,

Thanks for reply.

I have asked the server team to ensure they open port 1433 as looks like they haven't completed this as still cannot telnet from the sihr server to the uk south microsoft public ip addresses on port 1433.

The shir has been added to the firewall settings on the sql database previously.

I'll let you know once server team have ensured they have opened port 1433 on both the Windows firewall and your corporate firewall

Thanks

0 Votes 0 ·

0 Answers