Troubleshooting Reporting Services Access Denied Error when using Forms Based Authentication (FBA) in SharePoint 2010

Here is the scenario. I have a SharePoint 2010 site that uses Forms Based Authentication and as such it uses Claims Based Authentication. I did not enable Windows Integrated Authentication on the site. I created a Reports Library and created a very basic report there. It doesn't even pull any data. It just shows static content. I did that to eliminate any chance that the access denied was to a data source. In the real world, I did eventually have it pull real data once I solved my access denied issue.

I am running:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (VM)

 

The error I get when trying to load the page is as follows:

 

 

 

In text form:

Report Server has encountered a SharePoint error. (rsSharePointError)

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

For more information about this error navigate to the report server on the local server machine, or enable remote errors.

 

After searching through the SharePoint log files on the server we found:

Web part failed in SetParamPanelVisibilityForParamAreaContent: Microsoft.Reporting.WebForms.ReportServerException: Report Server has encountered a SharePoint error. (rsSharePointError) ---> Microsoft.Reporting.WebForms.ReportServerException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) ---> Microsoft.Reporting.WebForms.ReportServerException: For more information about this error navigate to the report server on the local server machine, or enable remote errors　　　　 --- End of inner exception stack trace ---　　　　 --- End of inner exception stack trace ---　　　　 at Microsoft.Reporting.WebForms.ServerReportSoapProxy.OnSoapException

 

The Solution

 

For Access Denied error, one solution put forth in this forum is

Reporting Services service account must be local admin on the reporting services server, please check if you meet this requirement. If not add the user to the server's local admin group and "reintegrate the reporting services with SharePoint"

The SSRS service account (you can get this from the SSRS Configuration tools in the Start Menu) was not in the local administrators group where  Reporting Services is installed. I have made this correction. I did NOT appear to fix the issue. I even tried restarting Reporting Services from  Services.msc, but no help.

Then I realized that I still need to "reintegrate the reporting services with SharePoint." I was not sure exactly what that meant, so I just tried some things. Here is what I did that I think fixed it after adding the SSRS service account user to the local admin group:

In Central Administration| Reporting Services | Reporting Services Integration| Clicked Activate feature in all existing site collections and then the OK button.
 

In Central Administration| Reporting Services |Add a Report Server to the Integration changed Server Name to a wrong value then changed back to the server name where Reporting Services is installed. Click OK after each change. When prompted for credentials I entered the farm service account and password.
 

I tried the report and it worked. I’m not sure exactly which step fixed it, but it works now.

Some other things to try if it still doesn't work:

You need to be running SQL Server 2008 R2 according to this article on MSDN. It says:

"SharePoint 2010 Claims based authentication is not supported by SQL Server 2008 or SQL Server 2008 SP2 report servers. Use SQL Server 2008 R2 Reporting Services if you need to use a Claims enabled SharePoint 2010 Web application."

According to the article, you don’t have to upgrade our SQL Server to SQL Server 2008 R2, but you do need to upgrade to at least SQL Server 2008 SP1 + Cumulative Update #8. You would have to upgrade the SQL Server 2008 R2 Reporting Services add-in for SharePoint 2010 products on all associated servers.

This article from MSDN also talks about compatibility issues. This is also useful. Here is one just for troubleshooting.

How to copy a SharePoint SiteCollection to a new server

First thing, you should make sure that the patch level is the same for both the installed patches and the content database. See here for more details on updating the patch level. Once the environments are the same, it is relatively straight forward to copy a SharePoint Site collection from one server to another server.


Here are the steps

1. Backup the site collection
   You can use Central Administration | Backup and Restore | Perform a site collection backup. You can use a UNC share path or you can specify a local disk path.
   Alternatively, you can use Powershell to do the backup as well.
   PS> Backup-SPSite "http://yourSPServerHere/sites/siteCollection1" -Path "C:\temp\siteCollection1.bak"
2. Copy the backup file to the new server or use a file share that both server can access to avoid copying the backup file. In this example, I kept the same path on the local disk for ease of syntax.
3. Restore the site collection at a desired url
   PS> Restore-SPSite "http://yourNewSPServerHere/sites/siteCollection1" -Path "C:\temp\siteCollection1.bak"
   NOTE: Unfortunately, there is ability to do this in Central Administration like there is for the backup.
   Also, notice I kept the same basic url (minus the server change)

Troubleshooting Powershell

If you get an error like the following:
The local farm is not accessible. Cmdlets with FeatureDependencyId are not registered.
Check here for details on how you can get access.

Patching or Updating SharePoint 2010 is a two step process

Whenever you patch or update SharePoint Server 2010 there are really two steps you need to follow. The first is the more obvious one. You download the patch from Microsoft and install it. It will typically show up in the Program Files list under the Control Panel. This needs to be done on each web front end in the farm. This is where many people stop. However, SharePoint 2010 requires that the content databases be updated as well. To update the configuration database, you have to do the following steps on one of web front ends in the farm. I usually use the one that has Central Administration installed.

1. Open an Administrative command prompt.
2. cd C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN
3. Run PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures

 This process can take a while depending on how big your databases are, processing power, etc, but it does take many minutes generally and SharePoint will be down while it is being updated.

For more details, check out this blog. I summarized some key info from it:

Troubleshooting: Cannot start service SPAdminV4 on computer

 

 

Failed to create the configuration database.

An exception of type System.InvalidOperationException was thrown.  Additional exception information: Cannot start service SPAdminV4 on computer '.'.

 

Potential Cause #1: SPAdminV4 Service needs more time to startup

The default is typically less than 30 seconds, so I changed it to 4 minutes (240,000 milliseconds) and this helped. A smaller value or larger value may be needed for your environment.

 

To change the default timeout for ALL services to start up do the following:

When a service starts, the service communicates to the Service Control Manager how long the service must have to start (the time-out period for the service). If the Service Control Manager does not receive a "service started" notice from the service within this time-out period, the Service Control Manager terminates the process that hosts the service. This time-out period is typically less than 30 seconds. If you do not adjust this time-out period, the Service Control Manager ends the process and the attached debugger while you are trying to debug. To adjust this time-out period, follow these steps:

1. In Registry Editor, locate, and then right-click the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
2. Point to New, and then click DWORD Value. In the right pane of Registry Editor, notice that New Value #1 (the name of a new registry entry) is selected for editing.
3. Type ServicesPipeTimeout to replace New Value #1, and then press ENTER.
4. Right-click the ServicesPipeTimeout registry entry that you created in step c, and then click Modify. The Edit DWORD Value dialog box appears.
5. In the Value data text box, type TimeoutPeriod, and then click OK
   Note TimeoutPeriod is a placeholder for the value of the time-out period (in milliseconds) that you want to set for the service. For example, if you want to set the time-out period to 24 hours (86400000 milliseconds), type 86400000.
6. Restart the computer. You must restart the computer for Service Control Manager to apply this change.

 

Yes, you need to restart for the change to take effect.

This was copied from http://support.microsoft.com/kb/824344; see #3 on the page.

 

Potential Cause #2: Certificate Validation

 

Beginning with June 2012 CU for SharePoint 2012 the CRL (Certificate Revocation List) checks to be enforced, which in turn affects some native functionality of SharePoint AdminV4 service. 

When running the SharePoint Product Configuration Wizard, the configuration will fail with the following error above. This is typically an issue if you don’t have direct internet access.

 

To work around this issue.

 

1.) Add a new computer policy which alters the options for retrieving certificate validation on a network.

2.) Add host file entries into the local computer host file.

• Alter the computer policy
  • Click on Start-Run
  • Type in "GPEdit.msc" and click "OK"
  • Expand Computer Configuration-Windows Settings-Security Settings-Public Key Policies
  • Double-click "Certificate Path Validation Settings"
  • Click on the "Network Retrieval" tab
  • Check the box "Define these policy settings"
  • Uncheck "Automatically update certificates in the Microsoft Root Certificate Program (recommended)" and uncheck "Allow issuer certificate (AIA) retrieval during path validation (recommended"
  • Click on "OK"
  • Close out of GPEdit.msc

• Add host file entries
  • Click on Start-Run
  • Type in "C:\Windows\System32\Drivers\Etc" and click "OK"
  • Double-click the file "Hosts"
  • Select "Notepad" as the program to open the file
  • Insert the following lines into the hosts file
    • 0.0.0.0 crl.microsoft.com
    • 0.0.0.0 crl.verisign.com
    • 0.0.0.0 ocsp.verisign.com
    • 0.0.0.0 SVRSecure-G2-crl.verisign.com
    • 0.0.0.0 SVRSecure-G3-crl.verisign.com
    • 0.0.0.0 www.download.windowsupdate.com
    • 0.0.0.0 SVRSecure-G2-aia.verisign.com
  • Save the file and exit notepad

I copied the above steps from:

http://collaborationnation.blogspot.com/2012/09/cannot-start-spadminv4-service-error-on.html

 

 

What is running on port 80

You can use netstat to see the process id of what is running on port 80 (or any other port for that matter). Here is the command you would use.

netstat -p tcp -ano

You can then start Task Manager and find the process that has that PID.

If you don't have a PID column you can add it.

1. In Task Manager go to the Processes tab
2. Go to the View menu | Select Columns... menu item.
3. Select PID (Process Identifier)
4. Click OK

That may or may not be useful. In my case the PID is 4 which is the System process and has the description NT Kernel & System. The problem is that lots of applications can use the kernel to open a port. Unfortunately, that was a deadend for me. I guess this exercise is sort of useful :)

List of Best Practices for C# and SharePoint development

Here are Guidelines for Naming Convention, Design Considerations, Best Practices, and Coding Conventions. They are definitely for C#. Web parts should be written in C# as well , so these conventions can also be applied to them.

C# Guidelines, Naming Conventions, Design Considerations, Best Practices, etc

C# Coding Conventions and Best Practices

Below is some best practices when developing web parts, and any code that uses the SharePoint Object Model. Generally, these do not go into code syntax or anything like that. They do go into issues specific to SharePoint development.

Best Practices: Using Disposable

Best Practices: Common Coding Issues When Using the SharePoint Object Model

Checklist for Creating SharePoint Web Parts

10 Best Practices For Building SharePoint Solutions

Reporting Services Permission Error

I have Reporting Services installed on SharePoint 2010. I created a new web application in SharePoint and then created a Site Collection. I then created a Report Library and then created a report. I opened the report from the link in the Report Library and I got the following error.

Report Server has encountered a SharePoint error. (rsSharePointError)

 

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

 

For more information about this error navigate to the report server on the local server machine, or enable remote errors

 

To fix the error I did the following:

1. Go to Central Administration | Application Management | Manage web applications
2. Select your web application
3. Click User Policy in the ribbon
4. Add Users the Reporting Services service account and give it Full Control.