Monthly Archives: June 2014

SharePoint 2013 BI Development Environment

This tutorial show you howto setup a complete SharePoint 2013 BI Development environment. The goal is to have SharePoint environment with all Business Intelligence features enabled and a more production like setup with distinguished service accounts and kerberos configuration enabled. The installation process will be partly automated using AutoSPInstaller and PowerShell scripts for installation and provisioning of service applications. The reason for this is to have a more clear and consistent method for future installations that also can be adapted to a  real world production environment.

The environment will consist of three servers: domain controller, database server and SharePoint server. To achieve those three servers we will configure a virtual environment using VirtualBox.

The environment will be “two-tier” farm setup shown in the diagram below:


SharePoint 2013 BI Development Environment

The tutorial is divided into four different posts/parts. Although they are all a part of the tutorial they are thought to be used independently as well depending on your needs. For example if you only would like to configure kerberos or only want automate an installation with AutoSPInstaller etc.


  1. SharePoint 2013 development environment using Virtualbox
  2. Install SharePoint 2013 SP1 using AutoSPInstaller
  3. Configure SharePoint 2013 Business Intelligence Services
  4. SharePoint 2013 Business Intelligence kerberos configuration

SharePoint 2013 kerberos configuration

SharePoint 2013 kerberos configuration is required in a SharePoint setup when user delegation is needed to access external data sources or other resources. This post will go through the steps you need to configure SharePoint 2013 kerberos for business intelligence services and web applications.

Most often when SharePoint is used with business intelligence this is needed since data sources and cubes etc. is stored on another server than the SharePoint machine. In the previous post Configure SharePoint 2013 BI Services we have one of those setups. However there we avoided to use kerberos by using service accounts or data access accounts to access our data sources and were not able to use integrated security option for our report/analyses data sources.

Although this post is the fourth post in the series Setup SharePoint 2013 BI Development Environment and assumes that you have made all the previous tutorials it can also be generally applied as long as you change names and urls to fit your own environment.

SharePoint 2013 BI Development Environment tutorial

  1. SharePoint 2013 development environment using Virtualbox
  2. Install SharePoint 2013 SP1 using AutoSPInstaller
  3. Configure SharePoint 2013 Business Intelligence Services
  4. >SharePoint 2013 Business Intelligence kerberos configuration<


Getting started

For this tutorial we have an environment resembling the following diagram:


 As you see in the picture above when using integrated security and a client connects to GRAMMI we would like GRAMMI to use the same user credentials to access TUMMI. For example the user opens an excel document (with excel services in the browser) with an external data source to a graph/pivot etc. and press the refresh data button. We would then expect GRAMMI to be able to connect to TUMMI using the same credentials as the client to access the data. However this will not work when using NTLM (which is what is enabled by default) and we will get what is called a NTLM double hop scenario:

ntlm double hop

With NTLM the client credential is only able to “hop” one step hence we get a failure when the SharePoint server is trying to access our database server. We will not go in to the details of kerberos in this post but if you like to dig deeper you can take a look at:

SharePoint 2010 Kerberos Configuration Guide

Plan for kerberos authentication in SharePoint 2013 and links to other resources

In the previous post Configure SharePoint 2013 BI services we avoided this double hop scenario by specifying a service account in our datasource or use a pre-configured unattended service account in SharePoint Secure Store service application. This solution has a few issues among others:

  • End users must  configure their data source to use an unattended service account or know the name and have access to another pre-configured credential in secure store
  • End users must know username/password of service account for the datasource they would like to use
  • There is no possibility to easy filter which data that should be available to the user by using their login (as for example roles and dimension filters in Analysis Services)
  • NTLM is less secure and causes more overhead on the network and traffic to the domain server that handles authentication

Test that user delegation is not working without kerberos

  • Create a workbook with a connection to Analysis Services using current user in authentication settings for the data connection (This is the default setting when adding a new server connection) and create a PivotTable. (Your current user will need to have access to the Analysis Server and the cube)
  • Upload/save the workbook to SharePoint.
  • Open the workbook in SharePoint using Excel Services -> try to drill down/change the PivotTable. You should get the following error message:
    external data refresh failed excel sercvices sharepoint 2013

If you look in the SharePoint logs you will also find an entry for:

SPSecurityContext: Could not retrieve a valid windows identity for username 'GUMMIS\SP2013_Install' with UPN ''. UPN is required when Kerberos constrained delegation is used. Exception: System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: WTS0003: The caller is not authorized to access the service. (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: System.UnauthorizedAccessException: WTS0003: The caller is not authorized to access the service.   

So this is what we will solve in the rest of this article.

Service account permissions for kerberos

This post assumes you have configured all service accounts below as managed accounts and also provisioned all BI service applications with a dedicated account as described in Configure SharePoint 2013 BI Services. If not make sure to adapt further settings to your environment.

Before continue, please configure all service accounts in your environment accordingly to the table below:

Account Security settings

Claims to windows token service
(Service account for: Claims To Windows Token Service Application)


  • Need to be in local administrators group
  • In local security policy
    (Open gpedit.msc as administrator -> Windows Settings -> Security Settings -> Local Policies > User rights assignment)
    • Act as part of operating system
    • Log on as a service
    • Impersonate a client after authentication
  • Edit the file:
    C:\Program Files\Windows Identity Foundation\v3.5\c2wtshost.exe.config
    Ensure the file has a section configured as below:

          <add value=”WSS_WPG” />


Farm account


  • Nothing “special” needed if service applications and everything else is configured using instructions in SharePoint 2013 BI Development Environment.

Excel Services
(Service account for: Excel Service Service Application)


  • Admin on Analys Services (SSAS) for SharePoint instance (GRAMMI\POWERPIVOT).
  • On SQL Server, locate Central Administration database
    (Usually SharePoint_AdminContent). 
    Expand security folder -> add/open setttings for SP2013_ExcelService account -> in the mappings settings tick the SPDataAccess role checkbox -> OK.



PowerPivot Services
(Service account for: PowerPivot Service Application)

Security rights:

  • Admin on Analys Services (SSAS) for SharePoint instance. (GRAMMI\POWERPIVOT)
  • In local security policy
    • Act as part of operating system

Performance Point Services
(Service account for: Reporting Services Service Application)


  • No additional settings except what is done automatically when provisioning service application.



Reporting Services

(When using reporting services in integrated mode with reporting services service application on the same server as your site web application then both the web application and reporting services service application need to use the same service account!)

In this setup the SP2013_WepApp will be used as service account for reporting services and no dedicated reporting services account will be used.


  • Admin rights on Analys Services (SSAS) for SharePoint instance. (GRAMMI\POWERPIVOT)(Needed if you would like to use PowerPivot as source for PowerView reports)
  • Read/admin rights on Analys Services (SSAS) tabular instances/databases that you which to use as source for PowerView.
  • Edit the file:
    C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\WebServices\Reporting\rsreportserver.config

    Change AuthenticationTypes RSWindowsNTLM to RSWindowsNegotiate and ensure the file has a section configured as below:



Site web application pools
(Service account for: Reporting Services and)
(Application pool account for portal web applications)

Security rights:

  • Read text in red and security rights for SP2013_RS account.

Installation account

This account will be used for installation and configuration of the BI services with the exception of PowerPivot Configuration Tool which have some issues to detect proper user permissions. (described later)


  • Member of Local Administrators security group
  • Member of Central Administrators group in Central Administration.

Unattended Data Access account
(If you like to use unattended data access or secure store account for BI services)


  • Read access to all data sources used by BI services.
    (TUMMI default SQL, TUMMI default SSAS)

Note: If you are going for kerberos configuration later you might not need this account.

*End users*


  • Read access to all data sources
  • Access to SharePoint BI portal site
  • The BI portal site should be added as trusted site in Internet Explorer

Configure Claims To Windows Token Service (C2WTS)

Ensure the C2TWS service is running

Open Central Administration -> Service On Server:

claims to windows token service central administration

Change the service account for C2WTS service

Ensure that you have added your C2WTS service account (SP2013_C2WTS) as a managed account. Run the following PowerShell script as administrator in SharePoint 2013 Management Shell:

# Change Claims to Windows Token Services service account 
$c2twsAccount = "GUMMIS\SP2013_C2WTS"
Add-PSSnapin Microsoft.SharePoint.PowerShell

[string] $Identity = $c2twsAccount
[string] $ServiceTypeName = "Claims to Windows Token Service"
#Get Reference to Service
$Service = (Get-SPFarm).services | where {$_.typename -eq $ServiceTypeName}
#Get Reference to Managed Account
$IdentityManagedAcct = Get-SPManagedAccount -Identity $Identity
#Get Reference to Process Identity and Update Identity
$SvcProcessIdentity = $Service.ProcessIdentity
$SvcProcessIdentity.CurrentIdentityType = [Microsoft.SharePoint.Administration.IdentityType]::SpecificUser
$SvcProcessIdentity.Username = $IdentityManagedAcct.UserName

 You can also download the script Configure SharePoint 2013 BI Services.ps1 which has function to create BI service applications and change C2WTs token service service account.

Add/verify DNS A records

For kerberos to work you will need to add DNS a records to the web applications that you would like to setup with SharePoint 2013 kerberos delegation. Please verify that alternate access mappings are properly for your web applications as well. For more information see previous post Install SharePoint 2013 SP1 using AutoSpInstaller – Configure DNS

We currently have the following A records:

Create SPN’s

Create fake SPN’s (This will enable delegation tab in Active Directory Active Directory and Users which is not shown by default)

Logon to your domain server (ZUMMI). Start a command prompt as domain adminitrator.

Create fake SPN’s

Setspn -S SP/Excel GUMMIS\SP2013_ExcelServices
Setspn -S SP/PowerPivot GUMMIS\SP2013_PowerPivot

Please note that SP2013_RS is only needed if you do not run the SSRS service application as the web application account (SP2013_WebApp)

Create web applications SPN’s

Setspn -S HTTP/portal SP2013_WebApp
Setspn -S HTTP/ SP2013_WebApp
Setspn -S HTTP/mysites SP2013_WebApp
Setspn -S HTTP/ SP2013_WebApp

Create SQL server services SPN’s

SPN For SQL Default Instance
Setspn -S MSSQLSvc/tummi:1433 GUMMIS\SQL2012_Services
Setspn -S MSSQLSvc/ GUMMIS\SQL2012_Services
SPN For SSAS Default Instance
Setspn -S MSOLAPSvc.3/tummi GUMMIS\SQL2012_Services
Setspn -S MSOLAPSvc.3/ GUMMIS\SQL2012_Services

SPN For SSAS TABULAR Named Instance
(We have not used a TABULAR instance in our installation, but still here to use as reference if needed)

Setspn -S MSOLAPSvc.3/tummi:TABULAR GUMMIS\SQL2012_Services
Setspn -S MSOLAPSvc.3/ GUMMIS\SQL2012_Services

In order for named instance to be found you also need to register SQL Browser Server for Kerberos

Setspn -S MSOLAPDisco.3/tummi GUMMIS\SQL2012_Services

When creating the spn you will get a reponse indicating that the object is updated/registred as below:

setspn sharepoint 2013 kerberos

Setup delegation

Trust SharePoint 2013 server for kerberos delegation

Open Active Directory Users and Computers on the domain server (Zummi). Browse to the SharePoint 2013 server. (

Right click on server (GRAMMI) -> Properties -> Delegation tab -> Trust this computer for delegation to any service (kerberos only):

ad enable kerberos delegation sharepoint server

Web application(s)

Do the following section for all web applications you wish to use with kerberos.

On SharePoint 2013 server (GRAMMI) open up Central Administration -> Web Applications -> Select web application -> Authentication provider -> Click Default -> Change from NTLM to Kerberos:

sharepoint 2012 sp1 central administration web application kerberos authentication 

Verify IIS settings

Open Internet Information Services Manager (inetmgr). Select SharePotin 2013 web application. Verify that windows authentication is enabled:

sharepoint 2013 kerberos iis auth configuration

Note: If you get a warning message complaining that both forms and windows authentication is enabled then just ignore the message.

Select windows Authentication. In the right action pane select Advanced Settings. Then verify that extended protected mode and kernel authentication mode is switched off. If this gives you another warning – ignore it.

sharepoint 2013 kerberos iis windows auth configuration advanced


Also make sure you have kerberos and NTLM in the providers configuration:

sharepoint 2013 kerberos iis windows auth configuration providers


Also verify that IIS access mappings and bindings are correct.

Delegate access to services

In Active Directory – Manage users & computers find the users for the services that you configured fake SPN’s in previous steps. E.g. accounts for:

  • Claims to windows token service (GUMMIS\SP2013_C2WTS)
  • Reporting Services (GUMMIS\SP2013_WebApp or SP2013_RS)
    (See Service Account Permissions for Kerberos)
  • Excel Services (GUMMIS\SP2013_ExcelServices)
  • PowerPivot (GUMMIS\SP2013_PowerPivot)
  • Performance Point Services (GUMMIS\SP2013_PPS)

Right click user -> properties -> delegation -> Ensure you do this by the “Trust this computer for delegation to specified services only” option -> Add SPN’s you create earlier for SQL, SSAS, SSAS Tabular etc. (You can find the SPN’s by searching for the service account e.g. GUMMIS\SQL2012_Services)

D SharePoint 2013 kerberos service accounts delegate services


Open your SharePoint web application in the browser. Verify that a kerberos ticket have been issued by running klist command in command prompt (as administrator). You should see a list of all kerberos ticket issued for your user. Make sure that a ticket have been issued for your web application similar as below:

sharepoint 2013 kerberos ticket klist

You can also look in the evnet logs of the domain server for Logon events related to kerberos:

  • 4768 – A Kerberos authentication ticket (TGT) was requested.
  • 4771 – Kerberos pre-authentication failed.
  • 4772 – A Kerberos authentication ticket request failed.

event kerberos ticket

If you do not get a ticket issued when loggin on to your site. Make sure that you have given the C2WTS (SP2013_C2WTS) and Web Application (SP2013_WebApp) proper permissions (See Service Account Permissions for Kerberos).

If it still does not work ensure that you have created all kerberos SPN’s correctly and that no duplicates or invalid entries exists.

When kerberos works for your web applications verify that your BI services work correctly by accessing data sources using “current user” and integrated security.