Forum Discussion

fabricpribeiro's avatar
fabricpribeiro
Post Patron
3 months ago
Solved

Fabric - Mirroring SQLServer MI - Permissions

Dears,

 

Can you please help me understand the permissions and identity I can use to do the mirrroing of SQLMI and Fabric?

 

1 - Can I use a workspace identity? or I need to use a service principal ?

2 - The Identity needs to be granted the minimum of Member in the Fabric workspace , what about at capacity Level?

3 - Do I need any specific Fabric capacity? My capacity is F16 in some cases and F4 in others , will it work? or does it require specific fabric capacity, for example, above F64? 

4 - Can I use the same identity from the point 2 to provide permissions on the SQL Server MI DB? so that the mirrring can happen, I believe it needs permissions on both, the workspace in Fabric but as well in SQLServer MI (the same user, right)?

5 - In terms of permissions to the SQLMI, can I provide DB_read? do they have a database role like this ? as it exists for example in SQL Server on-prem. I would prefer to provide this instead of giving very granular permissions as this is more staright forward and it will only be used by the indentity which will do the mirroring (non-human identity)

 

Thanks,

 

Pedro

 

Admin
Security
  • V-yubandi-msft's avatar
    V-yubandi-msft
    3 months ago

    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.

9 Replies

  • Tamanchu's avatar
    Tamanchu
    Super User
    3 months ago

    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:


    Hope this helps!

    • fabricpribeiro's avatar
      fabricpribeiro
      Post Patron
      3 months ago

      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

      • V-yubandi-msft's avatar
        V-yubandi-msft
        Community Support
        3 months ago

        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.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    3 months ago

    Hi fabricpribeiro ,

    We haven’t received any update from your side. Please let us know if you need any additional details or clarification.

     

    Thank you.

     

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    3 months ago

    Hi fabricpribeiro ,

    We haven’t received any response as of now. Kindly take some time to review our response, and please let us know if any additional details or clarification are required from our side.

     

    Thanks