Forum Discussion

jsarali's avatar
jsarali
Frequent Visitor
1 year ago
Solved

Mirrored Azure SQL DB - 'VIEW SERVER SECURITY STATE permission error

Hi, Using the MS tutorial I have followed all the steps. Despite that I get the following error (the tables load): The Server admin of the Azure SQL DB is Admin Object/App ID. My super user c...
admin
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hello jsarali ,

     

    Thank you for reaching out to Microsoft Fabric Community Forum.

    lbendlin Thank you for your quick response.

     

    When configuring Azure SQL Database mirroring to Microsoft Fabric, the following error may occur:

    "Unable to retrieve SQL Server managed identities. VIEW SERVER SECURITY STATE permission was denied on object 'server', database 'master'. The user does not have permission to perform this action."
     

    This error is caused by missing server-level permissions. Even if the user has full database-level access, Microsoft Fabric requires visibility into server security metadata to complete the mirroring process.

    You can try,

    1. Connect to the Azure SQL logical server as a Server Admin or Azure Active Directory Admin.
    2. Grant the required permissions to the user or service principal accessing the database from Fabric:

     

    GRANT VIEW SERVER STATE TO [UserOrServicePrincipal];
    GRANT VIEW SERVER SECURITY STATE TO [UserOrServicePrincipal];

    • These permissions must be granted at the server level, not just at the database level.
    • If permission cannot be granted directly, ensure the user is configured as an Azure AD Admin on the SQL Server.

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

    Regards,

    B Manikanteswara Reddy

  • jsarali's avatar
    jsarali
    1 year ago

    This issue got resolved once I could grant the necessary permisions and after increasing the DTUs on the Azure SQL Database.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hello jsarali ,

 

Thank you for reaching out to Microsoft Fabric Community Forum.

lbendlin Thank you for your quick response.

 

When configuring Azure SQL Database mirroring to Microsoft Fabric, the following error may occur:

"Unable to retrieve SQL Server managed identities. VIEW SERVER SECURITY STATE permission was denied on object 'server', database 'master'. The user does not have permission to perform this action."
 

This error is caused by missing server-level permissions. Even if the user has full database-level access, Microsoft Fabric requires visibility into server security metadata to complete the mirroring process.

You can try,

  1. Connect to the Azure SQL logical server as a Server Admin or Azure Active Directory Admin.
  2. Grant the required permissions to the user or service principal accessing the database from Fabric:

 

GRANT VIEW SERVER STATE TO [UserOrServicePrincipal];
GRANT VIEW SERVER SECURITY STATE TO [UserOrServicePrincipal];

  • These permissions must be granted at the server level, not just at the database level.
  • If permission cannot be granted directly, ensure the user is configured as an Azure AD Admin on the SQL Server.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

hdobaosolis's avatar
hdobaosolis
Regular Visitor
1 year ago

I got this error when I try to execute above scripts in an Azure SQL DB:

 

Msg 40521, Level 16, State 1, Line 1
Securable class 'server' not supported in the server scope in this version of SQL Server.

 

After having a look and poll this to an IA, the following different approach (on role based) has to be taken: 

 

Replace <Your UAMI Name> with the name of your User Assigned Managed Identity. 

 

 
  • Then grant the VIEW SERVER STATE permission as described above.
  1. Using ##MS_ServerStateReader## server role:
    • This role is a built-in role in SQL Server that grants the VIEW SERVER STATE permission. 
       

 

  • Connect to the SQL Server as a user with sufficient privileges (e.g., sysadmin). 
     
  • Run the following query:
        ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [username];
Replace [username] with the actual username or service principal. 
 
 
Important Considerations:
  • Azure SQL Database:
    On Azure SQL Database, VIEW SERVER STATE is not directly supported. You need to use the ##MS_ServerStateReader## server role or ensure your database is on a service tier that allows this permission.