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

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

SP\SP_C2WTS	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
$SvcProcessIdentity.Update()
$SvcProcessIdentity.Deploy()

6. Create SPN Records

Setspn -S SP/C2WTS SP\SP_C2WTS
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.