Flowing Identity in the
Microsoft BI Stack using
Claims-based Authentication
If you are adopting Active Directory Federation Services (ADFS) as a method of
user authentication for SharePoint 2013 and desire to implement role based
(per-user) security with Microsoft Business Intelligence (BI) tools accessing
enterprise data sources, one challenge you’ll find is bridging the gap between
SAML-based authentication used by ADFS and the proprietary Windows
Authentication used by enterprise data sources, like SQL Server Analysis
Services (SSAS).
This problem is important because more and more organizations are adopting
claims-based authentication for internal and/or external user access. Any
back-end system that does not support SAML claims can become inaccessible
without implementing some kind of translation system between SAML and the
system’s native authentication mechanism. Custom solutions can be created,
but they can be cumbersome to manage. Also, user mapping is error prone and
introduces another point of failure.
White Paper
Matt Youngstrom
Greg Moser
Jared Zagelbaum
“…more and more organizations are adopting claims-based
authentication for internal and/or external user access."
Introduction
The complexity required in translating authentication
methods can be mitigated if one is willing to accept
a many-to-one mapping of users to a single service
account. However, with the reduced management
headaches, you also lose the ability to track usage and
restrict authorization at a per-user level. This can be
an unacceptable risk, especially if you are in a heavily
regulated business in which security and data privacy
compliance is of utmost importance. Even in less regulated
environments, the practice of mapping roles to service
accounts requires that multiple copies of each data
visualization definition be maintained for each unique
emulated role. Better yet to leverage OOTB data security
included in enterprise tools like SSAS, and maintain
this security at the source layer. This ensures that the
visualization tools used by developers or business users
are the consumers of security, and not entrusted with the
role of enforcing data authorization.
This white paper looks at the SAML to proprietary problem
within the context of ADFS, SharePoint, and SSAS. It will
examine the problem in detail, show a solution, and provide
a roadmap for its implementation. It does not provide
instructions for installing the various components; references
will be made to the relevant TechNet articles within the
context of this white paper for installation instructions.
ADFS is different in that it only interacts with other
systems using SAML. While SAML succeeds in simplifying
proprietary authentication methods into a single crossplatform standard, it also requires that all target systems
implement SAML, which is not yet a reality.
SharePoint is able to work with SAML authentication.
Whereas in 2010 it was an option, in SharePoint 2013
all users are only represented in SAML internally to
SharePoint regardless of the originating authentication
mechanism. When a SharePoint service application like
Excel Services needs to authenticate with an external
system that does not support SAML claims (in this case
SSAS), a service running in SharePoint called the Claims
to Windows Token Service (C2WTS) converts the user’s
SAML claim back to a Windows token. But the C2WTS
will not convert any SAML claim to a Windows token; it is
hard-coded to verify that the SAML claim was generated
based on a Windows token. If not, no Windows token is
generated and authentication with SSAS fails.
The following diagram summarizes this process by
illustrating what happens when a user authenticated with
a Windows token accesses an Excel workbook (with an
SSAS data source and a specified per-user connection)
through Excel Services. Notice the presence of the Secure
Token Service (STS), which is responsible for initially
converting the user’s Windows token into a SAML token for
Examining the Issue
SharePoint consumption. This is an internal, OOTB process
Traditionally, Single-Sign-On (SSO) solutions provide
created by an administrator at the farm level to map user
“drivers” that allow a user’s identity to be translated into a
ids to stored credentials (which in our case defeats the
format that the target system requires. As an SSO solution,
purpose of what we are trying to accomplish).
to SharePoint, and is different from the STS that can be
1 | Flowing Identity in the Microsoft BI Stack using Claims-based Authentication
DESKTOP
SERVER
INTERNET BROWSER
Excel Workbook
1
IPrincipal
SHAREPOINT WEB
FRONT-END
SHAREPOINT
SECURE TOKEN
SERVICE
2
Excel Web Access
IClaims
Principal
SHAREPOINT APP SERVER
Excel Services
3
Content
Database
4
CLAIMS TO WINDOWS
TOKEN SERVICE
Excel Workbook
4.5
5
DOMAIN CONTROLLER
(AD)
SSAS
Kerberos Constrained
Delegation configured
If the authentication method originating with the request is not Windows Authentication
(at #1 above), such as ADFS, the orange line effectively stops at #4 (though in actuality an
anonymous Windows Token is generated despite failing to create a Windows token for the
user and that anonymous token is passed to SSAS instead).
The Solution
Because ADFS is of concern, we need to transition from ADFS-generated SAML Claims to Windows Tokens prior to
authenticating to SharePoint. In other words, what is needed is this:
WINDOWS TOKEN
ADFS-generated
SAML Claim
WINDOWS TOKEN
Sharepoint-generated
SAML Claim
WINDOWS TOKEN
2 | Flowing Identity in the Microsoft BI Stack using Claims-based Authentication
WINDOWS TOKEN
SSAS
“The added peace of mind gained from being able to track
SSAS access on a per-user basis is well worth the configuration
and effort, especially if your business is highly regulated and
depends on a well thought-out data privacy strategy.”
One way to solve this problem is using a reverse proxy
The new WAP server role’s purpose is to proxy
server that would inspect the incoming request, reformulate
authentication requests to designated applications on
it, and pass it on to the target application, which in this case
behalf of ADFS (utilizing Kerberos constrained delegation)
is SharePoint. Microsoft has had products that provided
and redirect the user if successfully authenticated. These
such functionality, such as the Unified Access Gateway
target applications can use SAML for authentication or
(UAG). However, UAG is expensive, bulky, and slated
Windows Authentication. In the case of the latter, WAP
for retirement. Fortunately, in the latest incarnation of
converts the ADFS SAML Token into a Windows Token
Windows Server 2012, dubbed R2, a new feature has been
before forwarding the request onto the target application.
added called the Web Application Proxy (WAP) which is
Given our scenario, this is exactly what is needed.
capable of meeting our solution requirements.
Implementation
To prove that this does indeed work, an environment was built utilizing Windows Azure for infrastructure.
The following diagram shows the server farm and its components:
SHAREPOINT
DATA WAREHOUSE / BI
Windows Server 2012
SQL Server 2012 SP1
Enterprise
DBEngine, SSAS
Windows Server 2012
SharePoint 2013
Web Front-End/Application Server
Windows Server 2012
2012 R2
Web Application Proxy
Windows Server 2012
SQL Server 2012 SP1 Enterprise
SharePoint Content Database Server
Windows Server 2012
2012 R2
ADFS
HTTPS EndPoint
DOMAIN CONTROLLERS
Windows Server 2012
Windows Server 2012
Primary Domain Controller
Backup Domain Controller
3 | Flowing Identity in the Microsoft BI Stack using Claims-based Authentication
Internet
The process was tested using a SharePoint Business
Excel workbook (note that the internet’s DNS would be
Intelligence site with an Excel workbook connected to
updated such that requests to the SharePoint-enabled web
an SSAS Cube (specifying a per-user connection). The
application are directed to the WAP but for the sake of the
diagram below describes what happens when the user
POC, we configured our local hosts file in lieu of making
tries to authenticate to the SharePoint site and access the
internet DNS changes):
Windows
SHAREPOINT
DATA WAREHOUSE / BI
3
Windows Server 2012
SQL Server 2012 SP1
Enterprise
DBEngine, SSAS
Windows
Windows Server 2012
SharePoint 2013
Web Front-End/Application Server
Windows Server 2012
2012 R2
Web Application Proxy
SAML
Windows Server 2012
SQL Server 2012 SP1 Enterprise
SharePoint Content Database Server
Windows Server 2012
2012 R2
ADFS
HTTPS EndPoint
SAML
3
2
1
Internet
1) When browsing to the site’s URL, the login screen for ADFS appears (either the out-of-the-box screen or
a custom login screen) as though the user was logging into ADFS. When the user enters credentials, the
WAP authenticates against the ADFS directory and then, if authentication is successful, and if the proxy
is configured to redirect to a windows authentication-enabled web application, it converts the ADFSgenerated SAML token to a Windows token and passes the Windows token to the target SharePoint windows
authentication-enabled web application.
2) SharePoint then takes the Windows token and converts it back to a SAML token for internal use via its
own internal STS. When a request is made to view an Excel workbook with a connection specifying per
user identity to an SSAS Cube, Excel Services utilizes the C2WTS to convert the SharePoint-generated SAML
token successfully back to a Windows token (because in this case the SAML token was generated based on a
Windows token).
3) Authentication then proceeds to SSAS which, if the user has the appropriate access, is successfully
authenticated using Windows authentication.
4 | Flowing Identity in the Microsoft BI Stack using Claims-based Authentication
1) T he user navigates to the URL of the target
application and is directed to the WAP
2) T he WAP communicates with the ADFS
server and redirects the user to ADFS
3) A
DFS displays the ADFS login page
4) T he user enters credentials
5) I f authenticated, ADFS communicates back
to the WAP that it’s OK to redirect to the
target site
6) T he WAP passes the user’s credentials
to the SharePoint web application and
proxies subsequent HTTP requests to and
from SharePoint
Another way of looking at this:
SHAREPOINT
Name: SPWEBAPP1
Windows Server 2012
SharePoint 2013
Web Front-End & App Server
Web App
6
5
Windows Server 2012 R2
Web Application Proxy
2
Windows Server 2012 R2
ADFS
3
ADFS
Login Page
4
1
Installation
Configuration
As was mentioned in the introduction, detailed
While you can gain most of what you need to know from
instructions for installation are not provided. Please refer
the installation documentation, the following configuration
to the following TechNet articles for ADFS, WAP, and
areas were not straightforward, and so they are detailed
SharePoint 2013 installation planning and instructions.
here for your reference. Keep in mind that the amount
ADFS
of configuration depends on how the environment is
scaled to meet usage demands, so in some ways this is
http://technet.microsoft.com/en-us/library/
only representative of the environment noted above and
dd807092(v=ws.10).aspx
not for your particular implementation. Nonetheless, the
WAP
http://technet.microsoft.com/en-us/library/dn383659.aspx
environment referenced in this white paper is intentionally
simple, and allows you to easily extrapolate what you need
from the basic artifacts it contains.
SharePoint 2013 on Azure IaaS
http://www.microsoft.com/en-ca/download/details.
aspx?id=38428
5 | Flowing Identity in the Microsoft BI Stack using Claims-based Authentication
Kerberos
In order for the initial SAML token from ADFS to be converted to a windows token, the target web application must be
configured for windows authentication. But the environment as a whole needs to be configured for Kerberos and specifically for
constrained delegation.
While a discussion on Kerberos is beyond the scope of this post, here is a high-level overview of the configuration:
1) S ervice Principal Names (SPN) needed to be set up for the following:
a. The target SharePoint web application
b. The SSAS service
2) B
oth the WAP and the ADFS servers need to be able to delegate to the target SharePoint web applications.
3) E xcel Services needs to be able to delegate to SSAS.
4) T he C2WTS needs to be able to delegate to SSAS.
The following diagram shows this in more detail:
SHAREPOINT
DATA WAREHOUSE / BI
Name: SPWEBAPP1
Name: SQLBI1
Windows Server 2012
Windows Server 2012
SharePoint 2013
SQL Server 2012 SP1 Enterprise
Web Front-End & App Server
Web App Pool
Service Account: DOMAIN\SP_FARM_DB
SSAS
Service Account:
DOMAIN\
SQLBISERVICE
Excel Service App Pool
Service Account: DOMAIN\SP_BI*
Claims to Windows Token Service (C2WTS)
Service Account: DOMAIN\C2WTS*
Windows Server 2012
SQL Server 2012 SP1
Enterprise
SPN: MSOLAPSVC.3/SQLBI1.int DOMAIN\SQLBISERVICE
SPN: MSOLAPSVC.3/SQLBI1 DOMAIN\SQLBISERVICE
Name: WEBPROXY1
Name: ADFS1
Windows Server 2012 R2
Web Application Proxy
Windows Server 2012 R2
ADFS
SPN: HTTP/spwebapp1.int DOMAIN\SP_FARM_DB
SPN: HTTP/spwebapp1 DOMAIN\SP_FARM_DB
Constrained Delegation:
COMPUTER: WEBPROXY1 -> HTTP/SPWEBAPP1
Constrained Delegation:
SERVICE ACCOUNT: DOMAIN\C2WTS -> MSOLAPSVC.3/SQLBi1
Constrained Delegation:
COMPUTER: ADFS1 -> HTTP/SPWEBAPP1
Constrained Delegation:
SERVICE ACCOUNT: DOMAIN\SP_BI -> MSOLAPSVC.3/SQLBi1
* 2 "dummy" SPNs must be created for DOMAIN\SP_BI and DOMAIN\C2WTS so that the
delegation tab appears in the account properties in the Active Directory Users and Computers
snap-in OR utilize PowerShell to configure constrained delegation.
6 | Flowing Identity in the Microsoft BI Stack using Claims-based Authentication
Domain Name System
The internet domain name system (DNS) needs to be
configured such that:
1) T he domain name for the SharePoint web application is
mapped to the IP address of the WAP
SharePoint Domain Name -> IP Address of WAP
ADFS Domain Name -> IP Address of WAP
2) T he domain name for the ADFS server is mapped to the
IP address of the WAP
Secure Sockets Layer
In general, it is recommended to utilize SSL whenever possible. For the purposes of the WAP and ADFS, however, two
certificates for SSL are required for securing the wire between:
1) t he WAP and ADFS
2) t he WAP and the SharePoint Web Application
Without these certificates, the WAP will not communicate with either system. SSL encryption elsewhere is not required,
but recommended.
SHAREPOINT
Name: SPWEBAPP1
SharePoint
SSL Cert
Windows Server 2012
SharePoint 2013
Web Front-End & App Server
ADFS
SSL Cert
Web App
6 SSL
5 SSL
Windows Server 2012 R2
Web Application Proxy
2
SSL
3
Windows Server 2012 R2
ADFS
SSL
4 SSL
1 SSL
Both certificates must be installed in the "Personal" as well as "Trusted Root Certification Authorities"
in the Computer Certificates snap-in on the WAP
7 | Flowing Identity in the Microsoft BI Stack using Claims-based Authentication
ADFS
Login Page
Conclusion
While this method bridges the gap between utilizing ADFS
for login and windows authentication for SharePoint,
at the end of the day the WAP is a proxy. Clients never
really access the target SharePoint site directly; all
traffic is handled through the proxy. Each time a new
web application in SharePoint is added that will be
used externally, you will need to register it in the WAP
configuration so that the WAP knows how to proxy
requests based on their target addresses. Still, this is
a fairly small price to pay for being able to utilize the
strengths of ADFS-based authentication with the ability to
use back-end systems that require credential delegation
to operate correctly. The added peace of mind gained
from being able to track SSAS access on a per-user basis
is well worth the configuration and effort, especially if
your business is highly regulated and depends on a well
thought-out data privacy strategy.
About Magenic
Founded in 1995 by the same technical minds that still run the company,
Magenic focuses on the Microsoft stack and mobile application development.
Visit us at magenic.com or call us at 877.277.1044 to learn more or to engage
Magenic today.
8 | Flowing Identity in the Microsoft BI Stack using Claims-based Authentication