Mirrored Azure SQL Database Won't Mirror

[mattke2b]

We're attempting to setup a Mirrored Azure SQL Database for some testing and so far we've found it impossible. We use VNet Gateways pretty much exclusively for our data connections, and we use a service principal for the connection, this has been working for months at this point just fine. Reading through the documentation I saw that we needed to give it the ##MS_ServerStateReader## role which I did and SQL Server reports that it was done succesfully. Looking at the properties of the service principal login it also shows that the role was applied succesfully.

However, when attempting to actually use the Mirror Database feature I get an error of:

Unable to retrieve SQL Server managed identities. A database operation failed with the following error: 'VIEW SERVER SECURITY STATE permission was denied on object 'server', database 'master'.

The user does not have permission to perform this action.'

VIEW SERVER SECURITY STATE permission was denied on object 'server', database 'master'.

The user does not have permission to perform this action., SqlErrorNumber=300,Class=14,State=1,

 I've validated that the SQL server has a System Assigned Identity, and that it's the primary:

And from my user when checking I see:

After running:

SELECT member.principal_id AS MemberPrincipalID,
    member.name AS MemberPrincipalName,
    roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS member
    ON server_role_members.member_principal_id = member.principal_id
LEFT JOIN sys.sql_logins AS sql_logins
    ON server_role_members.member_principal_id = sql_logins.principal_id
WHERE member.principal_id NOT IN (
    -- prevent SQL Logins from interfering with resultset
    SELECT principal_id
    FROM sys.sql_logins AS sql_logins
    WHERE member.principal_id = sql_logins.principal_id
);

Doing some reasearch and so far it seems like no one has gotten service principals working with this feature? Is this even supported?

[v-nmadadi-msft]

Hi @mattke2b 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks 

[v-nmadadi-msft]

Hi @mattke2b 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[burakkaragoz]

Hi @mattke2b ,

Thanks for the detailed breakdown—this is a tricky one. Based on the error and your setup, it looks like the service principal lacks the VIEW SERVER SECURITY STATE permission, which is required for the Mirror Database feature to retrieve SQL Server managed identities.

Here's what you can try:

---

✅ Grant the Missing Permission Explicitly

Even though the service principal is in the ##MS_ServerStateReader## role, that role does not include VIEW SERVER SECURITY STATE by default in Azure SQL. You can try granting it directly:

GRANT VIEW SERVER STATE TO [your-service-principal-name];

⚠️ Note: You’ll need to run this as a server-level principal (e.g., an Azure AD admin or SQL Server admin).

---

❗ Important Notes:

• Azure SQL has limited support for server-level permissions when using service principals.
• Some features (like Mirroring or Managed Identity discovery) may require interactive user context or Azure AD user identities instead of service principals.
• If this is a blocker, consider testing with a user-assigned managed identity or Azure AD user to confirm whether the issue is specific to service principals.

---

🧪 Alternative Approach:

If granting VIEW SERVER STATE doesn’t work or isn’t allowed in your environment, you might consider:

• Using a hybrid approach: perform the mirroring setup using an Azure AD user with elevated permissions, then switch back to the service principal for regular operations.
• Raising a support ticket with Microsoft to confirm whether service principals are officially supported for this specific feature.

Let me know if you want help drafting that support request or testing with a different identity type.

[mattke2b]

This database/Azure SQL Server has only ever had SAMI, I tried turning it off, and then back on, with no success. SQL based users work fine, and an Entra ID User works, but an Entra Service Principal auth does not.

[v-nmadadi-msft · Accepted solution]

Hi @mattke2b ,

If you have verified everything related to SAMI and is functioning as expected Please try the below steps now

Do not remove Azure SQL Database service principal name (SPN) contributor permissions on Fabric mirrored database item.

If you accidentally remove the SPN permission, Mirroring Azure SQL Database will not function as expected. No new data can be mirrored from the source database.

If you remove Azure SQL Database SPN permissions or permissions are not set up correctly, use the following steps.

1. Add the SPN as a user by selecting the ... ellipses option on the mirrored database item.
2. Select the Manage Permissions option.
3. Enter the name of the Azure SQL Database logical server name. Provide Read and Write permissions.
   
   

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[v-nmadadi-msft]

Hi @mattke2b 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft]

Hi @mattke2b  ,
Thanks for reaching out to the Microsoft fabric community forum.

After enablement, if SAMI setting status is either turned Off or initially enabled, then disabled, and then enabled again, the mirroring of Azure SQL Database to Fabric OneLake will fail.

The SAMI must be the primary identity. Verify the SAMI is the primary identity with the following: SELECT * FROM sys.dm_server_managed_identities;

User Assigned Managed Identity (UAMI) is not supported. If you add a UAMI, it becomes the primary identity, replacing the SAMI as primary. This causes replication to fail. To resolve:

Remove all UAMIs. Verify that the SAMI is enabled.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[nilendraFabric]

Hi @mattke2b 

Ensure SAMI is enabled and set as the primary identity for the Azure SQL logical server

Remove all User-Assigned Managed Identities (UAMI) if present, as they override SAMI as the primary identity