MSSQL – v2005 – Adding Reporting Services to Existing SQL Server Install

Background

There are “so many ways to go wrong” when adding Reporting Services to an existing SQL Server v2005 Install on MS Windows 2008.

 

Issues

Here are some of those ways:

  1. Reporting Services is disabled
  2. IIS Manager – The CGI Module Is not Installed
  3. Configure Report Server – Web Service Identity – ASP.Net Service Account is blank
  4. Database Not Properly/Fully Configured
  5. HTTP Error 404.2 – Not Found – The page you are requesting cannot be served because of the ISAPI and CGI Restriction list settings on the Web server
  6. Could not find permission set named ‘ASP.Net’.

Reporting Services is disabled

Symptom

In the screenshot below, on the “Components to Install” page the “Reporting Services” option is disabled.

ComponentsToInstall - Before

Diagnosis

On MS SQL2005, Reporting Services is dependant and reliant on IIS.

If IIS and certain subcomponents are not installed, Reporting Services will be disabled.

 

Remediation


REM Web Server (IIS) role
REM To install the Web Server role
REM https://technet.microsoft.com/en-us/library/ee441263(v=ws.10).aspx
start /w pkgmgr /iu:IIS-WebServerRole;WAS-WindowsActivationService;WAS-ProcessModel;IIS-WebServer;IIS-CommonHttpFeatures;IIS-ApplicationDevelopment;IIS-NetFxExtensibility;IIS-ASP;IIS-ASPNET;IIS-ISAPIExtensions;IIS-CGI;IIS-ISAPIFilter;IIS-StaticContent;IIS-DefaultDocument;IIS-DirectoryBrowsing;IIS-HttpErrors;IIS-HttpRedirect;IIS-ApplicationDevelopment;IIS-WindowsAuthentication;IIS-WebServerManagementTools;IIS-ManagementScriptingTools;IIS-IIS6ManagementCompatibility;IIS-Metabase;IIS-WMICompatibility;IIS-ManagementConsole;IIS-HttpTracing

 

 

IIS Manager – The CGI Module Is not Installed

Symptom

The CGI Module is not installed.

ISAPI and CGI Restrictions

Diagnosis

ISAPI and CGI Modules are not installed.

Remediation

Follow GUI or use sample script below.

In the script we added ISAPI and CGI packages.


start /w pkgmgr /iu:IIS-WebServerRole;WAS-WindowsActivationService;WAS-ProcessModel;IIS-WebServer;IIS-CommonHttpFeatures;IIS-ApplicationDevelopment;IIS-NetFxExtensibility;IIS-ASP;IIS-ASPNET;IIS-ISAPIExtensions;IIS-CGI;IIS-ISAPIFilter

 

Configure Report Server – Web Service Identity – ASP.Net Service Account is blank

Symptom

ASP.Net Service Account is blank.

SetWebServiceIdentity-Cropped

Diagnosis

The WebServiceAccount item is missing in the <SQL Server Instance Home>\Reporting Services\ReportServer\rsreportserver.config

In our case, C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\rsreportserver.config

Remediation

Steps

  1. Provision Domain or Local Account
  2. Register Account
  3. Add Web Service Account to rsreportserver.config
  4. Use Web Service Account

 

Provision Account

ComputerManagement-AddLocalUser

Register Account

Syntax

C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis -ga [Account]

Code

C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis -ga TYPHOON\ASPNetServiceAccount

 

Output

ASPNet-Register-IIS

Add WebServiceAccount to rsreportserver.config

Before
ReportServer-Config-Service-Before


After

ReportServer-Config-Service-After

 

Use Web Service Identity

WebServiceIdentity

 

 

Database Not Properly/Fully Configured

Symptom

Database Setup is red-checked.

DatabaseConnection - Red

 

Diagnosis

We  need to make sure that the Service Account has access to SQL Server Instance and that SQL Server itself is properly configured for Reporting Services.

Remediation

Access Configure Report Service \ Database Setup \ Database Connection

DatabaseConnection

 

Create Scripts

 DatabaseScript

 

Using Database Client Tool, Apply Generated Script

Using DB Client Tool, apply generated script.

Apply Changes

Click the Apply button.

DatabaseConnectionTaskStatus-Completed

 

Could not find a package to install on the installation media

Symptom

Using Program Features \ Uninstall or change a program \ Change

UninstallOrChangeAProgram-Change

 

 

CouldNotFindAPackage

Diagnosis

The install material is not available, download locally, and install using setup.exe

Remediation


setup SKUUPGRADE=1

HTTP Error 404.2 – Not Found

The page you are requesting cannot be served because of the ISAPI and CGI Restriction list settings on the Web server.

Symptom

When one accesses the Reports Virtual folder through a web browser, one might notice the error posted below.

Image

HTTPError--404Dot2--NotFound (Cropped)

Tabulate

Item Value
 Module IsapiModule
 Notification ExecuteRequestHandler
 Handler ExtensionlessUrlHandler-ISAPI-4.0_64bit
 Error Code 0x800704ec
 Requested URL  http://localhost:80/Reportsv2005
 Physical Path  C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager
 Logon Method  Negotiate
 Logon User LAB\dadeniji

 

 

Diagnosis

The problem is that the ExtensionlessUrlHandler-ISAPI-4.0_64bit is not being handled properly.

Remediation

Original Settings

On the Reports VirtualDirectory, the Handler Mappings is configured as shown below.

ExtensionlessUrlHandler-OriginalSettings

 

Correction

Virtual Directory – Features View

FeaturesView

Virtual Directory – Features View – Handler Mappings – Original View

Review the settings of “ExtensionlessUrlHandler-ISAPI-4.0_32bit” and “ExtensionlessUrlHandler-ISAPI-4.0_64bit“.

ExpressionUrlHandler-ISAPI-4-0

 

Virtual Directory – Features View – Handler Mappings – Modified View

EditScriptMap

 

Virtual Directory – Features View – Handler Mappings – Request Restrictions – Mapping

RequestRestrictions - Mapping

Virtual Directory – Features View – Handler Mappings – Request Restrictions – Mapping

RequestRestrictions - Verbs

Virtual Directory – Features View – Handler Mappings – Request Restrictions – Access

RequestRestrictions-Access

 

Virtual Directory – Features View – Handler Mappings – Override Inherited Settings

EditScriptMap-OverrideInheritedSettings

 

Handler Mappings

Here is where we end up upon overriding the settings for:

  1. CGI-exe
  2. ExtensionlessUrlHandler-ISAPI-4.0_64bit
  3. ExtensionlessUrlHandler-ISAPI-4.0_32bit

 

HandlerMappings-After

 

Could not find permission set named ‘ASP.Net’.

Symptom

When one accesses the Reports Virtual folder through a web browser, one might notice the error posted below.

CouldNotFindPermissionSetNamedASPNet

 

Diagnosis

The error states that “Could not find permission set named ‘ASP.Net’.

 

Remediation

Edit the ReportManager\web.config file and comment out the “<trust level=”RosettaMgr” originUrl=”” />” entry completely or replace with <trust level=Full” originUrl=”” />.

Original

TrustLevel-0123PM

Revised

TrustLevel-0126PM

 

Crediting

Crediting Noshad Latif for providing right answer here

Run asp.net 4.0 app under SharePoint
Link

Listening

The web must be doing something right  .. all these good advices

Billy Currington – Must Be Doin’ Somethin’ Right
Link

 

References

IIS

  1. How to install IIS 7.5 on Windows 7 using the Command Line
    https://blogs.msdn.microsoft.com/habibh/2009/08/14/how-to-install-iis-7-5-on-windows-7-using-the-command-line/

 

Encryption keys

  1. Delete and Re-create Encryption Keys
    https://technet.microsoft.com/en-us/library/ms156010%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

 

Error

  1. SQL Server  >  SQL Server Reporting Services, Power View > Could not find permission set named ASP.Net’
    Link


Failed Requests Using Tracing

  1. Troubleshooting Failed Requests Using Tracing in IIS 7
    Link
  2. Enable and Activate Failed Request Tracing Rules
    Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s