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.

SharePoint 2013–Update password of a managed account using PowerShell

We can change the password of a SharePoint managed account from SharePoint central administration console as well as using PowerShell.

Fun fact here is that, we don’t need to do anything from our Domain Controller. When we update the password either from Central Administration or PowerShell, it will automatically update the user’s password in Active Directory.

Update managed account password in Central Administration

Update managed account password using PowerShell

Set-SPManagedAccount -identity SP\SP_Farm -NewPassword (Convertto-Securestring "mynewpassword" -AsPlainText -Force) -SetNewPassword

Set home directories for multiple clients in CygWin SFTP–Windows Server 2012 R2

In one of my SharePoint environments, we have a file server configured with SFTP. SFTP is used to securely transfer content from each user using their comfortable FTP clients. Those content will later be processed by custom tool and uploaded to analysis server for data analysis.

We have configured SFTP using CygWin.

The environment need to support multiple clients where each client has multiple users. Following were some requirements

• Separate drive (“F”) to store uploaded content
• Each client should have a folder under SFTP root data directory
• Each user should have a separate folder under his client
• Specific user can interact with his own content only
• Specific user can’t view other users folders

Following were the steps I used to configure home directories and their permissions

1. Create domain user groups in Domain Controller

2. Add users to respective user groups (clients)

3. Perform configurations in SFTP to specify home directories for each client

• Navigate to /etc/fstab file
• Specify home directories as given below

4. Open CygWin Shell and execute following commands to obtain user information

5. Change home directories of users in /etc/passwd file as shown below

6. Create home directories in F drive and create folders for each client

7. Create sub folders for each users within the client

8. Deny permissions for SFTP user groups in CygWin root directory

9. Deny permission for SFTP user groups in SFTP root directory (F:/FTP)

10. Now we need to provide permission to specific user folders. We need to disable permission inheritance as shown below

11.  Check using FTP client

If we try to browse the directory in client level, an error message will be displayed

That is the expected behavior. that means the a specific user can interact with his own content only.

Enable “BI Semantic Model Connection” content type in SharePoint 2013 site collection

In one of my SharePoint environments, I had configured PowerPivot for SharePoint service application.

My requirement was to create a new client site collection and create a BISM connection to my analysis server. Finally my users consume that connection to analyze content using Microsoft Excel.

When I create a document library to create my BISM model, I couldn’t find the required content type. Following were the steps I used.

1. Navigate to PowerPivot for SharePoint 2013 Configuration

2. Select the new site collection to enable BISM and click run to enable necessary services.

3. Create a document library in that site and allow BISM content type

Collab365 “Low-Trust Provider Hosted Apps for On-Premise SharePoint 2013”

Collab365 Global Conference is set to be this years BIGGEST and most wide ranging event yet. With over 120 speakers.

There are sessions ranging from SharePoint, Office365, Windows Azure and  business implementation of these technologies.

Best part of this conference is that it is Free and it is Online :)

 

 

 

I’ll be covering a session on Low-trust provider hosted apps for on-premise SharePoint 2013.

In this session I will show how to configure On-Premise SharePoint 2013 environment, to consume Low-Trust Provider-Hosted apps created as Azure Web Site/Cloud Service.This allows us to decouple applications from SharePoint, and reap all the benefits of Platform as a Service (PaaS) provided by Windows Azure Websites.

You will learn

• Provider-Hosted app authentication/authorization models
• Use Office 365 tenant to make ACS as our trust provider
• How to create sample low-trust provider hosted app for on-premise environment
• Use azure web site/cloud service to host the app (MVC web application)

SharePoint 2013 Resolving the error - Microsoft.Office.Server.UserProfiles.UserProfileApplicationNotAvailableException: UserProfileApplicationNotAvailableException_Logging :: UserProfileApplicationProxy.ApplicationProperties ProfilePropertyCache does not have <GUID>

Recently I received above error when accessing user profile service’s REST services to access profile information. following are the steps I used to overcome the error

Step 1

Go to IIS Manager and select Authentication

Step 2

Select Anonymous authentication

Step 3

Select Edit

Step 4

Select Specific User and provide “IUSR” without any password