September 9, 2011 at 8:57 am
Hello
We have SQL Server 2008 R2 Enterprise on a virtual server (SQL1), plenty resources RAM and disk space.
We have a SharePoint 2010 Enterprise server farm (SP1, SP2) on virtual platform.
The SQL databases for our SP farm are on clustered servers (SP1_SQL1 and SP1_SQL2)
All servers fully patched up.
We have set up the Reporting Services from SQL1 to be integrated with the front end SP server SP1 (quite a task!). I have been assured that all the SSRS config is OK in SP Central Admin.
In SSRS we have experimented with different security options within the report data sources and have found the following:
With "Windows authentication (integrated) or SharePoint user" ticked in Credentials I get this error at runtime ....
An error has occurred during report processing. (rsProcessingAborted)
Cannot impersonate user for data source 'Dashboards'. (rsErrorImpersonatingUser)
This data source is configured to use Windows integrated security. Windows integrated security is either disabled for this report server or your report server is using Trusted Account mode. (rsWindowsIntegratedSecurityDisabled)
This is with me logged onto my PC using my windows credentials. I am in the Administrators group on the SQL server SQL1 and sysadmin in the SQL Server SQL1. I confirm that the SharePoint SSRS configuration for the Integrated Report Server is using Trusted Account mode. I am also in the administrators group on the SP farm.
With "Prompt for Credentials" ticked in Credentials I get this error at runtime ....
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'sp_User_Mappings'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
The only way that I can get this report to run successfully is to use a username and password (SQL login) and store them in "Stored Credentials" within the Data Source but I have to give this SQL login sysadmin status in order to run which is clearly not advisable.
In reading up on these problems I have come across references to Kerberos and some suggestions that because we are accessing data on one SQL server for output in SSRS on another server (SharePoint) then we may have to set up Kerberos in order to get around this 'double hop' problem.
Therefore I'm faced with a few choices ...
1. Ditch the idea of SharePoint Integration and revert back to having a native SSRS configuration on SQL1. Access reports from SharePoint via conventional urls on native SSRS server.
2. Pay someone to come in and set up Kerberos (not guaranteed this is the issue!)
3. Configure SSRS on the SharePoint server - theoretically no double hop
4. Hopefully someone has already found a way around this and advises me on which credentials are best to use in my situation.
Ideally I would like to keep my SP integration with SSRS however if I cannot solve my problem I cannot allow the data sources to run with sysadmin permissions.
Any thoughts will be well received.
L.:w00t:
September 11, 2011 at 3:20 am
No replies can only mean 4 things ...
1. I'm in the wrong Forum!
2. I didn't describe my issue clearly
3. My server configuration is not typical and people are not familiar with this problem
4. My issue is described clearly and I have a fairly typical server configuration but my problem is difficult to advise on.
Any comments would be appreciated.
September 12, 2011 at 11:08 pm
You're in the right place, this is just a topic that's difficult to conquer.
It is possible to configure, and you'll learn a ton about kerberos in the process.
There are a lot of pieces that must work together. There is a word document that walks you through setting up an environment almost exactly as you describe. http://blogs.msdn.com/b/prash/archive/2010/07/15/configuring-kerberos-authentication-for-ssrs-2008-r2-with-sharepoint-2010.aspx
when it's all setup, this is how the kerberos authentication flows:
1. user's browser sends request to http://mySharepointSite (whose app pool is running as domain\svcSP).
2. Sharepoint returns http unauthorized error code, says to use "Negotiate"
3. user's browser contacts the domain controller, requests a kerberos token for http://mySharepointSite
4. because http://mySharepointSite is a valid spn that's registered to the account domain\svcSP, the domain controller responds with a token that is encrypted with domain\svcSP's private key.
5. user retries the request passing the token.
6. http://mySharepointSite decrypts the token. it contacts the domain controller and requests a token on behalf of the user for http://SQL1 (where ssrs is running).
7. Because svcSP is trusted for delegation (more on this below), the domain controller replies with a token that can be decrypted by the service account for SSRS (domain\svcSSRS)
8. sharepoint uses this token when making the request to SSRS for the report.
9. SSRS requests a token on behalf of the user for MSSQL://someSqlServer
10. because svcSSRS is trusted for delegation, and the MSSQL is a registered spn, it returns a token.
11. ssrs passes the token to the database, the database returns the data, and all is good with the world.
Here are some tips:
1. install a network capture tool, e.g. wireshark. it is the easiest way to capture a trace of what is going on between your browser and the sharepoint site. You may need to install it on the ssrs server, the sharepoint server, etc.
2. You must explicitly enable kerberos (aka negotiate) as an authentication method in sharepoint. you can do this via central admin, or through IIS configuration manager. Verify using wireshark that sharepoint is responding with "negotiate" not "NTLM" in the http headers. Don't proceed until you get this first step working.
3. if your sharepoint site is http://mySharepointSite and it's running on webserver1 with the app pool running as the service acct domain\svcSP, you'd use "setspn -U -A HTTP/mySharepointSite domain\svcSP" to create the spn. also, mySharepointSite needs to be an "A" record in DNS, not a CNAME.
4. If iisreset doesn't get that working, you may have to reboot the box.
5. next, you need to configure SSRS to use kerberos authentication as well. It's a quick edit to one of the configuration files, changing RSWindows to RSNegotiate (don't remember which config, google is your friend)
6. both service accounts that are going to delegate credentials (sharepoint and ssrs) need to be flagged as "trusted for delegation" in active directory. There are two kinds of delegation, constrained and un-constrained. constrained is more secure, as it explicitly lists what the service is allowed to delegate to. for example, you'd trust svcSP to delegate only to SSRS, and SSRS could be allowed to delegate only to Sql servers that were going to be reported against. with unconstrained delegation, a service account could pass your credentials to anywhere (exchange, sql, etc). Both accounts should use the same type of delegation. You can go from unconstrained to constrained (security tightening). You can't go from constrained to unconstrained (security loosening).
7. if you hit http://SQL1/ReportServer/ you can browse to a report and try to execute it. If you can execute a report using integrated security when hitting ssrs directly, then the back half is working.
8. Use wireshark.
9. ssrs doesn't work with the claims to token service. This also means that if you're in sharepoint, and you hit the button in the corner to "log on as a different user" (or whatever it says), you won't be able to use integrated security reports until closing your browser.
10. Summary: Every service that gets connected to must have a valid, non-duplicate SPN. (SP, SSRS, SQL, SSAS, etc). Every service account that needs to delegate credentials must be "trusted for delegation". Don't mix delegation types. Use wireshark.
There's a lot to check, but it works like a champ when configured correctly. FYI, it's trivial to extend kerberos through a linked server, nothing to configure sql wise, just make sure the AD spn and delegation flag are set.
good luck!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply