Sql Server: The Activity Monitor is unable to execute queries against server. Activity Monitor for this instance will be placed into a paused state. Use the context menu in the overview pane to resume the Activity Monitor. Access is denied.

Interesting one today:

We have a bunch of lab Sql Server boxes machines and sometimes after a fresh Sql Server install, when we try to open Activity Monitor, we run into this problem.

Error:

 

TITLE: Microsoft SQL Server Management Studio
 ------------------------------

The Activity Monitor is unable to execute queries against server DC2POLTPS02.
 Activity Monitor for this instance will be placed into a paused state.
 Use the context menu in the overview pane to resume the Activity Monitor.

Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)

------------------------------

Since these are lab machines, we are remotely logged into the machines and looks like there is some setting that prevents Activity Monitor from opening successfully. Activity Monitor provides great detail on what is going on with Sql Server at any given point-in-time and such activity needs “high level insight” into the Operating System and Sql Server; Such “high level” permissions are not enabled by default for user accounts.

Following steps show a way to enable elevated permissions when logged in remotely.  From what I could gather from Microsoft Connect this seems like elevated permissions on remote operating system’s DCOM. So we need to enable Remote Launch & Remote Activation permissions on remote Operating System (lab machine)

Resolution:

RDP to the remote machine and

1. Open Component Services (DCOMCNFG) from start menu
2. In the left hand tree, under Console Root, expand Component Services, expand Computers, right-click on My Computer and go to Properties
3. In My computer Properties window, go to COM Security tab.
4. In the Launch and Activation Permissions section, click on Edit Limits button.
   1. In the Security Limits tab, see if your user/group name exists. If not add to the list by clicking on Add button.
   2. Once user is added, highlight the user and make sure it has both Remote Launch & Remote Activation permissions checked.
5. In the Access Permissions section, click on Edit Limits button
   1. In the Security Limits tab, see if your user/group name exists. If not add to the list by clicking on Add button.
   2. Once user is added, highlight the user and make sure it has Remote Access permissions checked.
6. Hit Okay to save changes.
7. Now expand the My Computer in the left-hand tree and go to DCOM Config.
   1. Find Windows Management and Instrumentation and go to Properties.
   2. Go to Security tab and under Launch and Activation Permissions section, click on Edit button
   3. In the Security tab, see if your user/group name exists. If not add to the list by clicking on Add button.
   4. Once user is added, highlight the user and make sure it has both Remote Launch & Remote Activation permissions checked.
   5. (See the image below)
8. Save all changes and re-open Activity Monitor

Activity Monitor Error

 

 

 

Hope this helps,
_Sqltimes

Read Full Post »