CIPortAuthority
asked on
SQL Server Reporting Services Access Denied from Management Studio
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:
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?
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
------------------------------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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what happens when you just try to ping the server using a command window?
And even more to the point, you have enabled remote acces on the server, right?
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...
ASKER
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.
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.
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.
ASKER
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:
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
------------------------------
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....
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....
ASKER
I am looking in to this today. Sorry for the delay.
ASKER
Well, I've followed all of the instructions on both pages and I still cannot remotely access Reporting Services using SSMS.
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?)
ASKER
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.
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.
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...
ASKER
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!
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!
ASKER
The server name in the connection settings was the issue. See my post above for details.
I am just happy you got it working !
ASKER
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.