Sunday, March 13, 2011

Installing Sql Server 2005 Reporting Services on Vista and deal with issues

Note: This post was originally posted on my old blog on 25th of June 2009

Update: (2009-06-26) - Added the Windows x64 server issue.
Update: (2009-10-26) - Added quick note about running IE as administrator in all cases and noted that the notes work with Windows 7
Update: (2009-11-10) – Link to MSDN article on Configuring Report Server for Local Admin on Vista and added another step to the Trusted Sites instructions. Also added a note that I installed Reporting Services to run as Local System.
Update: (2009-11-11) – Added a link and some information from a post on a forum which talked about using Management Studio to assign permissions to the report server (Even though I wasn’t able to connect to the Report Server using Management Studio to try it out… :( Groan, Complain!

The introduction of Windows Vista to the world caused some compatibility issues with a variety of software. One of those is an actual Microsoft product.

Sql Server 2005 Reporting Services has been an absolute pain to get running. I've nearly spent the whole day trying to get it working so that I can get to the Report Manager and manage the reports.

In this article I'm installing Sql Server 2005 With Advanced Services With SP3 on a Vista Business Box. and will also try and describe some of the errors that you may get and how to fix them.

Prerequisites:

Please note the following:
  • You have to install Sql Server 2005 on a Vista Business, Vista Enterprise or Vista Ultimate box. You cannot use Vista Basic or Vista Premium.
  • You cannot install Sql Server 2005 Enterprise Edition on Vista Machines.
You need to ensure that several features are installed in Windows as follows:
  1. Go to the Control Panel.
  2. Double click "Programs and Features"
  3. Click "Turn Windows features on or off"
  4. Click "Continue" on the UAC dialog
You now need to select the correct features which are listed below:

Under Internet Information Services | Web Management Tools| IIS 6 Management Compatibility select:
  • IIS 6 WMI Compatibility
  • IIS Metabase and IIS 6 configuration compatibility
Under Internet Information Services | World Wide Web Services | Application Development Features select:
  • ASP.NET
  • ISAPI Extensions
  • ISAPI Filters
Under  Internet Information Services | World Wide Web Services | Common Http Features Select:
  • Default Document
  • Directory Browsing
  • HTTP Errors
  • HTTP Redirection
  • Static Content
Under  Internet Information Services | World Wide Web Services | Security Select:
  • Windows Authentication

Installation

Follow the Microsoft instructions to install Sql Server 2005 (Some of the above information is also included in the instructions provided by Microsoft)

When you get to the Report Server Installation Options dialog select Install the default configuration so that you dont have to configure things manually.

Note: In my setup of Reporting Services I have it running as Local System. You might want to select this for Reporting Services unless you know better :)

Post Installation Changes

Ensuring Read, Script and Execute for ReportServer

  1. Open IIS Manager. Open a command prompt and type "inetmgr"
  2. Drill down to the website and click on the "ReportServer" virtual directory (remember that if you have a named instance it will be called "ReportServer$namedinstance"
  3. Under the server components category at the bottom, double click "Handler Mappings"
  4. In the "Actions" sidebar click "Edit Feature Permissions..."
  5. Ensure that "Read", "Script" and "Execute" are all checked!
  6. You might want to restart the application pool or website after this

Turn off SSL Requirement

This is only really for development purposes. In a production environment you might setup SSL. I don't want it!
  1. On the start menu select All Programs | Micosoft Sql Server 2005 | Configuration Tools | Reporting Services Configuration
  2. Enter the machine name and instance name and connect
  3. Click on the "Report Server Virtual Directory" Item to the left and ensure that the "Require Secure Socket Layer (SSL) connections" is unchecked (unless you want it, which in out case we don't.)

Set IE Trusted Sites to Include http://localhost (and http://machinename)

This one is actually in the Microsoft instructions for installing Sql Server 2005. Here it is for convenience, as it's a small thing, but can cause frustration :)
  1. Open IE
  2. Select Internet Options on the Tools menu
  3. Click the Security tab.
  4. Click Trusted Sites.
  5. Click the Sites button
  6. Add http://localhost and http://machinename to the list of trusted sites.
  7. Clear the “Require server certification (https:) for all sites in this zone” if not using https.
  8. Click Close and OK
For some reason IE will only show the top section of the home folder without all of the required functionality that you need. This fixes that problem.

For fuller instructions on the above as well as configuring the site security settings, see here.

Errors

This section details some of the errors that I've had at variouse times and attempts to give some guidance on how to fix them!

I can see the home page but can't do anything

See the "Set IE Trusted Sites to Include http://localhost (and http:/machinename)" section above.

It is important that you run IE as administrator also. Once you can see the root folder and the buttons to add folders you can go to the Properties tab and give the appropriate accounts permission to access the folder and any sub folders.

IF you can see the home page but no content (where you have already applied the other fixes) then you can also give yourself permissions using Sql Server Management Studio: at least in some cases. I can’t do that, but someone else I know was able to get around this issue using the method below.

(Below, for convenience, is from this entry on social.msdn.microsoft.com)
The solution that worked for me was:
  1. Run Sql Server Management Studio as Administrator (right click "Run as Administrator")
  2. Connect to a Reporting Services server using Windows Authentication.
  3. Right click Home, Select Properties, Select Permissions.
  4. Click Add Group or User, Type your Username, Click Ok.
  5. Check the box for Content Manager, Click Ok.
Now you can access Reporting Services via Sql Server Management Console without running as administrator.

You should also have permissions to browse Report Manager (http://localhost/Reports).  If not, but you do see the Information Bar complaining then add http://localhost/ to Explorer's Trusted Sites Zone.

Access is denied: You do not have permission to view this directory or page using the credentials you supplied...

Access is denied.

Server Error in '/Reports$JJJ' Application.

Access is denied.

Description: An error occurred while accessing the resources required to serve this request. You might not have permission to view the requested resources.
Error message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control Lists). Ask the Web server's administrator to give you access to 'c:\Program Files\Microsoft SQL Server\MSSQL.16\Reporting Services\ReportManager\Pages\Folder.aspx'.

Version Information: Microsoft .NET Framework Version:2.0.50727.4016; ASP.NET Version:2.0.50727.4016
This is an ASP.NET Error. It means that the account I used (which was my normal domain user account) was being impersonated by the web application, but that account doesn't have rights to the given folder.

Solution:

Give your account "FULL" rights to the folder. It seems not to work with anything less!

Reporting Services Error: The request failed with HTTP status 403: Forbidden

With this error you get an IIS error. Please ensure that you have done what is specified in the the Post Installation Changes section above

IE Error with dialog box asking for credentials

You get the following error after entering your credentials into a dialog box (3 times)

HTTP Error 401.3 - Unauthorized

You do not have permission to view this directory or page because of the access control list (ACL) configuration or encryption settings for this resource on the Web server.

The box asking for credentials must be on that can access  the report manager application folder which for the example I'm currently doing is "c:\Program Files\Microsoft SQL Server\MSSQL.16\Reporting Services\ReportManager\". If the account doens't have the permissions it will ask another two times and then give you the above error.

You need to got to the directory and add the account you entered so that they have permissions to the directory specified on the error page. If you run as administrator you will probably not get this error, if you use another account you may get it. If you want to add that user then either give them rights to the directory or add them in a group that does have full rights to the given directory.

When you have given the permissions you will get to the home page of the report manager. You have to run as administrator account and then setup the home folder permissions as well as configure site wide security so that the account has system administrator rights.

Error: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

This has been caused by the ReportServer virtual directory/application not having Read, Script and Execute features set.
  1. Open IIS Manager. Open a command prompt and type "inetmgr"
  2. Drill down to the website and click on the "ReportServer" virtual directory (remember that if you have a named instance it will be called "ReportServer$namedinstance"
  3. Under the server components category at the bottom, double click "Handler Mappings"
  4. In the "Actions" sidebar click "Edit Feature Permissions..."
  5. Ensure that "Read", "Script" and "Execute" are all checked!
  6. You might want to restart the application pool or website after this
Check the Reports virtual directory/application also.

Windows Server 2003 R2 x64 issue

If you still get the home page of the Report Manager with no content on Windows Server 2003 R2 x64. You can solve it by adding the http://localhost to the Local Intranet Trusted List as shown here

Windows 7 Error – An Unexpected error occured on a recieve – Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

The underlying connection was closed: An unexpected error occurred on a receive.
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
An existing connection was forcibly closed by the remote host


Have not been able to resolve this at the current moment.

No comments:

Post a Comment