topic Lakehouse Gen2 Datasource - Azure SQL DB cannot connect in Dataflow

Lakehouse Gen2 Datasource - Azure SQL DB cannot connect

dfkelso — Fri, 09 Jun 2023 03:12:31 GMT

I am using the Azure SQL Connector, but when I try with the Gen2 Dataflow I get the following error: "Connection could not be created. This may mean it is not accessible from this network or at this URI, or that a gateway is required to access this data source. (Session ID: c4977ea3-b822-601f-56fb-adcd91e54ba9, Region: us)". I use the same read-only account for other applications for access to the DB. The DB has firewall rules enabled and we must log approved inbound IP addresses in order to connect. I can create connections through PowerBI and other PowerPlatform applications and have approved the IPs for LogicApps, etc... but I cannot seem to locate the documentation for the IPs for these services.

Are there a list of inbound IP addresses that can be added to our firewall rule for this service?
NOTE: I cannot remove the firewall rules on this database, I can only add IP addresses to the approved inbound list.

Re: Lakehouse Gen2 Datasource - Azure SQL DB cannot connect

miguel — Fri, 26 May 2023 04:47:31 GMT

Hi!

You'll want to add the ip addresses for the PowerQueryOnline servicetag. Those are the ones used by Dataflows.

Hope this helps!

Re: Lakehouse Gen2 Datasource - Azure SQL DB cannot connect

HimanshuS-msft — Wed, 31 May 2023 23:24:34 GMT

Hello dfkelso ,
I was trying to repro the error which you encountered but I was not succesful in doing that .

But to me it does not appear to be IP issues as the message on the dataflow gen2 is very clear with that respect . I tried to read a table in my Lakehouse as sink and ASQL as source . I did not white listed the IP and also unchecked the option "Allow Azure Services and resources to access this server" on the ASQl side . Now the message which i got from the dataflow gen2 was

"An exception occurred: DataSource.Error: Microsoft SQL: Cannot open server 'myserver' requested by the login. Client with IP address 'My IP Address ' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect."

To me it looks like something else is wrong . Did you got an resolution to this ? If not what is the Authentication you selected while connected to the ASQL ?

Thanks
Himanshu

Re: Lakehouse Gen2 Datasource - Azure SQL DB cannot connect

dfkelso — Thu, 01 Jun 2023 00:16:42 GMT

i havent officially gotten a full resolution. i HAVE gotten past this error by making it connect through a gateway, but once i get past that the data does not ever publish. just throws a different, somehow more vague, error. i am working through these errors with msft support and will report back when i (hopefully) get it resolved.
in troubleshooting, i was able to set up a pipeline on the same sql database without the gateway that populated a table in the lakehouse ... so i am not really sure why or what the difference is.

unfortunately i dont control the azure sql server and cannot connect to the master db nor the azure sql tenant where this is hosted because it is a vendor hosted db that i have read-only rights to.

Re: Lakehouse Gen2 Datasource - Azure SQL DB cannot connect

HimanshuS-msft — Thu, 01 Jun 2023 00:28:19 GMT

Thanks dfkelso for the quick reply .
As a understand at this time the pipeline works for the ASQL but not the DF gen2 .
May I request you please post the resolution , so that its helpful to other community member . If you have the SR# , please do share the same here .

Thanks

Himanshu