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.

Certified Solutions - SharePoint 2013

How SharePoint 2013 Licensing Works.

Microsoft SharePoint Server 2013 Licensing Microsoft licenses SharePoint Server 2013 under the Server/Client Access License (CAL) licensing model. The Server/CAL licensing model requires both a server license for each copy of the server software a customer installs and uses and CALs for the users (or devices) that access that software.

Use Scenarios

This document also illustrates common deployment scenarios to further explain the licensing requirements of SharePoint Server 2013. Users can license these same deployment scenarios under device-based CALs.

Scenario A:

Intranet Description

Internal users accessing content, information, or applications inside the firewall through a local area network (LAN) or the Internet. No other users have access.


A professional sports team sets up an intranet site that the manager, coach, and players access. It is also used for support staff such as the physiotherapist who is an on-site contractor rather than an employee, and for CAL requirements, an internal user. A news reporter trying to access the SharePoint Server site is denied access.


Server: One SharePoint Server 2013 license per running instance of the software. 

Internal Users: One CAL/user.


Scenario B:

Extranet Description

An organization extends access to otherwise restricted content inside the firewall to a limited number of identifiable external users.


The Elm University publishes research papers that are available to specific educators from other universities (external users). This situation is an intranet plus extranet scenario.


Server: One SharePoint Server 2013 license per running instance of the software. 

Internal Users: One CAL/user. 

External Users: SharePoint CALS are not required; the server license permits external user access.


Scenario C:

Internet Description

Internal users make content, information, and applications publicly available to users via the Internet (for example, on a public-facing company website). A mix of internal and external users anonymously accesses the site, including employees. No SharePoint CALs are required. External user access is permitted under the server license, and SharePoint CAL requirements for internal users are waived for access to content, information, and applications made publicly available via the Internet.

Licensing 

Server: One SharePoint Server 2013 license per running instance of the software. 

Internal Users: CALs are not required. 

External Users: CALs are not required.


Simplifying Licensing

SharePoint Server 2013 simplifies SharePoint access and use. This is because SharePoint Server 2013 collapses internal and external use under a single licensing offering/model. Customers no longer need to look to SharePoint for Internet Sites to license anonymous access. Additionally, SharePoint Server 2013 integrates functionality that was previously available separately under FAST Search Server for SharePoint.

InfoPath 2013 Add Colour Coding to the Status of a Form

  • Edit your Form Library page.
  • Add a Content Editor web part to the page and move it below the form library.
  • Open the properties for the Content Editor web part and add the path to the StopLight.txt file. Make sure you upload this file to the Site Assets library in the same site where your form lives.
  • Set the Content Editor Appearance property to hidden and click OK to save the changes.
  • Add a new calculated field column to the Form Library and call it Overall Status (you can call it anything you like).
  • In the formula section add the following: StopLight Formula
  • Save the new column and return to the Form library.
  • Make any additional changes to the form library, for example set the Style to Newsletter to give it a neat professional look.

Download the StopLight.txt file here.

Any questions, let us know in the comments section below.

InfoPath 2013 Moving a Form from an Existing Form Library to a New Form Library

  • Create a document library to store the InfoPath form content type.
  • Publish the form as a content type to the site, save it in the new library created in step 1, then promote any new columns as site columns.
  • Create a new form library and add the new content type as the default.
  • Save a form from the old library by ‘downloading a copy’, then upload it to the new form library.
  • Relink this form in the new library.

It should open and function as normal.

SharePoint 2013 Code Packaging Scenarios Explained

What SharePoint 2013 deployment scenario you choose to host your SharePoint 2013 farm dictates how you develop your code and deploy it. SharePoint 2013 offers three deployment options when it comes to code packaging. See SharePoint 2013 Deployment Scenarios Explained for more details here.

Full Trust Farm Solution

This code packaging option is only available in the on-premise deployment scenario and some dedicated Cloud hosting options. Solutions contain code and customizations that are deployed across the entire farm and are deployed specifically to the \BIN directory or the Global Assembly Cache (GAC). A SharePoint Administrator with farm administrator rights deploys the solution to the farm but the solution itself can be added by a Developer with console access.

Sandboxed Solution

These are partial trust and are available in all types of SharePoint 2013 deployment scenarios. These sandboxed solutions are available to all sub sites of a site collection to where they are deployed. You do not have to be a Farm Administrator to deploy a sandboxed solution, however there are restrictions on solution development if you are a Developer.

SharePoint 2013 Apps

This is a new packaging option introduced in SharePoint 2013 and is based on the idea of small widgets or apps very similar to the apps on your smartphone. However, SharePoint apps are different to smartphone apps as the actual execution of SharePoint apps does not take place in SharePoint, instead it takes place in the browser and communicate with SharePoint via OAuth over SharePoint 2013 CSOM APIs.

What code packaging option do you currently use?

SharePoint 2013 Deployment Scenarios Explained

SharePoint 2013 offers four different deployment scenarios namely:

      Office 365

On-premise deployment

This form of deployment scenario involves having your SharePoint 2013 farm servers sitting behind the corporate firewall and is administered by the IT team and the SharePoint Administrator. With this option you have full control of the SharePoint environment including the Development environment and the how you deploy your applications.

Office 365 deployment

In this form of deployment, your SharePoint 2013 farm is hosted in the cloud and managed by Microsoft. You have access to all the development options as in the On-premise scenario except for running server-side code in apps.

Hosted deployment

In this deployment scenario your virtualised SharePoint 2013 farm is hosted in the cloud by third party providers which may or may not be Microsoft. Companies like Amazon EC2, CloudShare and more offer opportunities to host your virtual machines but have varied options when it comes to development. Some hosting companies will offer you your very own dedicated cloud which gives you more options as you do not have to share it with anyone else.

Hybrid deployment

This is a combination of servers hosted On-premise as well as in the Cloud. Some SharePoint 2013 servers are managed by the IT team and SharePoint Administrator on site while some applications are deployed to the Cloud.

Wondering what code packaging options are available in SharePoint 2013? Check out this link.

What are some of the options you have selected for your SharePoint farm? Share your suggestions with the community below.

What is Azure?

Get started with Windows Azure

Azure is Microsoft’s cloud computing platform that provides a powerful way to transform business and applications.

Ability to move really fast.
On premise might take weeks or days to stand up, but azure instantly spins up the resources you need to set up websites and servers.

You can save money with windows azure.
On premise servers, CPU utilization is 10 to 15% as there are times in the day when the servers are not busy or staff are away. Servers are built to run at peak no matter what time of the day it is. You end up having to pay for that peak usage even when it is not being leveraged.
In the cloud world you can dynamically spin up and spin down resources based on only what you need so you only pay for what you use. During off peak times you can cycle down the servers or run them at lower utilization and pocket that money. For most applications that will lead to a tremendous cost saving.
Another great thing about Azure is that it maintains hybrid consistency, this allows you to use the same programming models, the same core tools and the same languages with windows servers, SQL and .NET.
You can build applications that will run on both on premise as well as in the cloud. You can also build applications that can span both on premise and the cloud.

Where does Windows Azure run?

It runs in Microsoft Datacentres that are distributed around the world. Some regions consist of multiple datacentres that are close together. You can choose to run and deploy your applications to any of these regions around the world. The regions include North and South America, Europe, Japan, Australia and more in Asia. You simply point to where you want to deploy and run your application regardless of where you are in the world while maintaining full control over the deployment.
Each region is at least 500 miles away and so there are a cluster of regions around the world. You can then choose to run your application either in one of the regions or both. The benefit of running them in both regions is so that you can maintain disaster isolation during a natural disaster. So if one region goes down you can switch to the other region and continue running your applications. This gives you peace of mind knowing that in the event of a natural disaster you can still continue to run your applications. So you always have choice and flexibility.

Saving money

You only pay for what you use, only for the minutes that you use. There is no upfront cost, there is no need to buy Windows or SQL licenses. You simply log on to the Azure website, sign up and immediately start deploying assets. Billing is done per minute only, so you only pay per minute you use Azure. So if you spin up a new virtual machine and use it for 10 minutes you only pay for 10 minutes. You are not paying by the hour or per server, so that saves you a lot of money. There is no charge for stopping virtual machines, so if you stop your VMs for maintenance or for upgrade, the billing will also stop.

More cool features

Virtual Machines

When it comes to Azure capabilities you can run both Windows and Linux virtual machines and there is no restriction on what software you use with the VMs. You can install any database, any application server, and any framework in any language. You can also run these machines as standalone machines with their own unique IP address on the internet or you could take advantage of the ‘Virtual Private Networking’ feature. What this feature does is it allows you to take multiple VMs and put them together and have an application that consists of multiple machines with some very cool options like the ‘Load Balancing and High Availability’ feature in an Availability Set.
The Availability Set (19:00)

This allows you to deploy your VMs on Azure and run each of those VMs on separate fault domains or isolation units. For example if you have two or three VMs, you can make sure that they are each running on separate physical servers so that in the event of a server failure you wouldn’t lose all your VMs all at once.
To break it down, you simply create two or more virtual machines in an Availability Set and Azure will make sure that they are isolated on separate physical machines with two separate top rack routers available. So in the event of a network failure you can rest assured that at least some of the VMs will be still be running and your applications will remain unaffected.
There is also load balancing support so you can configure a single IP address to the outside world and make sure that the traffic to that IP address is distributed across the virtual machines, across the availability set or across your Azure regions. The advantage of having load balancing is that if one of the server fails the load balancer will automatically take it out of rotation and stop sending traffic to it and the other VMs can continue to handle the load. Azure will then spin up another instance of the virtual machine automatically on a completely separate server and bring it back into the network rotation. This is all done automatically which is built into the features which allows you to build much more available and reliable systems.

Certified Solutions Australia - SharePoint 2013

Internet Explorer 11 Plays Video But No Sound

I’m not sure why but recently when I upgraded my Internet Explorer browser to Version 11.0 I noticed when I opened a video in the browser it played but without sound.

To fix this:

  • Go to Internet Options > Advanced Tab
  • Scroll all the way down and select the Play sounds in webpages option

You could also update this setting by requesting your network administrator to run a Group Policy update.


Have you done anything else to fix this? Let us know below.


Certified Solutions Australia - SharePoint 2013

SharePoint 2013 – Login Prompt on Web Front End Server – What To Look For

There will be times when you have created a new SharePoint 2010 or SharePoint 2013 Web Application with a root Site Collection and have tried to access the Site Collection in IE on the Web Front End Server but keep getting prompted for a login name and password. What do you do?

Check the following:

  • Add the site to the list of Trusted Sites under Internet Options > Security Tab > Trusted Sites
  • While the Trusted Sites tab is selected, click the Custom Level button at the bottom of the window, in the window that opens, scroll all the way down to the bottom and select the Automatic logon with current user name and password
  • If you are using Kerberos Authentication in your Farm setup, make sure the Application Pool account has been configured for the site. For example: SetSPN -S http/ domain\svc-apppool
  • You could also diable loopback check on the WFE but I don’t usually worry about this as long as the above three steps are done right.
  • Can you think of anything else? Let us know below.


Certified Solutions Australia - SharePoint 2013

How To Create a SharePoint 2013 Project With Visual Studio 2012

Ok, so I was trying to create a new SharePoint 2013 Visual Web Part project on my SharePoint 2013 Application Server (I have a 3 tier SharePoint 2013 Farm) using Visual Studio 2012 Professional and I kept getting the error message below:


Cannot connect to the targeted site. This error can occur if the specified site is not hosted on the local system. SharePoint solutions work only with locally-installed versions of SharePoint Foundation or SharePoint Server. Remote development is supported only for apps for SharePoint 2013

You must understand that you cannot install Visual Studio 2012 on your Windows 7 or Windows 8 user PC and expect to create the project by connecting to a SharePoint 2013 site hosted on a different server. I know there are posts out there on the internet that say it is possible if you install the Office Tools bundle. I tried it and it did not work!

You can only create a new project if you have an instance on SharePoint 2013 running on the PC.

What you need

  • Visual Studio 2012 Professional
  • Office Tools For Visual Studio 2012 – OfficeToolsForVS2012RTW.exe –
  • Visual Studio Tools For Applications – vsta_setup.exe –

Once you’ve installed the above required software on your Application Server, make sure you reboot it!

Setting your Web Application up to work with Visual Studio 2012

I have a web application called on Port 9000 as it states in the URL. I’m assuming you have a DNS entry for this Web Application but the IP address will point to the IP Address of the WFE server.

Open the hosts file on the Application server at C:\Windows\System32\Drivers\etc and edit it in Notepad by adding an entry for the Web Application pointing to the IP Address of the Application Server. Do not point it to the IP address of the WFE.


Next, open Visual Studio 2012 on the Application Server as an Administrator and do as suggested in the screenshots below:

2014-01-31 12_53_30-mRemoteNG - confCons.xml

Select Yes

2014-01-31 12_57_01-mRemoteNG - confCons.xml

Select New Project

2014-01-31 12_59_30-mRemoteNG - confCons.xml

Pick the SharePoint 2013 project template

2014-01-31 12_59_45-mRemoteNG - confCons.xml

Specify the site and security level as


Validate it and then create the project


You may see the following error message when you try to create the project:

2014-01-31 16_21_27-sharepoint connection error (3).png - Windows Photo Viewer

Cannot connect to SharePoint site:″. Make sure that the site url is valid and that the current user has the necessary permissions to access the site

To get around this problem, log into the SharePoint 2013 SQL Server in the Farm and create a new Login under Security for the account that is trying to create the project.

Open it’s account properties and under User Mapping, check the SharePoint_Config database and under Database Role Membership check the db_datareader, public, SPDataAccess and SPReadOnly options.

It may also help to gave the account DBOwner rights on the Web Application Content Database.

If you need assistance with this feel free to leave a comment below and I will do my best to get back to you.



Certified Solutions Australia - SharePoint 2013

SharePoint 2013 Information Management Policy with Workflow

SharePoint 2013 Information Management Policy with Workflow

If you’ve ever wanted to set an Information Management Policy for a SharePoint 2013 document library and weren’t sure how to go about doing this, well this post if for you!

SharePoint Site Settings

Top Level Site Settings > Site Collection Administration > Site Collection Features and activate the Library and Folder Based Retention feature.

CoolWriteups.Com - SharePoint 2013 Information Management Policy

Central Administration Settings for Information Management Policy and Expiration Policy Jobs.

On the Application Server or the server running Central Administration, open Monitoring > Job Definitions > Information Management Policy (for SharePoint site)

2014-01-17 10_15_43-mRemoteNG - confCons.xml

Change the settings as per the screenshot below:

CoolWriteups.Com - SharePoint 2013 Information Management Policy

Then change the settings for the Expiration Policy job for the SharePoint site. Go to Central Administration > Monitoring > Job Definitions and find and click the Expiration Policy job for the SharePoint site.

CoolWriteups.Com - SharePoint 2013 Information Management Policy

Change the settings as per the screenshot below:

CoolWriteups.Com - SharePoint 2013 Information Management Policy

Note: Make sure the Information Management Policy job runs first before the Expiration Policy job.

Information Management Policy Settings for a Document Library in SharePoint 2013.

Document Library > Library Settings > Permissions and Management select Information Management Policy Settings as in the screenshot below.

CoolWriteups.Com - SharePoint 2013 Information Management Policy

Then click Change Source or Configure Library Schedule as in the screenshot below:

CoolWriteups.Com - SharePoint 2013 Information Management Policy

The document library has a custom column called ‘Date To’ which is a Date/Time field and any document that has a Date To value that is greater than today will be deleted with the help of this Information Management Policy and a Workflow.

In the screenshot below, when the policy is set to run when the current date is greater than the Date To value by 1 day.This will kick off a Workflow that will delete the document permanently and send an email to the owner of the document.

CoolWriteups.Com - SharePoint 2013 Information Management Policy

Today’s date is 17 January 2014 so as per the policy above any document with a Date To value before the 17th will be deleted and an email is sent to the owner. Workflow column has a Completed status which means the job is complete.


CoolWriteups.Com - SharePoint 2013 Information Management Policy

** Update

I’ve found that a number of users are unable to open the Information Management Policy link under the List or Library settings. They get an Access Denied error when they click the Information Management Policy link which points to policycts.aspx.

To correct this problem do the following:

In your top level site permissions, add the Designers group and ensure it has Design permissions. Add the user to this Designers group.

In your sub site where you are trying to set the Information Management Policy for the document library or list make sure the user has Full Control to the sub site.

** End Update

If you’re having trouble setting this up leave a comment below and we will do our best to assist.


Certified Solutions Australia - SharePoint 2013

Upgrade SharePoint 2010 My Sites to SharePoint 2013 My Sites

Upgrading My Sites from SharePoint 2010 to SharePoint 2013 can be a challenge and some of the information found on the Microsoft site can be a little misleading.

I have put together a few steps that worked for me. The process includes upgrading the Managed Metadata and User Profile Service Applications along with the My Sites content databases.

Disclaimer: This posting is provided “AS IS” with no warranties. Please carry out the tasks below in your Development environment first. I suggest using Hyper V to take a snapshot of your farm first including the SQL Server before carrying out the upgrade.

Microsoft links to refer to

Upgrade My Sites:
Upgrade Service Apps:

The upgrade process is as follows

Step 1: Copy and restore the following databases from SharePoint 2010 SQL Server to your SharePoint 2013 SQL Server.

– ProfileDB
– SocialDB
– SyncDB
– MetadataDB
– WSS_Content_MySites

The WSS_Content_MySites is the database that holds all the My Sites in my SharePoint 2010 environment. The project I was working on had their My Sites and SharePoint Sites all in the one database so I had to use the Move-SPSite powershell command to move the sites to a dedicated WSS_Content_MySites database for My sites.

Move-SPSite -DestinationDatabase WSS_Content_MySites

Next if you have a setup account you use to run your queries in your farm, make sure this account has DBOwner rights on the database. This account can be the FARM account or a Setup account like the one you used to run the SharePoint installation.

Step 2: Upgrade the Managed Metadata Service Application, but first delete the existing Managed Metadata Service Application and User Profile Service Application in your SharePoint 2013 environment (if it exists), but ensure that both the Managed Metadata Service and the User Profile Service (not synchronization service) are started on the Application Server.

I have a Service Application Pool that I use for most of my Service Applications. If you want to create a separate service application pools for the Managed Metadata Service Application, then please make changes to the commands below to suit your needs.

On your Application Server, log in with your Setup account, open a Powershell command window in Administrator mode and run the following commands:

Get-SPServiceApplicationPool |ft name

$applicationPool = Get-SPServiceApplicationPool -Identity 'Service Application Pool Account'

$mms = New-SPMetadataServiceApplication -Name 'Managed Metadata Service Application' -ApplicationPool $applicationPool -DatabaseName 'MetadataDB'

New-SPMetadataServiceApplicationProxy -Name ‘Managed Metadata Service Application’ -ServiceApplication $mms –DefaultProxyGroup

IISReset /noforce

The above Powershell commands will create a new Managed Metadata Service and upgrade the MetadataDB database.

Now, delete this new Managed Metadata Service Application by selecting it under Service Applicatons in Central Administration and clicking the Delete button at the top of the page. DO NOT check the box to delete the upgraded database. We need this so leave the database as is, only delete the service application.

After you have deleted the service application, re-create it, but this time via Central Administration and in the Database box, give it a database name like MetadataDBRemoveMe for example.

Once the service application is created, we need to change the database and make sure that it uses the upgraded database. So, run the powershell command below to do this:

Set-SPMetadataServiceApplication -Identity "Managed Metadata Service Application" -DatabaseName MetadataDB

Then run IISReset /NoForce

Possible error: “The managed Metadata Service or Connection is currently not available. The Application Pool or Managed Metadata Web Service may not have been started. Please Contact your Administrator.”

Possible solution: Give the Service Application Pool Account DBOwner rights to the upgraded Metadata database in SQL Server. Also give the service application pool full control permissions to the Service Application in Central Administration.

Make sure there are no errors when you click the Managed Metadata Service Application under Service Applications in Central Administration. It is important that there are no errors, do not proceed to the next step if there are errors.

Step 3: Create a new My Site Web Application in Classic mode.

New-SPWebApplication -Name "MySite WebApp" -ApplicationPool "MySiteAppPool" -AuthenticationMethod "Kerberos" -ApplicationPoolAccount (Get-SPManagedAccount "Contoso\svc-sp2013DevAppPool") -Port 80 -URL "" -HostHeader "" -DatabaseName WSS_Content_MySitesRemoveMe

Do not create the My Site Host site collection after creating the Web Application above.

Create a new My Sites User Policy (See Technet documentation regarding creation My Sites for SharePoint 2013 for details)

Don’t try to configure Self-Service site creation as there is no root site collection.

Step 4: Create a new User Profile Service Application.

Run the following Powershell scripts in Administrator mode as you did above:

Get-SPServiceApplicationPool |ft name

$applicationPool = Get-SPServiceApplicationPool -Identity 'Service Application Pool Account'

$upa = New-SPProfileServiceApplication -Name 'User Profile Service Application' -ApplicationPool $applicationPool -ProfileDBName 'ProfileDB' -SocialDBName 'SocialDB' -ProfileSyncDBName 'SyncDB'

New-SPProfileServiceApplicationProxy -Name ‘User Profile Service Application’ -ServiceApplication $upa -DefaultProxyGroup

IISReset /noforce

Next, delete this newly created User Profile Service Application, but do not check the box to delete the database too. We need these upgraded databases so leave them intact.

Create a new User Profile Service Application but for the database names, enter the name for the upgraded databases instead.

Once the new User Profile Service Application is created, select it under Central Administration > Service Applications > click the Administrators tab and add the FARM account and Service Application Pool account.

Also add the FARM account to the local Administrators group on the Application server that will run the User Profile Synchronization Service.

Start the Synchronization service on the Application Server, it will ask you to enter the FARM account password, enter it and wait. This will take some time, it needs to start the ForeFront Identity Manager Service and ForeFront Identity Synchronization Service. So be patient, minimum 10 minutes.

Step 5: Once the Synchronisation Service starts successfully, you need to upgrade the My Sites web application by testing and mounting the restored database from SharePoint 2010. You then need to convert it to Claims authentication.

Make sure you have copied the My Sites Content database across to your new SQL Server and restored it. Ensure that the Setup account has DBOwner rights on the database.

Remove the default database from the My Sites Web Application via Central Administration first.

Then run the following Powershell command to test the restored database with the My Sites Web Application.

Test-SPContentDatabase -name WSS_Content_MySites -webapplication

Make sure there are no errors and that they do not block the upgrade of the database.

Then run a Mount-SPContent Database like below:

Mount-SPContentDatabase "WSS_Content_MySites" -WebApplication “”

The upgrade should complete successfully.

Next convert the My Sites Web Application to Claims by running the following command:

Convert-SPWebApplication -Identity "" -To Claims -RetainPermissions -Force

You now need to create the My Sites host for the My Sites Web Application. So, in Central Administration, create a new Site Collection for the My Sites Web Application and select the My Sites template under Enterprise. Also, make sure that you have selected the root of the web application – ‘/’.

Step 6:

Configure Staff profile photo by editing the Manage User Properties, select Picture and then edit, add a new mapping and select thumbnail photo

Under Central Administration > Service Applications > User Profile Service Application, click ‘Setup My Sites’ and add the My Site Host Location.

Start full profile sync and make sure it completes with no errors.

Once the user synchronization has finished, open the SharePoint 2013 Management Shell as the Farm account svc-SP2013DevFarm and run the following command

$mySitesUrl=”” (Enter)
$mySitesHost=Get-SPSite –Identity $mySitesUrl (Enter)
Update-SPProfilePhotoStore –MySiteHostLocation $mySitesHost –CreateThumbnailsForImportedPhotos $true (Enter)

This completes the upgrade of My Sites from SharePoint 2010 to SharePoint 2013. Go to your SharePoint 2013 site and click the Newsfeed link at the top of the page. This will start the process of provisioning a new My Site for your account. Once done, you should receive an email from your SharePoint Farm notifying you of the set up. You will then find all your old files on your SkyDrive page.

If you have any questions or need help with your upgrade, post a message below and I will be happy to assist.



Certified Solutions Australia - SharePoint 2013

SharePoint 2013 – 401 Unauthorised Error When Accessing My Sites Site Collection


In SharePoint 2013, once you’ve created a dedicated Web Application to host My Sites and a Site Collection with the My Sites Host template, you open a browser and enter the address of the new Site Collection which in this case is http://mysitesdev2013.domain.internal only to see a 401 Unauthorised error.

I set my My Sites web application with a host header URL, so I did make a DNS entry similar to http://mysitesdev2013.domain.internal.

Log files will display the following warning which is a good indicator of what the problem is:

8059 Warning Alternate access mappings have not been configured. Users or services are accessing the site http://servername with the URL http://mysitesdev2013.domain.internal. This may cause incorrect links to be stored or returned to users. If this is expected, add the URL http://mysitesdev2013.domain.internal as an AAM response URL. For more information, see:″/>


I solved this by creating an Alternate Access Mapping (AAM) for the dedicated My Sites Web Application in the Custom Zone. See image below:

Certified Solutions - SharePoint 2013 - My Sites - Alternate Access Mapping (AAM) For 401 Unauthorised Error