Getting data/ mirroring from SQL Managed Instance to Fabrics

[subashmahat]

Hello I am trying to get data through a copy activity from Azure SQL Managed instance in Fabrics

However everytime i try to do this i am face with this error:
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. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
This is the same for mirroring database in fabrics. 
We have a SQL managed instance with SQL engine at SQL server 2022. We do not have always up to date. I use public endpoint to make the connection. 
Power BI lets me connect data with no issues for the same sources. 
Please kindly assist me.

[V-yubandi-msft]

Hi @subashmahat ,

Thank you for reaching out to the Fabric Community. @dlauth33 , has explained the key points very well.

If you’re using Mirroring with Azure SQL Managed Instance, you need to enable the  Always up to date update policy, as mentioned in the Microsoft documentation.

However, since you’re working with a Copy Activity, this setting isn’t required. As @dlauth33,  rightly pointed out, it's important to check your network configurations, especially ensuring that traffic is allowed on the required ports.  If you’ve already allowed traffic but are still encountering issues, please let us know. We’re happy to help.

Thanks to @dlauth33   for the valuable insights.

Helpful reference:Limitations in Mirrored Databases From Azure SQL Managed Instance (Preview) - Microsoft Fabric | Mic...

— Yugandhar
Community Support Team.

[dlauth33]

you mentioned in your first comment that you didn't have "always up to date" turned on.  That's a requirement for mirroring a managed instance.  If you just want to use a copy activity you just just need to turn on the public endpoint and whitelist the ip gateways for Fabric for your region in the network security group setting...allowing traffic to port 3342 (which the public endpoint uses)

[subashmahat]

I whitelisted the ip with servicetag servicefabric. it still does not allow me to connect to azure managed instance

[dlauth33]

I'm pretty sure I used the gateway ip's for power bi and not servicefabric. Try that.  ServiceFabric is not the same product as Fabric which is pretty confusing.  I didn't remember there being an entry in the IP Gateway for only "Fabric".

[V-yubandi-msft]

Hi @subashmahat,

Thank you for confirming that you've whitelisted the IPs using the ServiceFabric service tag.

However, in some regions, using only the ServiceFabric tag may not be sufficient to establish a connection with Azure SQL Managed Instance.

We recommend verifying that port 3342 is open in your Network Security Group  to allow inbound traffic for the public endpoint.

Also, please make sure that the public endpoint is enabled for your Azure SQL Managed Instance.

If the issue persists, you may need to manually whitelist the outbound IP ranges used by Microsoft Fabric for your region.

Helpful Reference:

Troubleshoot Common Connection Issues - Azure SQL & SQL database in Fabric | Microsoft Learn

Solved: Connecting to Azure SQL Database (Whitelisting IPs... - Microsoft Fabric Community

Solved: Re: Whitelisting of PowerBI IP address range in Fi... - Microsoft Fabric Community

— Yugandhar
Community Support Team.

[dlauth33]

Here's a link to the prerequisites:
https://learn.microsoft.com/en-us/fabric/database/mirrored-database/azure-sql-managed-instance-tutor...

Always up to date is a requirement to mirror a managed instance.  There are also some changes to be made to the SAMI on the source SQL managed instance.  

[subashmahat]

I did go through this. Is there a way that I could use copy activity directly with managed instance.