1

I have an Azure Postgres database, and am looking to query it in an Azure Data Factory pipeline. When I go to add the database as a linked service, I see it fails as the IP address is not whitelisted. Looking at https://learn.microsoft.com/en-us/azure/virtual-network/service-tags-overview#discover-service-tags-by-using-downloadable-json-files , I see that there's a whole lot of IPs in the region for Sql. I was wondering if there's some other way to allow Data Factory to read from the postgres database without enabling the button that allows all subscriptions (not even mine) network access to the database. Ideally, I'd like to say "allow all connections from this azure subscription". Is this doable?

ashic
  • 6,367
  • 5
  • 33
  • 54

1 Answers1

0

You could try add the access role for the Data Factory: enter image description here

Grant the Data Factory one of bellow roles:

  • Contributor: Grants full access to manage all resources, but does not allow you to assign roles in Azure RBAC.
  • Reader: View all resources, but does not allow you to make any changes.

usually, there are only the tree ways to control the access: 1. add IP roles, 2. Allow access Azure. 3.Access control(IAM). If the IAM doesn't work, we only can add the IP or allow access from Azure.

For all the Azure Database(as I know), the only thing we can set to achieve your request is "Allow access to Azure Service":

enter image description here

HTH.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Is there a standard user or principal that data factory uses? Where does dfleon come from above? Or can I specify a user that the data factory will run as? – ashic Oct 01 '20 at 10:13
  • @ashic of course you can. You can select your AD user for the Postgre database. dfleon is my data factory name and just an example. – Leon Yue Oct 01 '20 at 12:10
  • So when I go to add an Azure Database for Postgres linked service, I need to specify the database, username, password, port, etc. When I go to test connection, I see that it complains about the ip not being in the postgres db's allowed list. That's the issue - the ip rule. – ashic Oct 01 '20 at 12:28
  • @ashic I know your meaning, that why I want you test add the data factory role fro the postgres database. That's one of the way. If it doesn't work, we would say it's impossible for your question. – Leon Yue Oct 01 '20 at 14:53
  • Hi @ashic, did you get any progresses now? – Leon Yue Oct 02 '20 at 01:27
  • No... didn't work... can add the individual ips or allow access from any azure sub. – ashic Oct 02 '20 at 15:37
  • @ashic, usually, there are only the tree ways to control the access: 1. add IP roles, 2. Allow access Azure. 3.Access control(IAM). If the IAM doesn't work, we only can add the IP or allow access from Azure. – Leon Yue Oct 03 '20 at 00:37
  • @ashic Did you set "Allow access to Azure Service" to YES ? – Leon Yue Oct 03 '20 at 00:44
  • I ended up doing that, but would have preferred it to be limited to only our subscription. I guess that's not possible. – ashic Oct 16 '20 at 07:27
  • Sorry to hear that. Thanks for marking and have a good day! – Leon Yue Oct 16 '20 at 07:54