Solved

SQL Server Reporting Services Access Denied from Management Studio

Posted on 2012-03-20
17
2,605 Views
Last Modified: 2012-03-26
We are running SQL Server 2005 with reporting services (not Sharepoint integrated) and I am trying to connect to the reporting services with SQL Server Management Studio 2008 client.  Whenever I try and connect, I get the following error:
TITLE: Edit Server Registration Properties
------------------------------

Testing the registered server failed. Verify the server name, login credentials, and database, and then click Test again.

------------------------------
ADDITIONAL INFORMATION:

A connection to the computer cannot be established. (Microsoft.SqlServer.Management.UI.RSClient)

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

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

------------------------------
BUTTONS:

OK
------------------------------

Open in new window


I can connect to the http://<server>/Reports site and I have assigned the Domain Admins group (I am a domain admin) to the System Administrator role and I have also given my own login account the Content Manager role.

The reporting server is installed using the default instance so I am connecting to it with just the server name.  I have tried various URLs instead of the server name but none work.

How can I connect from the management studio?
0
Comment
Question by:CIPortAuthority
  • 9
  • 8
17 Comments
 
LVL 6

Accepted Solution

by:
SJCFL-Admin earned 500 total points
Comment Utility
I've added an attanchment to help you find your reporting instance if the problem is as simple as misunderstanding the name that is was created under.  It is usually different than a normal SQL instance and can take a little getting used to.
Finding-a-Reporting-Instance.docx
0
 

Author Comment

by:CIPortAuthority
Comment Utility
I should have mentioned that I am trying to connect from my Windows 7 workstation running the SQL Server Management Studio 2008 software.  The server is a SQL Server 2005 machine.

Your suggestion works if connecting from the local server but it does not work when I try and connect from my workstation.  But the connection name is just the server name ('KAI') itself.  There are no firewalls between my workstation and the server.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
what happens when you just try to ping the server using a command window?
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
And even more to the point, you have enabled remote acces on the server, right?
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
On the local server,  open SQL Server 2008 Management Studio, open the Server Properties (by right clicking) Navigate to Connections and ensure that Allow remote connections to this server is checked...
0
 

Author Comment

by:CIPortAuthority
Comment Utility
I can connect to the database engine remotely without a problem.  I can also connect to the reporting services website (http://<server>/Reports) without a problem.  It is only the connection to the reporting services from Management Studio that is giving me the access denied error.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
We may be talking apples and oranges.  There are 4 connections involved here. And the connection to reporting services would not normally just be KAI.  That would be the databse engine conenction and not the Reporting services engine. When you set up reporting services, sql should create a second instance, normally mssqlserver.  If you display the services on the machine you should see it.  A summary of the connections using KAI and standard defaults that I use would be:

Normal instance (SSMS)
(Connect Database Engine)
KAI

Reporting instance (SSMS)
(Connect Reporting Services)
KAI\mssqlserver

Web Management (file oriented)
http://KAI/reportserver

Web SQL Server Reporting Services
http://KAI/reports

Each of the four should be tried locally first and then remotely.  A sucessful ping against KAI tells you that you can get accross the network and the it recognizes the name KAI.  All these steps help pinpoint the problem.
0
 

Author Comment

by:CIPortAuthority
Comment Utility
OK...  On the server itself, I can connect to the database engine, analysis server, reporting services, and integration services all by using the server name "KAI".  

On my workstation, I can ping the server, I can connect to both http://KAI/Reports and http://KAI/ReportServer and I can connect to the database engine.  I cannot connect with SSMS to the reporting service using either "KAI" or "KAI\mssqlserver".  Out of curiosity, I tried connecting to the integration services as well from SSMS on my machine and couldn't do that either.  I got a different message in case the two issues are related:
TITLE: Connect to Server
------------------------------

Cannot connect to KAI.

------------------------------
ADDITIONAL INFORMATION:

Connecting to the Integration Services service on the computer "KAI" failed with the following error: "Access is denied.
".

This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.


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

Connecting to the Integration Services service on the computer "KAI" failed with the following error: "Access is denied.
".

This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.


------------------------------
BUTTONS:

OK
------------------------------

Open in new window

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
It does sound like DCOM may be your problem.  Here is a write up that describes administering a Report server remotely.

http://msdn.microsoft.com/en-us/library/ms365170.aspx

And this goes over administering integration services.

http://newlook.thejustordinarygroup.com/2010/01/giving-permissions-to-connect-to-sql-server-integration-services-ssis/

As a work around, there is the old standby that i tend to default to.  that is remoting to the report Server itself and then bringing up the local copy of Sql Studio while you work out the kinks in the remote management....
0
 

Author Comment

by:CIPortAuthority
Comment Utility
I am looking in to this today.  Sorry for the delay.
0
 

Author Comment

by:CIPortAuthority
Comment Utility
Well, I've followed all of the instructions on both pages and I still cannot remotely access Reporting Services using SSMS.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
At this point, when you log directly into the Server and look at the machine logs, what do the errors look like?  (That is, does it look like you are making it to the machine and sql server is rejecting you or are the errors coming from the windows server rejecting you?)
0
 

Author Comment

by:CIPortAuthority
Comment Utility
I do not see any errors in either the event log (application, system or security) or the SQL Server error log.  The server is setup to audit all failures to the event log.  I don't see anything in the WWW log file.  I don't see anything in my local event log either.

The access denied error pops up immediately without any sort of pause so I'm wondering if it's something with the 2008 R2 version of SSMS having an issue connecting to 2005 reporting services.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
It is as if it does not "see" it at all.  If it did, you would have gotten an error somewhere. And since you can connect to other instances on that box, I am tending to agree with your analysis.  Is it possible to load 2005 on the remote client to check it out?  Or will you be upgrading to 2008 on the server shortly and rather wait for them to come into alignment since you can always remote to the box and bring it up locally if you need to...
0
 

Author Comment

by:CIPortAuthority
Comment Utility
I installed the SQL Server Management Studio 2005 client to a virtual machine and tried to connect but it wouldn't connect either.  But, this time I got a different error message and when I looked at the details, I noticed that it was failing in the server name parsing.  So I started plugging in a bunch of different server names and finally got it to work!

I'm not sure if you told me to try this and I misunderstood, but the server name that worked was: http://kai/reportserver

I'm not sure why it works locally as just KAI but requires a URL remotely, but its working now so I'm happy.  Thanks for the help!
0
 

Author Closing Comment

by:CIPortAuthority
Comment Utility
The server name in the connection settings was the issue.  See my post above for details.
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
Comment Utility
I am just happy you got it working !
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now