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