Author Archives: Derek Halstead

Don’t Worry Be Happy

Matthew 6:25-34

25 “Therefore I tell you, do not worry about your life, what you will eat or drink; or about your body, what you will wear. Is not life more than food, and the body more than clothes? 26 Look at the birds of the air; they do not sow or reap or store away in barns, and yet your heavenly Father feeds them. Are you not much more valuable than they? 27 Can any one of you by worrying add a single hour to your life28 “And why do you worry about clothes? See how the flowers of the field grow. They do not labor or spin. 29 Yet I tell you that not even Solomon in all his splendor was dressed like one of these. 30 If that is how God clothes the grass of the field, which is here today and tomorrow is thrown into the fire, will he not much more clothe you—you of little faith? 31 So do not worry, saying, ‘What shall we eat?’ or ‘What shall we drink?’ or ‘What shall we wear?’ 32 For the pagans run after all these things, and your heavenly Father knows that you need them. 33 But seek first his kingdom and his righteousness, and all these things will be given to you as well. 34 Therefore do not worry about tomorrow, for tomorrow will worry about itself. Each day has enough trouble of its own.

Proverbs 3:5-6

5 Trust in the LORD with all your heart and lean not on your own understanding; 6 in all your ways submit to him, and he will make your paths straight.

Philippians 4:6-7

6 Do not be anxious about anything, but in every situation, by prayer and petition, with thanksgiving, present your requests to God. 7 And the peace of God, which transcends all understanding, will guard your hearts and your minds in Christ Jesus.

Luke 12:24-34

24 Consider the ravens: They do not sow or reap, they have no storeroom or barn; yet God feeds them. And how much more valuable you are than birds! 25 Who of you by worrying can add a single hour to your life? 26 Since you cannot do this very little thing, why do you worry about the rest? 27 “Consider how the wild flowers grow. They do not labor or spin. Yet I tell you, not even Solomon in all his splendor was dressed like one of these. 28 If that is how God clothes the grass of the field, which is here today, and tomorrow is thrown into the fire, how much more will he clothe you—you of little faith! 29 And do not set your heart on what you will eat or drink; do not worry about it. 30 For the pagan world runs after all such things, and your Father knows that you need them. 31 But seek his kingdom, and these things will be given to you as well. 32 “Do not be afraid, little flock, for your Father has been pleased to give you the kingdom. 33 Sell your possessions and give to the poor. Provide purses for yourselves that will not wear out, a treasure in heaven that will never fail, where no thief comes near and no moth destroys. 34 For where your treasure is, there your heart will be also.

Matthew 11:28-30

28 “Come to me, all you who are weary and burdened, and I will give you rest. 29 Take my yoke upon you and learn from me, for I am gentle and humble in heart, and you will find rest for your souls. 30 For my yoke is easy and my burden is light.”

John 14:27

27 Peace I leave with you; my peace I give you. I do not give to you as the world gives. Do not let your hearts be troubled and do not be afraid.

Colossians 3:15

15 Let the peace of Christ rule in your hearts, since as members of one body you were called to peace. And be thankful.

2 Thessalonians 3:16

Final Greetings
16 Now may the Lord of peace himself give you peace at all times and in every way. The Lord be with all of you.

Psalm 55:22

22 Cast your cares on the LORD and he will sustain you; he will never let the righteous be shaken.

Proverbs 12:25

25 Anxiety weighs down the heart, but a kind word cheers it up.

1 Peter 5:6-8

6 Humble yourselves, therefore, under God’s mighty hand, that he may lift you up in due time. 7 Cast all your anxiety on him because he cares for you. 8 Be alert and of sober mind. Your enemy the devil prowls around like a roaring lion looking for someone to devour.

Psalm 23:4

4 Even though I walk through the darkest valley,I will fear no evil, for you are with me; your rod and your staff, they comfort me.

Hebrews 13:5-6

5 Keep your lives free from the love of money and be content with what you have, because God has said, “Never will I leave you; never will I forsake you.” 6 So we say with confidence, “The Lord is my helper; I will not be afraid. What can mere mortals do to me?”

Psalm 56:3

3 When I am afraid, I put my trust in you.

Romans 8:38

38 For I am convinced that neither death nor life, neither angels nor demons,[a] neither the present nor the future, nor any powers, 39 neither height nor depth, nor anything else in all creation, will be able to separate us from the love of God that is in Christ Jesus our Lord.

Elegant Favors For Weddings Secrets

There is only one tiny, small decision left to make, that of what your party favors ideas ought to be. You’ve been to your share of weddings through the years and took home the little baubles that appeared just an afterthought. You don’t wish your wedding to be like this. No, you need it to exude an air of luxury in each and every decision, down to the smallest one- favors.

Some things to ponder while making this decision… Staying with your wedding colors while choosing wedding favors will certainly put you off to a great start. Your theme would be complete. If you wish for a bit of an alternation in color, black, white and silver are very much keeping consistent with a stylish pattern.

Some other thoughts to keep in mind. You’ll be able to still stick with your theme yet throw somewhat of a twist in. Maybe add integrate a damask print, which is very sophisticated. Pin stripe and zebra print can also be good options.

Elegance is typically quite simplistic, moderate. It’s not necessary to try hard, items of class stand with assurance independently. Formal items don’t need to overly shimmer, they already shine.

Let’s look at a few ideas that you might pick for your most unique wedding favors. Small gift boxes tied up with satin ribbons are lovely to use gourmet chocolates or other treats in. Coasters certainly are a nice choice. You can also go with small picture albums or oblong picture frames. Of all of the shapes frames can be found in, oblong seems one of the most special, don’t you agree? Curio boxes are a wonderful choice, together with anything pewter or crystal. Champagne glasses, boxed in sets of two, would be good, too. Gift boxes housing capuccino sets could be divine.

Incorporating a personal touch is never a bad idea. In fact, customizing favors shows exactly how important it is to you personally that your friends recieve something nice, something you took time to select for them. Have your wedding date and also your newly married names engraved on your favors, or printed into it.

An additional way to leave a special touch would be to add a tiny note attached to the favor. Have it state something simple like ‘thank you for becoming a part of our wedding memories’. Other great choices to say would be to the sound of ‘please accept this tiny keepsake as a symbol of our admiration for you’ or ‘how blessed we are having friends like you’. One more would be ‘Thank you so much for sharing our special day’. Speak from the heart and you will hit the mark each and every time.

Favors are very important, but don’t lose sight of the big picture. What most people are going to most remember is the magnificence of the pair they observed join together in marriage.

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.