Author Archives: Derek Halstead

Road Trippin – Final Day 19 – Homeward Bound – Coffs Harbour to Brisbane

Well what can I say?!?! It’s the last day of my road trip and what an amazing trip this has turned out to be!! Bear in mind this was not planned, I just went with the flow and the more I rode the more I enjoyed this beautiful country. I will most definitely do another trip down to Melbourne and in to Tasmania some day. With the experience I’ve gained along the way I would even consider riding up north to the Territory. What a great experience that would be!

I left for Brisbane with mixed feelings, part of me wanted to stay back and head to Tassie but my head said it was best to head back and take on a new challenge offered to me in my career by a very sincere team and an even more sincere and honest outgoing applications manager. This is an opportunity for me to make a big difference and I just couldn’t say no.

The weather today was pleasant with a very small chance of rain. I didn’t care anymore about the weather as I had ridden my bike through heavy downfall, in the dark, in the early hours and for long hours and so I got on the bike and left Coffs Harbour. On my way out I took a few photos to remind me of my time here.
























From then on it was a ride up to Brisbane via Ballina, Byron Bay, Tween Heads, Gold Coast and then finally back to Brisbane. I was very confident riding at 110 kms/hr on the highway. This is a huge improvement from when I left Brisbane where I didn’t feel very comfortable doing those speeds. This motorcycle is so well balanced, it’s easy to ride!

I did make a couple of stops along the way, one was at New Italy and the other for a quick snack. As usual there were some amazing roads to ride on and just for the memories I thought I’d take a quick photo of my bike setup and gear. I know there are a couple of things to do for the next trip but I’ve got pretty much everything I could possible need.

Once I got home the first thing I did was to take a photo of the number of kilometres I had covered from start to finish and the state of the bike. I had done over 4,840 kilometres over 19 days! The bike is well and truly ready for it’s 8000 kilometre service.

I know the bike is dirty and I’m keen to give it a wash asap. I booked the bike in for a service on the 10th of Feb and will wash it on the weekend so Willow will be as good as new.

Thank you so much for keeping up with the updates. I hope you’ve enjoyed reading my posts as much as I’ve enjoyed writing them.

Take care and stay safe 🙂


Road Trippin – Day 18 – Homeward Bound – Newcastle NSW to Coffs Harbour

Coffs Harbour is one place I’ve heard so much about and have always wanted to visit, and I’m glad I did. Leaving Newcastle the weather was great and it seemed like a fairly good day for a ride.

This was another long ride so I just wanted to get to my destination, but when you’re on the road, there is so much to see and my curiosity tends to get the better of me, along with the thought of not heading back this way again! So I just had to stop and have a look at the National Motorcycle Museum, the Slim Dusty centre and the Australian Reptile Park. No prizes for guessing which one I liked the best 😜

Arriving in Coffs Harbour the weather wasn’t very kind with strong winds making it difficult to keep control of the bike, but the views made up for all the pain earlier in the day! I checked into the Coffs Jetty B&B which was nice but a little different. As I was traveling by myself I would have preferred a hotel to a B&B.

Tomorrow I head back to Brisbane, to a new job and hopefully a great year ahead!!! 🙂

Road Trippin – Day 17 – Homeward Bound – Canberra to Newcastle NSW

Canberra is the capital of the best country on the planet, Australia. It is where the politicians meet to govern and the city is where you find every government department and museum you can think of. It reminds a lot of America’s ‘Washington Mall’.

On my way out of Canberra I took a couple of photos at the Parliament House which was just around the corner, and having recently visited Canberra in October 2017 I didn’t want to do any site seeing.

The morning of the ride the weather was probably the worst I’ve experienced on this trip with heavy rain and strong winds which made visibility poor so much so that I could barely see through the helmet visor and then through the windshield on the motorcycle.

I made it!! Once I got out of the rain the ride improved and I carried on my way to the Best Western Blackbutt Inn just outside of Newcastle. My stay here was comfortable, but it’s not in the city area so if you’re here for a night then it’s probably best to stay where the action is.

On the way to Newcastle NSW I stopped at the Big Sheep and the Australian Reptile Park which are both great stops if you’re with family. The reptile park has crocodiles aplenty and other reptiles native to Australia. Entry was expensive and so was everything else!!

And just like that I was at the Best Western Blackbutt Hotel where I spent the night after a decent chicken and ham Wellington from their $15 menu.

I got my laundry done at a BP service station laundromat about 3kms away and then returned to the hotel for a good night’s sleep.

Tomorrow I head to Coffs Harbour!! Catch ya then…

Road Trippin – Day 16 – Homeward Bound – Melbourne to Canberra

After the Great Ocean Road I returned to Melbourne the following day for three nights to relax and recover from the long trip. I decided to get some Asian food as the hotel I stayed at is in Melbourne’s Chinatown area. I had Michelin star street food from Singapore for $8.80 at Hawker Chan.It was amazing!! I also had ramen at Musashi which was excellent.

I must admit, I had no intentions of leaving Melbourne if it wasn’t for a new job offer in Brisbane. I love Melbourne as the weather suits me well. It is never hot and humid like in Brisbane and there is heaps more to do. The Australian Open tennis is just four days away and it would have been nice to go to the tennis and catch a game or two.

Anyway it’s homeward bound and my plan is to ride from Melbourne to Canberra and then to Newcastle NSW on to Coffs Harbour and finally to Brisbane.

As usual the weather in Melbourne was gloomy but I managed to get a photo at the Melbourne Cricket Ground, well barely.

From then on it was up the highway and on to Canberra. This was my longest ride on this road trip and with frequent stops along the way it took longer than anticipated. The weather too didn’t help with frequent rain and strong winds the closer I got to Canberra. The views along the way made up for my wet shoes and socks, clothes and dirty motorcycle.

Canberra can be an excellent stopover especially if you are looking for some peace and quiet. Unfortunately, it doesn’t have a decent social life, even for the outgoing introvert like me.

I love the Barton area and stayed at the Brassey hotel for the second time in six months.

After a long tiring day on the motorcycle it was time for some rest for myself and my amazing Indian Chief Vintage. Tomorrow we ride to Newcastle NSW. See you then…

Road Trippin – Day 12 – Great Ocean Road – Melbourne to Warrnambool

My day started at 5:30am with a coffee at the Hotel Grand Chancellor in Melbourne and no brekkie. I thought I’d leave early to dodge the early morning traffic of people heading in to work and then stop in Torquay for a break! Bingo!! Worked well!! Hey I think being out on the road for 12 days straight got me thinking like a pro.

Weather forecast suggested a cool mostly cloudy day but as I was heading out of Melbourne the heavens opened! What?? I nervously gasped!! This wasn’t meant to happen! But in a few minutes it had stopped raining before I hit the highway on my way to Geelong and all was calm again. It was cool and I was in command 👍

<<<< rst stop was Torquay which is officially the first stop of the Great Ocean Road. Of course I couldn’t help but get a quick photo or two 🙂

In Torquay I asked myself, why was the road built? Usually roads link towns to increase commerce and economic opportunities and bring people together. Looking at my route for today several such questions came to mind?? Then half way through the ride I came across a sign that answered my question…

After arriving in Torquay for my first stop I stumbled across Bells road not far away. I wondered if this was the famous road that lead to Bells Beach?? This is the beach that hosts the annual Bells classic surfing event and you know what? It was!! It was packed with surfers, all having a go in good surfing conditions!! For a minute there I wished I could surf but then I realised I need to first learn to swim before surfing!!

After soaking in some sun I packed my bike and continued on my journey to the Twelve Apostles visitor centre. Tip: buy your souvenirs at a travel centre and cafe (great pies and salads) called Yatzies in Lavers hill. I didn’t, thinking I’d find souvenirs at the Twelve Apostles centre! I was wrong!! They didn’t have any so I had to settle for souvenirs I found at store in Port Campbell. I figured if I didn’t tell anyone no one would know 😜

I highly recommend the Great Ocean Road, it is absolutely beautiful. I’ve been very lucky to have done this on a motorcycle. The views are simply breathtaking.

What a glorious way to end the ride with perfect weather and picture perfect scenery like this!

Next, on to The Gateway Motor Inn in Warrnambool for the night before I head back to Melbourne the next morning for a couple of nights to recover from the long ride, then back home to Brisbane.

Catch you on the road, stay safe 👍🙂

Road Trippin – Day 11 – Bairnsdale to Melbourne

This unplanned last minute road trip just keeps getting better! My final destination – Melbourne 🙂👍 with one stop in Traralgon and one in Robinhood Victoria.

The weather today was perfect for riding, it was cool with a top of 23 degrees celcius, the complete opposite of the previous day.

Heading out of Bairnsdale at 7:00am was a good idea. It meant no traffic on an open straight road for miles. Perfect riding conditions! On the way out I came across a ‘swim between the flags’ that a farm owner put up on his farm, something you would usually see at a beach! I thought it was cool and I can only imagine the farm owner trying to be clever with his idea of a scarecrow 🙂

Arriving in Melbourne I was greeted with familiar sights and was extremely happy to have made it all the way from Brisbane on my motorcycle. Most travellers will not do this by car but I did it on a motorcycle! I must be mad!! 🙂

Road Trippin – Day 10 – Eden to Bairnsdale

Eden was very relaxing with beautiful views of the coast and rich history. But it’s a new day and its time to get moving! Today I ride to Bairnsdale which is a small town on my way to Melbourne.

I will be staying at the Mitchell River Tavern. I’ve never stayed at a tavern before so thought I’d try it out especially when it has 4 star reviews and a nightly stay price of only $98! The cheapest so far!

And it was excellent!! Great room and you only need to walk down a flight of stairs to get to the restaurant and bar! How good is that?? 🙂

<< e weather today was extremely hot with temperatures across the coast averaging as high as 42 degrees Celsius. Very rare to have just the one day of the week so hot! It felt even hotter on the motorcycle and at one point my iPhone ceased to function due to the extreme heat. Lucky for me I had a spare phone which I used to navigate my way to Bairnsdale from Lakes Entrance.

< span style=”font-size: 1rem”> On my way to Bairnsdale I rode along the famous and legendary Snowy River trail. It is so very beautiful and the river looked good too.< a href=””><< inally made it to the tavern, checked in, rushed to my room with a couple of cans of bourbon and cola to keep cool and relax in much needed air conditioning. Tonight my job is to get some sleep because tomorrow is a big day! Tomorrow I ride to Melbourne!! See you on the road 😀  

Road Trippin – Day 9 – Batemans Bay to Eden

It’s time to hit the road again after a very relaxing stay in Batemans Bay. I wasn’t sure if I should head to Canberra, the nation’s capital city and then on to the Snowy Mountains or carry on the coastal journey. Given that I’ve visited Canberra not too long ago and having done a fair bit of mountain riding I thought I’d stick with the coastal theme and head to Eden NSW.

The ride to Eden took me to several quaint towns namely Bodalla which has a dairy and a cool looking outdated pub which looked like it had been shut down and was out of service to the public.

I also rode through Tilba Tilba and the very famous town of Bega, thanks to the delicious cheese made there. At the Bega restaurant I got stuck into a cheese toastie and coffee and on my way out I helped myself to a box of assorted fudge. I highly recommend visiting the cheese factory at Bega.

Originally founded as a whaling station, today Eden is best known for whale watching with a Killer Whale museum and a beautiful coastline.

Arriving in Eden, I checked into the Halfway Motel which is in a great location, close to the service station and Coles and a two minute walk to the restaurants and bars just up the road. The room was clean with air conditioning and the bed very comfortable. It was a bit on the pricey side though for a motel.

After checking in I decided to head out for a walk down main street and check out the local attractions including the whale museum. Entrance was $10 and you have access to the history of the town and the people who made it famous, including the whales that were killed. Seriously, there is history about a famous killer whale!

The main street of Eden has plenty of locations that detail the history of this tiny town.

Here are some shots of the main street itself.

And now for some selfies of me!! Why?? Just cause 😜

< img src=”” class=”size-full”><<<<<<<<<<<<<<< ke my way into Victoria and to the town of Bairnsdale. The weather forecast for tomorrow is HOT!! But I'm on a road trip and I love it 🙂

Road Trippin – Day 8 – Batemans Bay Walkabout

Welcome to Batemans Bay!!

To be honest I had absolutely no plans of staying in Batemans Bay, until I started to feel a little fluey after the ride from Sydney. I thought it would be best to stay an extra day and recover before heading on my merry way. The owners of The Bridge motel, Dave and his wife Chris were kind enough to accommodate me at such short notice.

The weather here was a little cooler with plenty of beautiful sunshine, just the sort of weather you would want if you were on holiday. There were families everywhere with plenty of food, ice cream and things to do like fishing, window shopping or just relaxing.

I highly recommend heading here for a few days to get away from the hustle and bustle of the city.

Having relaxed and recovered from the flu like symptoms it’s time to head on to Eden NSW. See you on the road 😉

Road Trippin – Day 7 – Sydney to Batemans Bay

Thank you Sydney for an awesome day and awesome weather. Today it’s time to head to Batemans Bay along the sunny and picturesque NSW coast stopping in Woolongong, Scarborough, Royal National Park, Kiama, Nowra, Milton, Ulladulla and finally Batemans Bay.

The weather was meant to be clear but once I got riding it was pretty cloudy and windy and not exactly safe to ride a motorcycle let alone a ride along the coast.

Heading out of Sydney via the Princess highway and turning left on to Farnell avenue brings you to the Royal National Park with smooth windy roads and very little traffic. Its a great ride which goes for a while once you hit the town of Lilyvale. From here on you will see beaches, picturesque towns, weekend markets and families relaxing and enjoying the weather.

Having arrived in Batemans Bay I checked into the Bridge Motel owned and run by Dave (pictured on my motorcycle below) and his wife Chris. They looked after me and  were happy to accommodate for an additional unplanned day. Also laundry was free 👍 I highly recommend staying here if you’re after a clean, no frills place to stay.







Tomorrow I take in the relaxing atmosphere of Batemans Bay. Ciao 👍😊



Road Trippin – Day 6 – Sydney Walkabout


Finally I’m in Sydney and it is beautiful. It’s a huge city and very multicultural with mostly tourists from all over the world this time of the year.

I started the day at the Vulcan hotel with a decent brekkie and then walked to Hyde park to join a group of tourists on a “free” walking tour of Sydney. The guide was knowledgeable, she should be, she lives in Sydney and works as a guide everyday.

Most of you reading this post have most likely visited Sydney or know of Sydney, but did you know that Sydney was originally named “New Albion”, later changed to “Sydney”, after the (then) British Baron Lord Sydney? The name stuck and people just started calling Sydney, Sydney 😊

< img src=”” class=”size-full”>< img src=”” class=”size-full”>After a long tiring day checking out the sights and sounds of Sydney it was time to head back to the hotel to get some much needed rest and prepare for the next day – the ride to Bateman’s Bay! This will be the first coastal ride of this journey which I’m so looking forward to. See you then 🙂

Road Trippin – Day 5 – Tamworth to Sydney

Rise and shine!! 🙂

It’s time to leave the peace and quiet of Tamworth and head to big and bustling Sydney. The weather this morning was perfect for riding and I wanted to make the most of it, so with a hint of excitement I was up early ready to pack my bike and head off.

I decided to have brekkie (Maccas 😬) and check out the Big Golden Guitar on my way out of town. I didn’t realise I was going to spend so much time here, even got myself a fridge magnet – a yellow Tamworth guitar 🙂

Before you know it, it’s 9:30am and I’m still in Tamworth!! Anyway, I’m in no hurry and proceeded towards Sydney at an easy cruisie pace.

Heading out of Tamworth and the views still continue to mesmerise. I couldn’t help but think about these views being lost to population growth and commercialisation.

After a few unwanted stops (seriously I need to make fewer stops) I was in Sydney and checked into the Vulcan hotel with secure parking just around the corner for $20 AUD per night.

Tomorrow I will be taking in the sights and sounds of Sydney, see you then 🤓

Road Trippin – Day 4 – Tamworth Walkabout

Heading in to Tamworth I couldn’t have been more excited. The owners at the retreat in Dorrigo suggested Tamworth because it was “lively with plenty of bars playing live music”. Unfortunately that is not what I experienced. I’m not sure if it was the time of the year or because it was the weekend but it was pretty quiet and I felt let down.

Well I went for a walk around Tamworth anyway, here are some shots.

I’m sure Tamworth comes alive during the country music festival starting late January. I’d love to come back then and experience it first hand. Anywhoo, I did spend new year’s eve at a bar called the Albert which was lively with a live band playing at one end and a DJ playing at the other end.

Happy New Year to everyone reading this post.

Tomorrow I’ll be heading to Sydney for two nights before I head on to Bateman’s Bay. Catch you then 🙂👍

Road Trippin – Day 3 – Bellingen to Tamworth

I checked the weather forecast for day 3 and no matter where I decided to ride to the weather Gods forecast rain. At the Mountain Lookout Retreat, the evening of day 2, the weather changed from warm to strong winds with clouds forming. My room was a single room with a sliding glass door and a sliding insect door. I slept with the sliding glass door open and this wasn’t the best idea due to the loud winds, needless to say, I didn’t sleep very much. I was also anxious about the rain forecast for the morning and worrying about how I was going to make it to Tamworth in the rain.

The morning of day 3, the winds had calmed down and even though there was a hint of rain I could tell I had about an hour or two before the heavens opened. It was only a matter of time.

I had everything packed and ready to go at 8:00am, I said thank you to reception staff and was on my way….in very windy conditions. Riding a bike this big in conditions like this through two state forests isn’t easy. An hour into the ride and it started to rain. I stopped and changed jackets but my Redwing 8112 boots were soaked and so were my deer leather gloves. The speed limit on the road was 100 and here I was averaging 70. I wanted to get to Tamworth safely and just imagine having a fall in the state forests with no one around to help, so it made sense to ride safely. It is always sensible to ride safely!!

Leaving Dorrigo mountain at 8:10am I rode through Ebor, Armidale where I stopped for breakfast, the Cathedral Rock national park, Uralla which is a very cozy town and one that I wish I stayed a bit longer in and finally arriving in Tamworth at 2:00pm. I am a sucker for beautiful views so I stopped at every opportunity to take some amazing shots.

Arriving in Tamworth I was excited, looking forward to the bars and making new friends. It was quiet! At first I thought it was probably because it was a Saturday, maybe it was late in the afternoon. Where were all the country music bars that I was told about?? Anyway I took it easy when I got there and then decided to head out later in the night.

I found a couple of bars at the end of Peel street not far from where I was starting at CH on Peel which is a nice boutique hotel. The bars both had live bands playing but not the country music I was looking forward to, nothing like the Nashville I was thinking!! Instead there was a band playing 80s music, the name of the band – ‘All Access 80s’. Cool name and a very cool band!! For dinner reason thought it just didn’t feel like Tamworth.

It was a fun night but it was getting late and so I decided to head back to the hotel.

And that’s it for day 3!! Tomorrow I will get a feel for Tamworth! Until then…

Road Trippin – Day 2 – Ballina to Dorrigo

Day 2 didn’t get off to the best start as planned. The night before the owner of the Colonial Motel in Ballina suggested I park my Indian Chief Vintage motorcycle in his newly built garage for security reasons. He was right, it was a good idea, but then we got talking about motorcycles and I forgot to mention to him that I’d be leaving at 8am!! This turned out to be a problem because he only opens the reception at 8am, which meant I had to wait till 8am for him to open the garage for me to get my motorcycle!! So, I ended up leaving at 9:30am by which time the weather was starting to warm up, traffic getting busy and I had to ride for five and a half hours to get to my next destination – the Lookout Mountain Retreat.

My ride started at Ballina and then on to Lismore, Casino, Grafton, Nymboida and finally Dorrigo. The ride through the state forests between Casino and Grafton was the highlight with 99 kms of blissful motorcycle riding and not a lot of traffic. If only the weather was a little cooler it would have been perfect. Well, at least it didn’t rain!

I must admit I’m not a big fan of windy roads and definitely not on the Indian Chief Vintage. This motorcycle prefers open highways where you just sit back and cruise. Personally I dislike having to change gears so often on a bike this big!

But when you’ve got views this good, who cares right??

I’m absolutely in love with nature and the outdoors. I believe I get this from my Dad who is an avid fisherman and in his day used to do a bit of hunting too. The freedom to head out and leave the busy career circus behind is great.

Well, that’s about it from me for today. Tomorrow I’m off to Tamworth, the country music capital of Australia where I will be spending three nights and will usher in the new year. Let’s hope it’s going to be a rippa!! 😎

Have a good one, chat soon…<< p>< /p>

Road Trippin – Day 1 – Brisbane to Ballina via Byron Bay

Finally the day has arrived!! Yes, my first ever motorcycle road trip on my Indian Chief Vintage across Australia 🙂 and what a beautiful day it was, the weather could not have been better.

I was pretty excited the night before and hardly slept. I kept waking up to check the time. I made sure I had most of my stuff packed the day before. Stuff like jeans, gloves, jackets, toiletries, sunscreen, money etc.

I left home at 6:30am with the ride starting in Brisbane and ending in Ballina through plenty of beautiful hinterland and highway via Byron Bay. Good reggae music like ‘Maybe Tomorrow’ by UB40 to keep me company on my long ride.

Once I got to Byron Bay, the views from the lighthouse were amazeballs on a hot and sticky day.

After taking in the breathtaking scenery I continued on my merry way, the Indian Chief happy as ever to keep rumbling along, to my last stop for the day – Ballina.

Tomorrow, I head to Bellingen and Dorrigo via Lismore with  more beautiful scenery and weather. See you then 🙂

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}}