Solved

How can I resolve my issues with Reporting Services and SharePoint 2010 Integration?

Posted on 2011-03-25
6
2,179 Views
Last Modified: 2012-08-14
Hello,

I have built a SharePoint 2010 Farm with an application server, 2 web front end servers, and two SQL servers, (one for the main SharePoint Data, and one for Reporting Services).  I am able to configure the Reporting Services Integration and I beleive I can add a Report Server to the Integration as it accepts my server name and instance.  The problem is when I click on the next section to Set Server Defaults, or if I go to Site Settings and click on any of the links for Reporting Services, I get the following error:

The request failed with HTTP status 401: Unauthorized.

I looked at the correlation ID in the ULS Logs and get the following:

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)

I looked around the Internet for similar issues and many people were mentioning Kerberos being misconfigured.  I spent some time going over my SPN configurations and everything seemed to be setup.  I am by no mean an expert when it comes to Kerberos so I could be mistaken though.

As a final test to try and rule out Kerberos as a culprit, I changed the configuration in the Reporting Services Integration from using the URL with the FQDN of the Reporting Server, to the URL with the IP address of the Reporting Server as I understand that would use NTLM to authenticate.  Once I made the changes and click on the links, I was still getting the same error on the screen and in the ULS logs.

I can successfully access the Web Service URL listed in Reporting Services Configuration Manager, and when I enter that URL into the Reporting Integration section of Central Admin everything checks out okay.

So far, the only way I have been able to get reporting to load without the errors is to change the configuration in the Reporting Services Integration area to use a Trusted Account instead of Windows Authentication.

Does anyone have any ideas as to what I may have done wrong, or not done that might fix this issue?

0
Comment
Question by:ADX39655
6 Comments
 
LVL 3

Expert Comment

by:AndrewSkoraro
ID: 35218786
It does sound like it could be kerberos.  You will need to provide all of the SPN settings for each server and the database server.  Here is the SPNs that you will need.  I am assuming you know how to set IIS and Reporting services config files as well as AD for the system accounts.

**Your app server**
setspn -a HTTP/app01.yourdomain.com domain\serviceaccount
setspn -a HTTP/app01 domain\serviceaccount
**Your web server**
setspn -a HTTP/web01.yourdomain.com domain\serviceaccount
setspn -a HTTP/web01 domain\serviceaccount
**Your reporting server**
setspn -a HTTP/web02.yourdomain.com domain\serviceaccount
setspn -a HTTP/web02 domain\serviceaccount
**Your sql servers **  Note that you will need to specify the port for a named instance, default is port 1433 - my example assumes instance on port 58180
setspn -a MSSQLSvc/DB01.yourdomain.com domain\sqlaccount
setspn -a MSSQLSvc/DB01.yourdomain.com:58180 domain\sqlaccount

setspn -a MSSQLSvc/DB02.yourdomain.com domain\sqlaccount
setspn -a MSSQLSvc/DB02.yourdomain.com:58180 domain\sqlaccount
0
 

Author Comment

by:ADX39655
ID: 35219187
Here is what I currently have for my SPN configurations

SQL Service Account

MSSQLSVC/appprojsql2p:1433
MSSQLSVC/appprojsql2p.mydomain.com:1433
MSSQLSVC/appprojsql1p:1433
MSOLAPSvc.3/approjsql2p:ProjectSSRS
MSOLAPSvc.3/approjsql2p.mydomain.com:ProjectSSRS
MSSQLSVC/appprojsql1p.mydomain.com:1433

SQL Reporting Service Account

 HTTP/appprojsql2p.mydomain.com
 HTTP/appprojsql2p
 HTTP/appprojsql1p.mydomain.com
 HTTP/appprojsql1p

IIS Service Account

http/appprojweb2p
http/appprojsvr1p.mydomain.com
http/appprojweb2p.mydomain.com
http/appprojweb1p
http/appprojweb1p.mydomain.com
http/appprojsvr1p
http/schedule.mydomain.com
http/schedule

If I did something wrong with this setup please let me know as I am very new to Kerberos.
0
 
LVL 2

Expert Comment

by:edzhu
ID: 35220253
Did you try to configure trust for delegation for web parts? You can find the detail in the following KB: http://support.microsoft.com/default.aspx?scid=kb;EN-US;832769
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:AndrewSkoraro
ID: 35242740
Your SQL connections needs some changes.  

setspn -d MSSQLSvc/appprojsql2p:1433 [domain\account]
setspn -d MSSQLSvc/appprojsql1p:1433 [domain\account]

setspn -a MSSQLSvc/appprojsql2p.mydomain.com [domain\account]
setspn -a MSSQLSvc/appprojweb1p.mydomain.com [domain\account]
0
 
LVL 1

Accepted Solution

by:
wickid187 earned 500 total points
ID: 35259720
I think the SPNs look right; however, you may want to check your delegations.  Verify the IIS account on the SSRS server and add a delegation for it to use the SPN for the Reporting Services account and see if that clears things up.

Also look into disabling loopback on servers in the farm as that could cause 401 errors as well.
0
 

Author Comment

by:ADX39655
ID: 35261046
Not sure which was the fix...the loopback changes or adding the delagation but it cleared up my errors.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now