Mirrored Azure SQL Database Won't Mirror

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.