FIX: CMG stuck on Deleting? Check SQL permissions for the public server role!
Have you ever had the need to delete a Cloud Management Gateway (CMG) deployment from ConfigMgr?
If so, did it work as intended? ?
I’ve personally come across a few issues on the Azure side of things, where resources wouldn’t get deleted. Most times that was due to granular permissions on Resource Groups and was fixed by asking the appropriate administrator to remove the resources.
This time though, the Azure resources did get removed. But the status of CMG in the ConfigMgr Admin console showed “Deleting”
First thought was to give it some time. And then some more. A weekend has passed, and the CMG still seems stuck on deleting.
What’s going on?
Well – TL;DR : it was due to the public Server Role missing “View server state” permissions on the SQL server.
Let’s dive in…
Note: screenshots have been redacted to mask names and attributes.
Here’s what the CMG shows in the admin console. FYI, we’re on Current Branch 1906 with Hotfix Rollup KB4517869 applied.
Now, let’s have a look at CloudMgr.log on the Site System that hosts the Service connection point role.
Note: you may find a CloudMgr.log file on other Site Systems as well, but they won’t have the same content. Make sure you know which server to look on!
Now, I repeated this a couple of times, and every time the log would state that the CMG service deployment had bee successfully deleted. Yet it remained in the console and in the database, which you can check by running this query:
SQL: select * from vAzure_Service
WQL: select * from SMS_AzureService
A quick search on the interwebs led me to a few different discussions, the following being the one that led me the nearest to the solution.
https://dba.stackexchange.com/questions/118452/error-message-view-server-state-permission-was-denied-when-connecting-to-sql
FIX
This is what I did to make it all work.
- Connect to the SQL server that hosts the ConfigMgr database, using SQL Server Management Studio
-
Grant VIEW SERVER STATE permissions to the Server Role public
Now try and delete the CMG once more and it should work.
Notice the difference is subtle. There’s no Exception 300 this time right after the service is trying to acquire the access token.
There you go. See, it’s not always ConfigMgr. Or Azure. Sometimes it’s SQL too ?
Hope it helped someone!