Home Dashboard Directory Help
Search

After installing SP1 SQL Server 2012 Activity monitor gives Access denied. by CERWK


Status: 

Closed
 as By Design Help for as By Design


10
1
Sign in
to vote
Type: Bug
ID: 770897
Opened: 11/13/2012 7:50:09 AM
Access Restriction: Public
0
Workaround(s)
view
9
User(s) can reproduce this bug

Description

Yesterday I was able to use Activity monitor in 2012 management studio to view Activity on SQL Server 2008 R2. I installed 2012 SP 1 now I get access denied on the Servers that I connect to with domain authentication. Servers connected to with SQL Server authentication still work, and 2005 management studio Activity monitor still works. Other users who have not installed SP1 are still able to run activity monitor through 2012 management studio. I can connect to the servers, and run queries on user tables, just cannot run Activity Monitor. Does not make a difference if I run management studio as administrator, same results.
Details
Sign in to post a comment.
Posted by olpaq on 11/6/2013 at 12:19 PM
Same as Ed888, PJW_79 and OptimumSolutions.
My OS is Windows 2008 R2 SP1 and the DCOM permission is not working.
Posted by OptimumSolutions on 10/16/2013 at 2:46 PM
I have been struggling with this same issue for a while now. I also changed the DCOM config settings to no avail. However, there seems to be a strange workaround for this that I just stumbled across. Open the Activity Monitor in MSSMS. You'll get the error, but just ignore it. Click on the "Properties Window (F4)" button at the top right-hand corner of the screen. (This is an icon that looks like a hand pointing at a piece of paper.) This will open the Properties Window. Now, try to expand the "Processes" section of the Activity Monitor area. It may take a few seconds, but the processes were listed for me and my coworkers. Then, we were able to close the Properties Window and proceed. I have no idea why this works, but it gave me what I needed. Maybe it will help someone here, too. At least until there's a proper fix.
Posted by PJW_79 on 10/16/2013 at 6:19 AM
Same as @Ed888 I still see this problem even after DCOM config and verifying DB security. Have a Server 2008 R2 running SQL 2012 SP1. Local admin group and domain admin will work. So I must concur that it is still some kind of permission issue.
Posted by SQLCHANTER on 8/8/2013 at 10:33 AM
I am accessing the instance on the Server host. Even then I get this error... Is there a fix to this yet ? any CU patch ?
Posted by Ed888 on 7/25/2013 at 10:28 AM
Still see this problem even after DCOM config and verifying DB security. Have a Server 2003 R2 running SQL 2008 R2, the developer still gets access denied. Another, Server 2008 R2 running SQL 2008 R2 also gets access denied. I have verified DCOM is correct on both systems, and the developer has required DB permissions. Interestingly, if I add the developer to the local admin group, or domain admin it will work. So still some kind of permission issue, but I have not nailed it down yet and making the developer an admin is not an option.
Posted by pen_2 on 6/26/2013 at 2:15 AM
Also if steps that adding DCOM permission are the only way to fix this please update your documentation http://msdn.microsoft.com/en-us/library/ms175518.aspx
Posted by pen_2 on 6/26/2013 at 1:47 AM
I've meant not SMO 2012 but SSMS 2012
Posted by pen_2 on 6/26/2013 at 1:45 AM
"upgrade to more recent OS" what do you mean by that? I'm getting access denied connecting from SMO 2012 running on WIN8 to SQL 2012 running on WIN2008...
Posted by woodsy99 on 5/16/2013 at 1:54 AM
Im getting the Access Denied error on a Windows 2008 R2 machine using SQL 2012 Sp1, connecting from another domain using SQL authentication. It was working fine for months even with the Service Pack but has just started to block access from my management studio on my desktop
Posted by Microsoft on 2/1/2013 at 7:44 AM
If you’re getting Access Denied – unfortunately the only way is to either enable DCOM permissions as it was suggested in the workaround, or to upgrade to more recent OS. Windows Server 2003 is out of regular support cycle anyway (http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=windows+server&Filter=FilterNO)

If you’re getting “Invalid Query” error message in the same configuration – I’d recommend look at this hotfix, since it addresses the issue: http://support.microsoft.com/kb/932370

Vladimir Svidersky [MS SQL SERVER]
Posted by CERWK on 12/13/2012 at 6:25 AM
And you didn't list this as a breaking change when upgrading to sp1 in the docs??
Posted by Microsoft on 12/13/2012 at 6:12 AM
If you are remotely accessing to a server, 2012 SP1 requires additional permissions. The user needs to have remote access to the computer through DCOM.
Posted by Kevin Eckart on 11/28/2012 at 8:33 AM
I am also having this issue.
Posted by CERWK on 11/21/2012 at 8:00 AM
Could DCOM rights on the server for my user have changed due to me installing SQL server 2012 sp1 on my workstation? Or does 2012 sp1 require additional DCOM rights that were not necessary before sp1 was installed? This worked prior to installation of sp1.
Posted by Microsoft on 11/20/2012 at 3:48 PM
Hi,

Are you remotely accesing to a server? This issue could have occured if you don't have remote access to the computer through DCOM. Typically, DCOM errors occur when connecting to a remote computer with a different operating system version. If this is the case, please try give the user Remote Launch and Remote Activation permissions.

Please follow the steps:

1. Click Start, click Run, type DCOMCNFG, and then click OK.
2. In the Component Services dialog box, expand Component Services, expand Computers, and then right-click My Computer and click Properties.
3. In the My Computer Properties dialog box, click the COM Security tab.
4. Under Launch and Activation Permissions, click Edit Limits.
5. In the Launch Permission dialog box, select your user and group in the Group or user names box. In the Allow column under Permissions for User, select Remote Launch and select Remote Activation, and then click OK.
6. Under Access Permissions, click Edit Limits and give Remote Access to your user.
7. Go to DCOM Config(Expand My Computer), find "Windows Management Instrumentation", right-click and select Properties
8. In the Security tab, click on Edit under Launch and Activation Permissions, and give your user Remote Launch and Remote Activation.

For more information, see Securing a Remote WMI Connection
http://msdn.microsoft.com/en-us/library/windows/desktop/aa393266(v=vs.85).aspx
Posted by Microsoft on 11/14/2012 at 1:11 PM
Thank you for reporting this issue - we are investigating and will update you when we have more information.

Thanks,

Alex Grach [MSFT SQL SERVER]
Sign in to post a workaround.