Unraveling SharePoint BI Access to External Data Sources - Part 1
Intro
I’ve been meaning to write this blog for some time now so I finally got around to doing it. In my opinion, SharePoint BI setup is one of the most confusing aspects for SharePoint IT Pro’s and Dev’s in recent memory. Microsoft TechNet Authors and some fellow Microsoft Colleagues have produced some great documentation which provide steps to set it up and general limitations. While this is a huge benefit, I do see a big technical gap here and that is:
If you don’t understand why you’re configuring or setting up the components in a particular way what happens when things don’t work initially or things break at a later date!
So my intent here is not to replace the BI setup documentation that is already publicly available. Rather, it’s to supplement that setup documentation which should fill in more BI foundational knowledge. This blog is specifically focused on SharePoint 2013 On Premise environments that will leverage external Data sources. In order to prevent you from navigating away from this blog to go check the setup, let me give you the very basic SSRS setup on SharePoint 2013. Again, I still recommend checking the references I’ll add to the bottom of this blog for more detailed setup steps.
1. On SharePoint 2013 Application Server, run SQL 2012 setup and installed the following:
- Reporting Services – SharePoint
- Reporting Services Add-in for SharePoint Products
- Management Tools – Basic & Complete
2. Provision SSRS Service Application in Central Administrator
Note: My SSRS Service Application uses an application pool called “bi_pool” which runs using Contoso\spsvc account. Yes, this account is setup as the service account in Central Admin under Configure Service Accounts.
3. Setup SSRS Add-In on remaining SharePoint boxes
4. Configure Claims to Windows Token Service -> I’ll discuss this later.
5. Setup Service Accounts for Kerberos Constrained Delegation (To be more specific accounts running C2WTS and SSRS). I’ll discuss this in more detail later.
External DB Access
In my example case, SharePoint via the SSRS Service Application leverages an external database in SQL called AdventureWorksDW2012. The SharePoint BI Service Application identity (Service Account) will impersonate the user attempting to gain access which means I’ll need to add the appropriate DB permissions for that particular user.
For Example, I need SharePoint end user Jose to access AdventureWorksDW2012 SQL DB.
1. Within SQL MGMT Studio, I can simply expand the Database/Security folder.
2. Right Click on Users folder and select new User (I selected Windows user and added Contoso\Jose)
3. Select Membership and granted db_datareader role.
Note: Should look like:
4. Finally, hit OK
Note: If I go into properties of the AdventureWorksDW2012 database and look at Permissions tab, I’ll see Jose has Connect Permissions.
Managing or Applying Permissions to one user at a time would certainly become burdensome from a SQL Administration perspective unless you have a specific reason or intention of locking down access. If the desire to make external data available to a group of SharePoint Users, it’s better to drop the SharePoint users into an Active Directory Security Group and add the AD Security Group to SQL DB Permissions.
Question: Do the service accounts representing the C2WTS and SSRS Service Application require access to this database.
Answer: Nope. The SSRS and C2WTS will delegate the user credentials. More on this later.
SharePoint Access
Claims to Windows Token Service (C2WTS)
With SharePoint 2013, Classic Authentication is deprecated and Windows Claims Authentication is the recommended approach. SQL doesn’t yet support Windows Claims Authentication so you must convert the claims identity to a traditional Windows Identity. That’s where the Claims to Windows Token service comes in as it will convert the Claims identity into a Windows Identity that SQL understands. The Claims to Windows Token Service must be provisioned on all servers hosting BI Service Applications that access external data sources that don’t speak Windows Claims. That is, any servers running logical service instances for BI. For Example, my server App2 is running the SQL Server Reporting Services Service within Central Administrator, Services on Server:
The reason is that C2WTS communicates with Local Procedure Calls so in order for a SharePoint service to leverage it, it must reside locally on the same machine. After provisioning the C2WTS, it should be running with the designated service account within services.msc. In my case, I’m running C2WTS on my App2 server and services looks like:
Note: Yes, my C2WTS service is running with the Contoso\Magic user account
The mystery of how Claims to Windows Token Service converts a claims identity is S4U login. This is a special login that enables the C2WTS to fetch the users Windows Identity without knowing the users password. Next, it logs in via Kerberos and fetches a Kerberos service ticket representing the user and passes it back to the associated BI Service Application. In my case, the SSRS Service Application. Because it’s a special type of login or considered highly privileged, you must access the local security policy for the machine running C2WTS and grant the C2WTS service account Impersonate Client, Run as Service, and Act as Operating System. I’ll write more about this in later sections to put it all together.
Kerberos Basics
If you understand the basics, great and you can probably skip over a large chunk of this. I’ll briefly cover high level what is Kerberos before diving into Kerberos Constrained Delegation. Basically, Kerberos is an authentication protocol and used by Active Directory. It’s a ticketing based system which overcomes the traditional NTLM double hop limitation. Once a client acquires a Ticket Granting Ticket (TGT) from Active Directory, it will present the TGT to Active Directory in order to acquire a service ticket for a specific service. Once a client receives the service ticket, it presents it to the server hosting that particular service. That server decrypts the ticket and if successful the client will gain access. Active Directory requires a Service Principal Name also known as an SPN in order to locate a service. Without an SPN registered for a specific service, the client would never get a service ticket back and Kerberos authentication would fail. In the basic example of SharePoint 2013 Web Application setup with Kerberos, The service account for the web application is running as the application pool identity. The SPN for protocol and url for the web application is stamped on the service accounts AD object. Setspn is used to register an SPN and can be used to list SPN’s for a specific account. So in the below example, my Web Application URL is https://intranet.contsoso.com and the application pool identity is contoso\wasvc :
Kerberos Constrained Delegation Basics
When SSRS Service Application is configured for pass thru authentication, you’re basically telling the SharePoint Application Server hosting the SSRS Service Application that you want the SSRS service account to authenticate to a backend service on behalf of the user. That means present the backend service, (SQL in this case), the credentials of the user that made the initial call. Remember, the user credentials are encrypted in the service ticket I mentioned above.
For Example:
SSRS Service Account: Contoso\spsvc
End User: Contoso\Jose
So in the above example, the SSRS Service account presents the credentials of Jose in the form of a service ticket to the external backend SQL Server. This is referred to as Delegation. This is often confused with Impersonation which is the same thing with the exception that Impersonation takes place when the call was initiated on the same server that is calling the service. For Example, if we have a SharePoint Web Application configured with Kerberos and the user refreshes a page which requires some fetches of data from a SharePoint Content Database, the Web Front End impersonates the user to retrieve data from SQL Server. In our case with SSRS, the original call originates from a different server (Web Front End) so it uses Delegation.
Delegation is setup in Active Directory Users and Computers for the service account via the Delegation Tab. First, you must register an SPN for that account before the Delegation Tab is exposed. Remember, in my case both of my service accounts are the following:
Claims to Windows Token Service: Contoso\Magic
SSRS Service Account: Contoso\spsvc
Both of these accounts will be delegating the original user identities to the SQL server hosting the Adventure Works Database. I’ll provide more details in the behind the scenes section. But for now, take note I have Delegation Setup for both accounts like:
Notice, I set it to Trust this user for delegation to specified services only. This is the only way it works for these service accounts and this is called Constrained Delegation. Constrained means that you are defining specific services that a service ticket can be delegated to. The S4U login performed by C2WTS retrieves a constrained service ticket back which is why this is a requirement. Configuring Trust this user for delegation to any service (Kerberos only) is considered unconstrained delegation and will never work in this scenario. I point this out because this is the most common mistake I’ve seen with folks setting up BI in SharePoint. They setup unconstrained delegation and Kerberos fails to the SQL Backend as a result.
Putting it all together
I’ll take you on a journey thru all of the moving parts. The scenario is user Jose will access a BI Site in SharePoint and access an SSRS report which retrieves data from SQL Server’s External Database.
So again to recap my setup:
End User: Jose
SharePoint - Web Front End Server Name: WFE
SharePoint App Server Name hosting SSRS: APP2
SQL Backend Server Name: SQL
External Database: AdventureWorksDW2012
C2WTS Service Account: Contoso\Magic
SSRS Service Account: Contoso\spsvc
Steps are the following:
1. Jose logs into SharePoint (WFE) and his Windows Identity is converted to a Windows Claim
2. Jose accesses the Report from a SharePoint Library
3. The Web Application on WFE will talk to the SSRS Service Application Proxy and uses WCF to connect to App2.
4. SSRS Service App receives the requests along with a Windows Claims representation of the user. It understands that the requests is to retrieve data on SQL Server.
5. The SSRS first passes the Windows Claims identity over to C2WTS on the same machine in order to convert the Windows Claim to a Windows identity in the form of a service ticket.
6. C2WTS (contoso\magic) performs the S4U login and receives the service ticket back and passes it over to the SSRS Service Application
7. The SSRS Service Application (contoso\spsvc) will call SQL passing the service ticket representing the user.
8. After success, it will retrieve the data from AdventureWorksDW2012 DB and pass it back to the caller
In part 2 of this series, I’ll walk through the above steps using tools to expose the moving parts which will help one with Troubleshooting access issues involving SharePoint BI Services.
Thanks,
Russ Maxwell, MSFT
References:
https://msdn.microsoft.com/en-us/library/ff647248.aspx
https://msdn.microsoft.com/en-us/library/jj219068.aspx