SSRS in SharePoint integration mode gets HTTP status 401: Unauthorized error on only one server

My environment has two web front ends for SharePoint. We have SSRS (SQL Server Reporting Services) installed on one of them. I have used Central Administration to integrate the two. If I access a SSRS report using a load balanced url, about 50% of the time I get the following error (in the SharePoint logs).


Exception encountered for SOAP method GetSystemProperties: System.Net.WebException: The request failed with HTTP status 401: Unauthorized.     at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SetConnectionProtocol()     at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper`1.ExecuteMethod(Boolean setConnectionProtocol)

System.Net.WebException: The request failed with HTTP status 401: Unauthorized.    at Microsoft.ReportingServices.SharePoint.UI.RSConnectionInfo.get_SPManagementProxy()     at Microsoft.ReportingServices.SharePoint.UI.RSItemPickerNavigator.ListParents(String item)     at Microsoft.ReportingServices.SharePoint.UI.RSItemPickerNavigator.GetParentItemUrl(String enumerableLocation)     at Microsoft.ReportingServices.SharePoint.UI.ItemSelectorDialogControl.ValidatePath(String unvalidatedPath, Boolean useFallbackPath, String& itemPathParent, String& itemPathGrandparent, String& invalidReason)     at Microsoft.ReportingServices.SharePoint.UI.ItemSelectorDialogControl.OnPreRender(EventArgs e)     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Control.PreRenderRecursiveInternal()     at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

If I use a url that goes directly to one of the servers, I soon see that predictably I get the error for only one of the servers. How weird. I also found that if I add the Reporting Services web part to a page and then try to select a report I get the same error. The good thing is that I then got a correlation id which I could then use to find the exact error in the SharePoint logs.

So I verified a bunch of things:

• Changed Reporting Services to be a single server deployment instead of a scaled out deployment (just to make things easier to troubleshoot).
• The patch level is the same for the servers
• The files are identical on both servers
• Tried using the IP address of the reporting server instead of the host name in Central Admin | Reporting Services Integration | Report Server Web Service URL.
• Changing logging in Reporting Services to verbose. Click here for instructions.
• Disable loopback check. Click here for instructions and security risks.
• Selected Authentication Mode to Trusted Account in Central Admin | Reporting Services Integration | Authentication Mode. I was using a service account before that.

I believe the last thing I tried was the key change that solved it, but the other items could also have helped (though it never worked until I made the last change).

Lots of docs I read through to try to figure out what was wrong. If the above doesn't work, you may want to start with one of the links below.

• The one stop shop for FIXING your Reporting Service's Issues
• Configuring Reporting Services for SharePoint 2010 Integration
• Server Deployment Checklist
• How Do I learn About SharePoint Integrated Mode
• Storing and Synchronizing Report Server Content With SharePoint Databases
• Troubleshooting Configuration Problems
• Security Overview for Reporting Services in SharePoint Integrated Mode
• How to: Install and Configure SharePoint Integration on Multiple Servers
• Deployment Topologies for Reporting Services in SharePoint Integrated Mode
• Granting Permissions on Report Server Items on a SharePoint Site
• How to: Set Permissions for Report Server Items on a SharePoint Site (Reporting Services in SharePoint Integrated Mode)
• How to: Configure a Report Server on a Network Load Balancing Cluster
• Reporting Services Log Files
• Reporting Services SharePoint Integration Troubleshooting
• How to: Configure Report Server Integration in SharePoint Central Administration
• Configuring the Report Server Service Account
• Integrating SharePoint 2010 and SQL Reporting Services 2008 in 6 easy steps
• SharePoint 2010 & SQL 2008 Reporting Services Integration (forum question)
• Error message when you try to access a server locally by using its FQDN or its CNAME alias after you install Windows Server 2003 Service Pack 1: "Access denied" or "No network provider accepted the given network path"
• Recommended configuration for Verbose Logging in Reporting Services

Connect to a FileShare using different credentials

Ever try to use robocopy of other tools to copy files from a Windows share, but you need to use the credentials of another users? If you were to try to use Windows to access the Windows share it would prompt you for a username and password. However, from the command line or batch file it won't. To get around that, you can do the following:

net use \\server1\c$ /user:domain1\user1

When you are done with it, you can remove the credentials you setup by doing the following:

net use \\server1\c$ /delete

You can type net use /? to get help

Here is what you could get from the help

>net use /?
The syntax of this command is:
NET USE
[devicename | *] [\\computername\sharename[\volume] [password | *]]
        [/USER:[domainname\]username]
        [/USER:[dotted domain name\]username]
        [/USER:[username@dotted domain name]
        [/SMARTCARD]
        [/SAVECRED]
        [[/DELETE] | [/PERSISTENT:{YES | NO}]]
NET USE {devicename | *} [password | *] /HOME
NET USE [/PERSISTENT:{YES | NO}]

For explanations of each switch check out the online docs.

Notice you can pass the password so you can use it in a start up or batch script. It also has a /PERSISTENT option so that the connection will be restored at each logon.

Creating a FBA (Forms Based Authentication) (Extranet) site in SharePoint 2010

By default SharePoint uses Windows Authentication and Active Directory to control who can access a SharePoint site. In most cases, this is desired behavior. However, I have a scenario were the users are actually external users from the internet. Basically, I want to use one of my SharePoint web applications as an Extranet. While Windows Authentication works well for intranets, it is doesn't make sense on the internet. Typically on the internet users have a username and password that is specific to the site they are access. This is the behavior we want to have for our SharePoint site.

You can use FBA (Forms Based Authentication) and still use Active Directory as the security store to query, but in my case I didn't want to do that either because there were some domain trust issues. Also, it made provisioning new users for the extranet more difficult. The ideal solution I chose was using ASP.NET Membership provider that can be used in any ASP.NET application. Mainly because it is standard and SharePoint supports it, and others have already used it.

Unfortunately, doing all this is not trivial and is actually quite tedious. Fortunately, many people have done this with some variation.

I looked at several sites that showed how to do this to varying levels of detail. I found this one that showed lots of details, screenshots, and explained why in many cases. I did learn some things along the way that were not explicitly noted.

• When creating the ASP.NET database, use the farm service account or whatever you run the application pools as. This will guarantee that the service account has access to the database. This was important to me since I didn't have direct access to my SQL Server. Alternatively, you can do it like the article describes as well. The reason is I needed to use Windows Integrated Security to connect to my SQL Server database. This can be easy if the Service account can RDP to a server, but if the user can't (like my scenario) then you need to run the aspnet_regsql.exe as the service account. To do that, do the following:
  1. Open a command prompt by right-clicking on it and choosing Run as Administrator
  2. At the command prompt type

  runas /user:SPServiceUser cmd.exe

  This will give you a command prompt running under the service account (mine is called SPServiceUser in this example). Now you can do whatever you want as that user.
  
  3. In this case, we want to run aspnet_regsql.exe, so do something like:
  

  C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

• When it came to the SecurityTokenServiceApplication, I didn't have much luck with the default providers being set the way the author suggested. I changed removed the defaultProviders Attribute for roleManager and membership tags. New Default could be FBA,FBARoles, but the CA user look test (later in this doc) only seems to work with c,I as defaults. Curious if others have the same issue.
• You will need to make these changes on each node in the SharePoint web farm, probably want to just copy the web.config files from server to server. If you aren't sure if the corresponding web.configs on each server is the same, then maybe just copy the key parts outlined in my diagram. I was surprised my web.configs had variations from server to server. I don't think they should be though.

When it came to troubleshooting, and comparing different articles I found on the internet, I found it difficult to figure out why there were differences and if there were what were they. Also, I wanted to know what they were assuming the default settings in IIS and the web.configs were before and after. Also, I am a visual person, so I wanted a nice visual representation of before and after. I also found it difficult to keep track of what the settings where for users verses roles. So, I put together a diagram and corresponding final web.config examples. You can download it here. The user settings are on the left, and the roles are on the right.

Once you are all done, you will likely realize that you have no way to manage users yet. A good choice for adding this functionality to the Site Settings of SharePoint 2010 is SharePoint 2010 FBA Pack. I have tried it (password change and reset, add, edit, delete users manually) and found that it works well. I am not letting users request access to the site, so I didn't try that functionality.

Other things I tried
I ran into trouble trying to extend an existing web application and have FBA on one url and Windows Authentication on another url. I was able to get a single site to have both FBA and Windows Authentication, but we didn't want the users to have to decide if they were FBAor Windows Authentication. I also tried converting an existing site using Powershell, but I didn't have much luck with that either. In the end, it was best to just create a site that only used FBA.

If you are doing the same thing for SharePoint 2013, you may want to look at this one. I haven't studied it in detail,but it looks like it is good. From what I remember reading, the configuration is basically  the same, but IIS doesn't support adding the users so you have to do it another way.

The key links you need in one place:
Step by Step Instructions
My Visual Summary and Web.Config examples
Add FBA User and Role management to SharePoint 2010
A good place to start if you are using SharePoint 2013 instead of 2010

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