Connect FDF To Azure SQLMI

[IanKeogh]

Hi, So I am totally new to FDF (Fabric Data Factory). I am using app.fabric.microsoft.com to access Fabric Data Factory. I am creating a pipline, I am able to connect to Azure blob and injest my flat file, my problem is, I want the destination to be my Azure SQL Managed Instance. but when i try to connect to it from FDF, "An exception occurred: DataSource.Error: Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible"

Now I am pretty sure I just need to create an endpoint on SQL, wether that be Private or Public, and with app.fabric.microsoft.com not being in Azure not sure what to do? Can someone point me in the right direction PLEASE. 😀

Thanks in advance,

Ian

[GarryH]

To connect to a SQL managed instance that is on a public IP address, you will need to add a firewall allow rule on the network security group attached to the SQL server's subnet. For connections from Fabric data factory, you would need to add an allow rule with a source service tag of 'DataFactory'. You would also need to allow inbound port 3342. Then connect to the server on its public address, which you can copy from the server's connection strings page, and will look something like 'tcp:<yourserver>.public.abcdefgh1234.database.windows.net,3342'.

[IanKeogh]

"You would need to access the SQL MI via a data gateway" - Thank you so much. I beleive i already have one setup for PowerBI, so i should be able to leverage that.

Thanks again.

[NandanHegde · Accepted solution]

Hey,

The reason why you are able to access blob without issue is because it is not encassed within any firewall.

Whereas in case of SQL MI, the database is within a VNET; so fabric is unable to access it directly.

You would need to access the SQL MI via a data gateway

[AmadouBari]

Hi Nandan - why would i need a gateway to access the MI from a SAAS Fabric? I am not on-prem and they are in the same tenant.

[NandanHegde]

A gateway act as a bridge between Fabric and any source which is hosted within a VNet.

Note : The tenant doesnt ideally matter in decision whether to use a gateway or not.

As the SQL MI is within a VNet, Fabric wont be able to directly access objects within the VNet because of which you would need a gateway

[AmadouBari]

Thank you, Nandan. In case of just mirroring the MI into fabric, i am not even getting an option to select a gateway.