Tuesday, January 19, 2016

Disable warning : “Query and Refresh Data Be careful. The query to get the data might be unsafe so you should only refresh the workbook if you trust its source”. in excel service data refresh

In one of my environments, I got the warning message every time I refresh or drill down content in my workbooks. Following was the exact message


Following were the steps I used to resolve the error.

1. Navigate to Excel Services service application


2. Click on Trusted File Locations link


3. Click on the desired http URL, and untick the setting shown below


Monday, January 11, 2016

Configure Kerberos for SharePoint 2013–PowerShell Scripts

Recently I had to reconfigure an existing web application to use Kerberos authentication. The objective was to allow my Business Intelligence environment to use impersonation account for row level security.

In my environment I had a Domain Controller server, SQL Server, Analysis Server configured in Tabular mode and a SharePoint server

Following were the steps I used to perform the configuration

1. Prepare service accounts.

Following were my service accounts

SharePoint Claims to windows token service service account
  • SP\SP_Farm
SharePoint farm account
  • SP\SP_Services
ExcelServices app pool account
  • SP\SP_Services
PowerPivot app pool account
  • SP\SP_PortalAppPool
Web Application app pool account
  • SP\SP_Admin   
SharePoint installation account
  • SP\SP_UnattendedService
Excel services data refresh account

2. Add “SP\SP_C2WTS” Account to local administrator group in the SharePoint server

3. Set accounts in local security policy





4. Start the Claims to Windows Token Service in SharePoint


5. We have to change the service account for Claims to Windows Token Service.

$ct = "SP\SP_C2WTS"
$Identity = $ct
$ServiceTypeName = "Claims to Windows Token Service"

$Service = (Get-SPFarm).services | where {$_.typename -eq $ServiceTypeName}
$IdentityManagedAcct = Get-SPManagedAccount -Identity $Identity

$SvcProcessIdentity = $Service.ProcessIdentity
$SvcProcessIdentity.CurrentIdentityType = [Microsoft.SharePoint.Administration.IdentityType]::SpecificUser
$SvcProcessIdentity.Username = $IdentityManagedAcct.UserName

6. Create SPN Records

Setspn -S SP/Excel SP\SP_Services
Setspn -S SP/PowerPivot SP\SP_Services

7. Set SPN records for Web Applications

Setspn -S HTTP/portal.contoso.com SP_PortalAppPool
Setspn -S HTTP/my.contoso.com SP_ProfilesAppPool

8. Set SPN for SQL Server

Setspn -S MSSQLSvc/sql.contoso.com:1433 SP\SQL_Admin

9. Set SPN for Analysis Server Tabular

Setspn -S MSOLAPSvc.3/sql.contoso.com SP\SQL_OLAP_Admin

10. Set SPN for SQL browser

Setspn -S MSOLAPDisco.3/pivot.contoso.com SP\SQL_OLAP_Admin

11. Trust SharePoint Server for delegation

Navigate to Domain Controller and Active Directory Users and Computers



12. Change Web Application settings


That’s all we have to do.