Category Archives: SharePoint 2013

SharePoint 2013 - Cool Writeups dot com and Certified Solutions Australia dot com by Derek Halstead

SharePoint 2013 – Create A New Site Collection Using a Custom Template

You’ve just created a new site template and you’re now wondering if you could use the custom site template to create a new site collection using PowerShell.

Follow these steps and you’ll be sweet.

Log into the SharePoint Application server hosting the Central Admin website with the Farm account.

Add new Content DB via Central Admin and give the Setup account used to run SharePoint setup DBOwner rights to the database in SQL Server Management Studio > Security > Logins.

Open PowerShell in Administrator mode

$mainurl = ‘http://coolwriteupsnewsite.root.internal/’

New-SPSite $mainurl –HostHeaderWebApplication ‘http://coolwriteups.root.internal/’ -OwnerAlias ‘coolwriteups\svcSetup’ -Name ‘EPP Prototype’ –Description ‘EPP Prototype Site’ –language 1033 –ContentDatabase WSS_Content_EPPPrototype

Turn on the Publishing, PerformancePoint and PowerPivot features under Site Collection features and Site Features.

Add-SPUserSolution -LiteralPath ‘D:\_Local\Software\EPPPrototype.wsp’ -Site $mainurl   $ErrorActionPreference = “silentlycontinue”

do{Write-Host “.” -NoNewline -ForeGroundColor White;Start-Sleep -Seconds 5;try{    $testsolution = Get-SPUserSolution -Identity EPPPrototype.wsp -Site $mainurl}catch{}}while(!$testsolution);

$ErrorActionPreference = “stop”
Install-SPUserSolution -Identity EPPPrototype.wsp -Site $mainurl

Get the GUID of the custom template

$site= new-Object Microsoft.SharePoint.SPSite($mainurl)
$loc= [System.Int32]::Parse(1033)
$templates= $site.GetWebTemplates($loc)
foreach ($child in $templates){write-host $child.Name “” $child.Title}

You should see something like this

{2AE95BB2-A62A-40FF-A852-8560258A08B1}#EPPPrototype EPPPrototype

Apply the template to the newly created site collection

$web = GET-SPWeb $mainurl


That’s all it takes to create a new site collection using a custom template.


  • Has own database
  • Easy to set up with script – 40 mins
  • Own security
  • Easy to migrate the content database to a new farm
  • Performance is good on the server


  • Some links will break but should be able to re-add them.

If you need assistance feel free to post your queries in the comments section below. Thanks for reading.

Credit to one of the authors found here.

SharePoint 2013 - Cool Writeups dot com and Certified Solutions Australia dot com by Derek Halstead

How To Kerberos – Configure Constrained Delegation for Excel Services and PowerPivot in SharePoint 2013

I have a SharePoint farm consisting of two web front end servers, two application servers and one SQL server 2012 instance. On both application servers I have Excel Services and PowerPivot services running.

In order to successfully configure constrained kerberos delegation from the SharePoint farm to an external SQL data source you must configure constrained delegation on the application servers too.

Following are Microsoft best practices that I will keep in mind when configuring this:

Best Practices

  • As a best practice you should run the C2WTS using a dedicated service account and not as Local System (the default configuration). Refer this link.
  • Each server running excel services must be trusted to delegate credentials to each back-end service excel will authenticate with. Refer this link.
  • The excel services service account must also be configured to allow delegation to the same back-end services. Refer this link.
  • As a security best practice, Microsoft always recommends constrained delegation over unconstrained delegation. Unconstrained delegation is a major security risk because it allows the service identity to impersonate another user on any downstream computer, service, or application (as opposed to just those services explicitly defined via constrained delegation). Refer this link.
  1. To connect to an external SQL data source via kerberos first set a SPN on the SQL Server. Refer this link.SetSPN
  2. Create a dedicated account for the Claims to Windows Token service which converts claims tickets to windows tokens for the external SQL Server to authenticate. Refer this link.
  3. Create a dedicated account for Excel Services and configure the service for SharePoint. Refer this link.
  4. Create a dedicated account for PowerPivot and configure. PowerPivot does not require the C2WTS service. Refer this link.

Example configuration

Lets assume we need to connect to an external SQL server as in the image below.


My SharePoint environment consists of the following:


  • WEB001
  • WEB002
  • APP001 – running Excel, PowerPivot services
  • APP002 – running Excel, PowerPivot services
  • SQL001
  • SQL004 (external data source to connect to)

Service Accounts

  • coolwriteups\svcC2WTS – C2WTS service running only on APP servers
  • coolwriteups\svcExcel
  • coolwriteups\svcPwrPivot
  • coolwriteups\svcSQL001
  • coolwriteups\svcSQL004 (service account for SQL service on external data source)

Steps to configure

  1. Set an SPN (What is an SPN?) for the SQL service on the external data source using the service account running the service.For example: SetSPN -S MSSQLSvc/ coolwriteups\svcSQL004 provided 1433 is the port number in use. If you are connecting to an instance on the SQL server other than the default then use the port number of that instance instead.
  2. For the Claims to Windows Token Service svcC2WTS, right click the service account to open the properties window and click the delegation tab. Check the “Trust this user for delegation to specified services only” and “Use any authentication protocol” and add the delegation MSSQLSvc/ using the coolwriteups\svcSQL004 account.
  3. Repeat the step above for the svcExcel and svcPwrPivot accounts.
  4. For both the applications servers, right click the name of the servers in Active Directory and click the Delegation tab. By default, you should see “Trust this computer for delegation to any Service (kerberos only)” enabled. Change this to “Trust this computer for delegation to specified services onlyandUse any authentication protocol” and then add the delegation MSSQLSvc/ using the coolwriteups\svcSQL004 account.


  1. Open SQL Profiler and connect to the external SQL data source. Start the profiler and then refresh your PowerPivot or Excel Services dashboard. You should see the user account of the user who refreshed the dashboard in the trace.
  2. Also, open Event Viewer on the external SQL data source and click the Security tab on the left. You should see the user’s account under “New Logon“, Kerberos under “Detailed Authentication Information” and the PowerPivot account under “Transited Services“.
  3. If you see “ANONYMOUS LOGON” and NTLM as the Authentication Package then that means there is a problem and kerberos delegation hasn’t worked.

If you have any queries, post them in the comments section below and let the author or the community assist you.

Helpful links


SharePoint 2013 - Cool Writeups dot com and Certified Solutions Australia dot com by Derek Halstead

Analysis Services TABULAR SQL Server Data Tools Import Power Pivot Project

Analysis Services TABULAR SQL Server Data Tools – How to Import Power Pivot Project

Step by step

Open SSDT as an Administrator on your DEV machine.



Create a new Project to Import from Power Pivot and give your project a name.



Connection to ‘Analysis Services – Tabular’ on your server should always be YourSQLInstance\TABULAR



Copy your Power Pivot files to a folder on the Analysis Server such as D:\PowerPivotFiles and share it like so – \\YourServer\PowerPivotFiles



Give the service account that runs Analysis on the SharePoint/Analysis server READ rights to the folder that hosts the Power Pivot files.





Select your file to import into your Visual Studio project.

Troubleshooting – Use Process Monitor to debug access to the Power Pivot shared folder when experiencing issues.

SharePoint 2013 Get Service Applications And Service Accounts

Get-SPServiceApplication | Select Name, @{Name=”SPAppPoolName”; Expression={$_.ApplicationPool.Name}}, @{Name=”IISAppPoolName”; Expression={$_.ApplicationPool.Id}}, @{Name=”ProcessAccountName”; Expression={$_.ApplicationPool.ProcessAccountName}}

Get-SPServiceApplication | Select Name, @{Name=”SPAppPoolName”; Expression={$_.ApplicationPool.Name}}, @{Name=”ProcessAccountName”; Expression={$_.ApplicationPool.ProcessAccountName}}

SharePoint 2013 - Cool Writeups dot com and Certified Solutions Australia dot com by Derek Halstead

Call To Excel Services Returned An Error

So you’re configuring PowerPivot for SharePoint 2013 and you’re testing your Excel spreadsheet PowerPivot, you come across the error:

Exception calling “Open” with “0” argument(s): “Call to Excel Services returned an error.”

This could be because when you have a fresh install of SharePoint the size of spreadsheets in your Excel Services Service Application is set to a default of 10MB. Change this to a size that is greater than the size of the spreadsheet you are testing.

Solution 1

SharePoint Central Administration, and go to Application Management > Manage Service Applications > Excel Service Application > Trusted File Locations and click on the appropriate Address. Under the Workbook Properties section you will see a Maximum Workbook Size option. Set this value to the required file size in MB, and click OK to save the setting.

Solution 2

SharePoint Central Administration, and go to Application Management > Manage Web Applications > Web Application > General Settings. In the Web Application General Settings window,  under Maximum Upload size, increase this value to the required file size in MB, and click OK to save the setting.

SharePoint 2013 - Cool Writeups dot com and Certified Solutions Australia dot com by Derek Halstead

The Redirector Service Did Not Provide Required Database Information

So you’re trying to configure PowerPivot for SharePoint 2013 and you see an error in you log files or cmd window along the lines of

Exception calling “Open” with “0” argument(s): “The connection cannot be opened because the redirector service did not provide required database information.”

This could be because you are working/testing with an Excel spreadsheet that has a data connection that is external to your domain and because your server is not open to the internet, you see the error.

SharePoint 2013 - Cool Writeups dot com and Certified Solutions Australia dot com by Derek Halstead

Excel Slicer in SharePoint 2013 Web Part Page

Found this cool little trick on a Technet forum post.

1. In Excel, select the worksheet area you want to display (for me , this was the cells containing/around two PivotCharts and two slicers)

2. In the Ribbon, select the Page Layout tab

3. Click on Print Area – Set Print Area

4. In the Ribbon, select the Formulas tab

5. Beside the Name Manager (which as JJ says is where you can check the defined names within the workbook, to prevent conflicts) , click Define Name

6. Type in a name (like “PrintArea”) – you will see your selected cell range in this dialog

7. Click OK and re-upload the excel spreadsheet to SharePoint overwriting the existing version.

8. In the SharePoint 2013 Web Part properties, under Workbook Display > Named Item enter the name “PrintArea” without quotes.

Alternatively, in the Named Item box, type the name of a named item in the workbook (such as a defined name, the name of a chart, table, PivotTable, or PivotChart) that you want displayed in the web part.