Flowing Identity in the Microsoft BI Stack using Claims-based Authentication

(1)

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.

Flowing Identity in the

Microsoft BI Stack using

Claims-based Authentication

(2)

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.

Examining the Issue

Traditionally, Single-Sign-On (SSO) solutions provide “drivers” that allow a user’s identity to be translated into a format that the target system requires. As an SSO solution,

ADFS is different in that it only interacts with other

systems using SAML. While SAML succeeds in simplifying

proprietary authentication methods into a single cross-platform 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 SharePoint consumption. This is an internal, OOTB process to SharePoint, and is different from the STS that can be

created by an administrator at the farm level to map user

ids to stored credentials (which in our case defeats the

purpose of what we are trying to accomplish).

“…more and more organizations are adopting claims-based

authentication for internal and/or external user access."

(3)

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 WINDOWS TOKEN

SSAS

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).

ADFS-generated

SAML Claim Sharepoint-generated SAML Claim

DESKTOP

SERVER

Excel Workbook

INTERNET BROWSER

Excel Web Access

SHAREPOINT WEB FRONT-END SHAREPOINT SECURE TOKEN SERVICE CLAIMS TO WINDOWS TOKEN SERVICE IPrincipal IClaims Principal SSAS DOMAIN CONTROLLER (AD) Kerberos Constrained Delegation configured 1 2 3 Content Database Excel Services

SHAREPOINT APP SERVER 4

4.5

Excel Workbook

(4)

SHAREPOINT

DOMAIN CONTROLLERS

One way to solve this problem is using a reverse proxy

server that would inspect the incoming request, reformulate

it, and pass it on to the target application, which in this case is SharePoint. Microsoft has had products that provided such functionality, such as the Unified Access Gateway (UAG). However, UAG is expensive, bulky, and slated for retirement. Fortunately, in the latest incarnation of

Windows Server 2012, dubbed R2, a new feature has been

added called the Web Application Proxy (WAP) which is capable of meeting our solution requirements.

The new WAP server role’s purpose is to proxy

authentication requests to designated applications on behalf of ADFS (utilizing Kerberos constrained delegation) and redirect the user if successfully authenticated. These target applications can use SAML for authentication or Windows Authentication. In the case of the latter, WAP

converts the ADFS SAML Token into a Windows Token

before forwarding the request onto the target application. Given our scenario, this is exactly what is needed.

“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.”

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:

DATA WAREHOUSE / BI Windows Server 2012 SQL Server 2012 SP1 Enterprise DBEngine, SSAS Windows Server 2012 Primary Domain Controller

Windows Server 2012 Backup Domain Controller Windows Server 2012

SharePoint 2013

Web Front-End/Application Server

Windows Server 2012 2012 R2

Web Application Proxy

Windows Server 2012 2012 R2 ADFS HTTPS EndPoint Internet Windows Server 2012 SQL Server 2012 SP1 Enterprise SharePoint Content Database Server

(5)

SHAREPOINT 3 DATA WAREHOUSE / BI Windows Server 2012 SQL Server 2012 SP1 Enterprise DBEngine, SSAS

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

ADFS-generated 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.

The process was tested using a SharePoint Business Intelligence site with an Excel workbook connected to

an SSAS Cube (specifying a per-user connection). The

diagram below describes what happens when the user

tries to authenticate to the SharePoint site and access the

Excel workbook (note that the internet’s DNS would be updated such that requests to the SharePoint-enabled web

application are directed to the WAP but for the sake of the POC, we configured our local hosts file in lieu of making internet DNS changes):

Windows Server 2012 SharePoint 2013

Web Front-End/Application Server

Windows Server 2012 2012 R2

Web Application Proxy

Windows Server 2012 2012 R2 ADFS HTTPS EndPoint Internet Windows Server 2012 SQL Server 2012 SP1 Enterprise SharePoint Content Database Server

Windows Windows SAML SAML 1 2 3

(6)

1) The user navigates to the URL of the target

application and is directed to the WAP

2) The WAP communicates with the ADFS

server and redirects the user to ADFS

3) ADFS displays the ADFS login page

4) The user enters credentials

5) If authenticated, ADFS communicates back

to the WAP that it’s OK to redirect to the

target site

6) The WAP passes the user’s credentials

to the SharePoint web application and

proxies subsequent HTTP requests to and

from SharePoint

Installation

As was mentioned in the introduction, detailed

instructions for installation are not provided. Please refer to the following TechNet articles for ADFS, WAP, and SharePoint 2013 installation planning and instructions.

ADFS

http://technet.microsoft.com/en-us/library/ dd807092(v=ws.10).aspx

WAP

http://technet.microsoft.com/en-us/library/dn383659.aspx

SharePoint 2013 on Azure IaaS

http://www.microsoft.com/en-ca/download/details. aspx?id=38428

Another way of looking at this:

SHAREPOINT

Windows Server 2012 SharePoint 2013 Web Front-End & App Server

Name: SPWEBAPP1

Web App

Windows Server 2012 R2

Web Application Proxy Windows Server 2012 R2ADFS Login PageADFS

1 4 5 2 3 6

Configuration

While you can gain most of what you need to know from

the installation documentation, the following configuration areas were not straightforward, and so they are detailed

here for your reference. Keep in mind that the amount

of configuration depends on how the environment is

scaled to meet usage demands, so in some ways this is

only representative of the environment noted above and not for your particular implementation. Nonetheless, the environment referenced in this white paper is intentionally

simple, and allows you to easily extrapolate what you need

(7)

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) Service Principal Names (SPN) needed to be set up for the following:

a. The target SharePoint web application

b. The SSAS service

2) Both the WAP and the ADFS servers need to be able to delegate to the target SharePoint web applications.

3) Excel Services needs to be able to delegate to SSAS. 4) The C2WTS needs to be able to delegate to SSAS.

The following diagram shows this in more detail:

SHAREPOINT DATA WAREHOUSE / BI

Windows Server 2012 SQL Server 2012 SP1 Enterprise

Name: SPWEBAPP1 Name: SQLBI1

SPN: HTTP/spwebapp1.int DOMAIN\SP_FARM_DB SPN: HTTP/spwebapp1 DOMAIN\SP_FARM_DB

SPN: MSOLAPSVC.3/SQLBI1.int DOMAIN\SQLBISERVICE SPN: MSOLAPSVC.3/SQLBI1 DOMAIN\SQLBISERVICE

Constrained Delegation:

SERVICE ACCOUNT: DOMAIN\C2WTS -> MSOLAPSVC.3/SQLBi1

COMPUTER: WEBPROXY1 -> HTTP/SPWEBAPP1

Windows Server 2012 SQL Server 2012 SP1

Enterprise

Web App Pool

Service Account: DOMAIN\SP_FARM_DB

Excel Service App Pool

Service Account: DOMAIN\SP_BI*

Claims to Windows Token Service (C2WTS)

Service Account: DOMAIN\C2WTS*

SSAS

Service Account: DOMAIN\ SQLBISERVICE

Windows Server 2012 R2 Web Application Proxy

Name: WEBPROXY1

Windows Server 2012 R2 ADFS

(8)

Domain Name System

The internet domain name system (DNS) needs to be

configured such that:

1) The domain name for the SharePoint web application is

mapped to the IP address of the WAP

2) The 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) the WAP and ADFS

2) the 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 Domain Name -> IP Address of WAP ADFS Domain Name -> IP Address of WAP

SHAREPOINT

Name: SPWEBAPP1

Web App

Windows Server 2012 R2

Web Application Proxy Windows Server 2012 R2ADFS Login PageADFS

1 SSL 4 SSL 5 SSL 2 SSL SSL 3 6 SSL SharePoint SSL Cert ADFS SSL Cert

Both certificates must be installed in the "Personal" as well as "Trusted Root Certification Authorities" in the Computer Certificates snap-in on the WAP

(9)

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.

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.