Forum Discussion
Fabric - Mirroring SQLServer MI - Permissions
You need to set up the VNET Data Gateway in Azure, rather than on Windows. This gateway is an Azure resource that should be deployed within the same Virtual Network as your SQL Managed Instance. Its function is strictly for connectivity, so the service principal doesn’t require any permissions beyond those already assigned in Fabric and SQL MI.
Helpful Document: Microsoft Fabric Mirrored Databases From Azure SQL Managed Instance - Microsoft Fabric | Microsoft Learn
I hope this clarifies the situation. If I’ve misunderstood any part of your situation, please let us know.
Hi fabricpribeiro,
Happy to answer each of your questions based on the official Fabric Mirroring docs for Azure SQL Managed Instance:
1. Workspace identity or service principal?
Both are supported. For a non-human identity scenario (which is your case), a service principal is the recommended approach. Note that User Assigned Managed Identity (UAMI) is not supported so if you were considering that option, it won't work.
2. Fabric workspace permissions + capacity level?
The identity needs at minimum Member role in the Fabric workspace. No specific capacity-level permission is required beyond that workspace membership.
3. Fabric capacity F4 and F16 are fine?
Yes. Mirroring for Azure SQL MI works from F2 and above. Your F4 and F16 are both fully supported. There is no requirement for F64+.
One important note: if the Fabric capacity is paused or stopped, mirroring stops and must be manually restarted when the capacity is back up.
4. Same identity for Fabric workspace + SQL MI database?
Yes, the same service principal used for the Fabric workspace should also be the one granted permissions on the SQL MI database. That's the intended pattern.
5. Minimum SQL MI database permissions db_datareader enough?
Unfortunately db_datareader alone is not sufficient. Mirroring requires Change Data Capture (CDC) access, which needs more than just read permissions. The minimum recommended permission (following the principle of least privilege) is :
GRANT CONTROL ON DATABASE::[YourDatabaseName] TO [your-service-principal];
"Following the principle of least privilege, you should only grant CONTROL DATABASE permission in the database you intend to mirror." Microsoft Docs
This is broader than db_datareader, but you can scope it to the specific database being mirrored only.
References:
- Fabric Mirroring Azure SQL MI — Overview
- Tutorial: Configure Mirroring from Azure SQL MI
- Limitations Azure SQL MI Mirroring
Hope this helps!
thanks for your reply, I don't see anyware stating that workspace identity is allowed, where did you saw it? can you please send me the link?
About the permissions ? see that one : CONTROL DATABASE but seems additional ones are required, no? I mean in the SQL Server MI instance
Workspace identity isn't supported for SQL MI mirroring in Fabric only service principals are allowed, which is why workspace identity isn't mentioned in the documentation.
Additionally, db datareader permission alone isn't sufficient. Since mirroring uses CDC behind the scenes, the service principal needs at least the following permission
GRANT CONTROL ON DATABASE:[YourDatabaseName] TO [your-service-principal]This permission enables the necessary CDC and replication operations for mirroring. Typically, CONTROL DATABASE is enough, and it's best to grant this only on the specific database being mirrored.
Both F4 and F16 capacities are compatible for mirroring. You can use the same service principal in both.
Let me know if you need any further clarification.
Thank you, I will do Change feed, I suppose that when you say CDC, its because the change feed uses behind the scenes some CDC?
What about the permissions for the VNET gateway ? shall I use as well the same service prinicipal? and which permissions do I need to provide in the VNET gateway to this user for both, SQLMI and Fabric?
Yes, that's correct the change feed operates on top of CDC in the background. For the VNET gateway, you don't need to provide any special permissions like you would for SQL MI or Fabric; it's mainly used for connectivity.
You can use the same service principal, and there aren't any specific roles to assign on the gateway side. Just make sure the gateway is running and can connect to SQL MI, and your Fabric connection uses the same service principal. Permissions are only required at the workspace level and on the SQL MI database.
Hope this clarifies things.