Forum Discussion
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 credentials are used to access the Azure SQL DB. I login to Fabric using my normal user ID. I have created all these users and granted the permissions mentioned in the tutorial Document for all the users. SQL Admin, my Super user and the normal user credentials. But despite that I get the error:
The database cannot be mirrored to Fabric due to below error: 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
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,
- Connect to the Azure SQL logical server as a Server Admin or Azure Active Directory Admin.
- 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
This issue got resolved once I could grant the necessary permisions and after increasing the DTUs on the Azure SQL Database.
9 Replies
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,
- Connect to the Azure SQL logical server as a Server Admin or Azure Active Directory Admin.
- 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
This solution did not work for me. I am an Entra Admin on the SQL server, but I get "Securable class 'server' not supported in the server scope in this version of SQL Server." Should I add the login/user to a special fixed role, like db_manager instead? I'm not sure how to add a server permission on Azure SQL Database.
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.
- Using ##MS_ServerStateReader## server role:
- This role is a built-in role in SQL Server that grants the VIEW SERVER STATE permission.
- 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.
Please follow this article for applying all proper permissions:
1) At SQL server level
2) At Azure SQL Database level
https://learn.microsoft.com/en-us/fabric/database/mirrored-database/azure-sql-database-tutorial
Can you run that query/action in SSMS?
I have connected to SQL server using my super user credentials. No, when I run the GRANT VIEW SERVER STATE, I get the following error.
Additionally, I have also checked the permissions I have.
what's the server version? Did you maybe lower the compatibility level?
This issue got resolved once I could grant the necessary permisions and after increasing the DTUs on the Azure SQL Database.
Hello jsarali ,
It looks like your problem has been solved, please mark the helpful reply and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .
Thank you very much for your kind cooperation!
Regards,
B Manikanteswara Reddy
