question

SubashBasnayake-6433 avatar image
0 Votes"
SubashBasnayake-6433 asked SumanthMarigowda-MSFT commented

How to define a inbound security rule in a NSG defining source as an Azure sql server

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.

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.

azure-sql-databaseazure-virtual-network
· 2
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,
Since your question is more related to the AZURE, i removed the Server security tag to get more professional advice.
Best Regards,

1 Vote 1 ·

Thank you.!

0 Votes 0 ·
SaiKishor-MSFT avatar image
1 Vote"
SaiKishor-MSFT answered BattersonEctonRoccoWCOO07000-9382 commented

@SubashBasnayake-6433

Thank you for reaching out to Microsoft Q&A. If I understand your requirement, you need to access an Azure VM with an Oracle DB installed in it from an SSIS package running on an Azure data factory SSIS runtime and want to know how you can setup your NSGs to do the same and not allow all traffic.

There are two alternative ways to do this:

  1. When you create Azure-SSIS IR, you can bring your own public IP addresses and specify them via Data Factory UI or SDK. Only the outbound internet connectivity of Azure-SSIS IR will use your provided public IP addresses and other devices in the subnet will not use them.

  2. You can also setup Virtual Network NAT for the subnet that Azure-SSIS IR will join and all outbound connectivity in this subnet will use your specified public IP addresses.

More details regarding is mentioned in this document here that talks about Access to data sources protected by IP firewall rule

Hope this helps. Please let me know if you haver any further question and we will be glad to assist further. Thank you!

· 5
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 @SaiKishor-MSFT,
Thank you for your answer.
Actually, I found this article and tried setting it up as described in that doc. I tried option one with 2 public IP addresses when configuring the SSIS-IR and it worked. Now my nsg rule for port 1521 in the VM is only opened up for those 2 IP addresses.

I have another question about these 2 public IPs, It is mentioned that "Only the outbound internet connectivity of Azure-SSIS IR will use your provided public IP addresses".
So does that mean even though these are public IPs it won't allow any inbound traffic to the Azure SSIS-IR?
There isn't any risk of exposing the SSIS-IR with a public IP?
I would like to know more about the security aspects of using public IPs in this scenario.

0 Votes 0 ·

@SubashBasnayake-6433 I am checking on this with a DB expert. I will get back to you soon with more information. Thank you!

1 Vote 1 ·

@SubashBasnayake-6433 Apologize for the delay in response while I was investigating further. The Public IPs are only used for outbound purposes and will not allow inbound traffic to the SSIS-IR. Hope this helps. If you still have any further concerns, please let us know. Thank you!

1 Vote 1 ·
Show more comments
AndreasBaumgarten avatar image
0 Votes"
AndreasBaumgarten answered SubashBasnayake-6433 commented

Hi @SubashBasnayake-6433 ,

you could maybe use the Azure Service Tags in your NSG rule.


94869-image.png


(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten



image.png (43.1 KiB)
· 1
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, @AndreasBaumgarten tried creating this rule and it gives me an ORA-12170: TNS connect timeout occurred.
This was the same error I am getting when I use,

  • the service tag as sql.NorthEurope (location of the SQL server is North EU)

  • Source IP range as the gateway IPs of North Europe region

So I am also a bit confused about the originating source.



0 Votes 0 ·