Viewing, Restoring and Removing Items from the SharePoint Recycle Bin – The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

I’ve had a script for a while that allows you to view all the items in the Recycle Bin for a Site Collection and prints out to a CSV file. Recently the environment I’ve been running this in has been throwing an error saying;

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator“.

Getting all items out of the recycle bin.

Originally, I used the PNP Powershell command Get-PnPRecycleBinItem and it was only when I did a Google search for this issue, I found that other people were also having this problem. The PnP team have solved this issue now by adding -RowLimit parameter. If you set the RowLimit high enough you can return all items, as internally, it seems to implement a paging mechanism.

I now use the below script to export the result to a CSV file.

Loops through the recycle bin and output a csv string.
Uses PNP Powershell.
-URL:'https://<tenant&gt;<siteCollection>' -Stage:First -Path:.\FirstRecycleBin.csv
-URL:'https://<tenant&gt;<siteCollection>' -Stage:First -Path:.\FirstRecycleBin.csv -RowLimit:200000
# The url to the site containing the Site Requests list
[Parameter(Mandatory)][ValidateSet("First", "Second")][string]$Stage,
Connect-PnPOnline -Url:$URL -UseWebLogin
Write-Host "Getting recycle bin items..."
if ($Stage -eq "First") {
$RecycleStage = Get-PnPRecycleBinItem -FirstStage -RowLimit 150000
else {
$RecycleStage = Get-PnPRecycleBinItem -SecondStage -RowLimit 150000
$Output = @()
$RecycleStage | ForEach-Object {
$Item = $PSItem
$Obj = "" | Select-Object Title, AuthorEmail, AuthorName, DeletedBy, DeletedByEmail, DeletedDate, Directory, ID, ItemState, ItemType, LeafName, Size
$Obj.Title = $Item.Title
$Obj.AuthorEmail = $Item.AuthorEmail
$Obj.AuthorName = $Item.AuthorName
$Obj.DeletedBy = $Item.DeletedByName
$Obj.DeletedByEmail = $Item.DeletedByEmail
$Obj.DeletedDate = $Item.DeletedDate
$Obj.Directory = $Item.DirName
$Obj.ID = $Item.ID
$Obj.ItemState = $Item.ItemState
$Obj.ItemType = $Item.ItemType
$Obj.LeafName = $Item.LeafName
$Obj.Size = $Item.Size
$output += $Obj
$Output | Export-csv $Path -NoTypeInformation
Write-Host "Done"

Once I have the CSV file, I’m able to filter further in excel and save back to CSV to use to either Restore / Delete the items out of the recycle bin.

Restoring Deleted Items using a csv file.

It seemed that now that I can use RowLimit with Get-PnPRecycleBinItem I should be able to call Restore-PnpRecycleBinItem to restore the item. However, this isn’t the case. Even just passing the Identity of one item within the Recycle Bin, you get the same error message.

The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator“.

There is no RowLimit option on the Restore-PnpRecycleBinItem. The code must internally make a call to get all RecycleBin Items first without using RowLimit. Interestingly though, a user could go to a recycle bin, see items, and restore them if they wanted to. By looking through the network traffic, I was able to see that the GUI uses the following API to Restore Items.

POST /_api/site/RecycleBin/RestoreByIds

Passing in the following JSON body.

"ids": [

There can be one or many Ids.

The trouble with using REST API you need an accessToken. Using the Invoke-PnPSPRestMethod it automatically provides the AccessToken in the call.

This is what I do in the below code. Loop through every item in a CSV file to restore, and call “/_api/sites/RecycleBin/RestoreByIds” using Invoke-PnPSPRestMethod.

# The URL of the Sitecollection where the recycle bin is.
# Full Path of CSV file of Get-AllRecycleBin.ps1
function Restore-RecycleBinItem {
$siteUrl = (Get-PnPSite).Url
$apiCall = $siteUrl + "/_api/site/RecycleBin/RestoreByIds"
$body = "{""ids"":[""$Id""]}"
Write-Verbose "Performing API Call to Restore item from RecycleBin..."
try {
Invoke-PnPSPRestMethod -Method Post -Url $apiCall -Content $body | Out-Null
catch {
Write-Error "Unable to Restore ID {$Id}"
$ErrorActionPreference = 'Continue'
$InformationPreference = 'Continue'
Connect-PnPOnline -Url:$SiteUrl -UseWebLogin
@($(Import-Csv -Path:"$Path")).ForEach({
$csv = $PSItem
Write-Information -MessageData:"Restore item $($csv.Title)"
Restore-RecycleBinItem -Id $($csv.ID)

Deleting Deleted Items using a csv file.

I discovered that I also get the error message when using Clear-PnpRecycleBinItem.

Again, I was able to do this in the GUI, and looking at the network traffic there is an API to delete the items.

POST /_api/site/RecycleBin/DeleteByIds

The JSON body is same format as the RestoreByIds, where it passes in one or many Ids.

The code below is almost identical to the Restore-RecycleBinItems.ps1. Passing in a CSV file with the IDs of files to delete permanently.

# The URL of the Sitecollection where the recycle bin is.
# Full Path of CSV file of Get-AllRecycleBin.ps1
function Clear-RecycleBinItem {
$siteUrl = (Get-PnPSite).Url
$apiCall = $siteUrl + "/_api/site/RecycleBin/DeleteByIds"
$body = "{""ids"":[""$Id""]}"
Write-Verbose "Performing API Call to delete item from RecycleBin..."
try {
Invoke-PnPSPRestMethod -Method Post -Url $apiCall -Content $body | Out-Null
catch {
Write-Error "Unable to Delete ID {$Id}"
$ErrorActionPreference = 'Continue'
$InformationPreference = 'Continue'
Connect-PnPOnline -Url:$SiteUrl -UseWebLogin
@($(Import-Csv -Path:"$Path")).ForEach({
$csv = $PSItem
Write-Information -MessageData:"Delete item $($csv.Title)"
Clear-RecycleBinItem -Id $($csv.ID)

AZ CLI putting message on a storage queue – not a valid Base-64 string

Using Az CLI a lot recently, it has made interactions with Azure so much easier using PowerShell.

I had to write a simple PowerShell script that added items to a Storage queue. Once the items were added to a queue an Azure function picked up the items and processed them.

According to the documentation of Azure CLI you need to use az storage message put.

az login
#Get the connection string
$connectionString = az storage account show-connection-string --name "mystorageaccountname" –resource-group "MyResourceGroup" --query connectionString | ForEach-Object { $PSItem -join '' } | ConvertFrom-Json
#Create message
$message = "{""Name"":""Paul"",""LastName"":""Matthews"",""ID"":""d4b2ffc9-4380-46e4-a0bf-8a9ca58734d2""}"
#Add item to the queue
az storage message put --content $message --queue-name "myQueueName" --connection-string $connectionString

This fires successfully and I can see the item on the queue. However, when my Azure Function starts to run, I get the error message:

The input is not a valid Base-64 string as it contains a non-base 64 character, more than two padding characters, or an illegal character among the padding characters.

To solve this problem you just need to convert the string.

az login
#Get the connection string
$connectionString = az storage account show-connection-string --name "mystorageaccountname" –resource-group "MyResourceGroup" --query connectionString | ForEach-Object { $PSItem -join '' } | ConvertFrom-Json
#Create message
$message = "{""Name"":""Paul"",""LastName"":""Matthews"",""ID"":""d4b2ffc9-4380-46e4-a0bf-8a9ca58734d2""}"
#Encode to bytes
$b = [System.Text.Encoding]::UTF8.GetBytes($message)
#Convert to Base64String
$message64Base = [System.Convert]::ToBase64String($b)
#Add item to the queue
az storage message put --content $message64Base --queue-name "myQueueName" --connection-string $connectionString

Amazingly the value you push upto the queue is something like below:


But it appears on the queue correctly.

Unable to download the Exchange Online PowerShell Module – “Deployment and application do not have matching security zones”

If you want to use multi-factor authentication (MFA) to connect to Exchange Online PowerShell, Microsoft states you are required to install the Exchange Online Remote PowerShell Module, and use the Connect-EXOPSSession cmdlet to connect.

To download this, you need to go to the Exchange Admin Center (EAC) for your Exchange Online organization.

  • Head to Microsoft 365 Admin centre for your tenant and sign in.
  • In the left hand navigation, expand out Admin Centers and select Exchange
  • A new tab opens and loads up the Exchange admin center.
  • In the navigation select Hybrid, and then click the second configuration button.

  • This downloads and runs Microsoft.Online.CSE.PSModule.Client.application.

It was at this point I got an error message stated, “Application cannot be started Contact the application vendor.”

From clicking on the Details under summary email it stated: “Deployment and application do not have matching security zones.”

The problem here, is that, it is trying to run a click-once application. This only works in IE/Edge. I was accessing the EAC through chrome. Therefore, log into your tenant again using Edge or IE.

  • Once loaded, you will find Microsoft Exchange Online PowerShell available in your Windows 10 menu. Also, a PowerShell window will open ready for you to connect to Exchange online using PowerShell.

Connect-EXOPSSession -UserPrincipalName <your UPN>

Summary: When downloading the Microsoft Exchange Online PowerShell from the Microsoft Exchange Admin centre, ensure you have logged in using IE or Edge.

Programmatically change the New Menu in SharePoint Online using PowerShell

Back in October 2018 Microsoft changed the way the New menu on a document library worked. You can now edit what is shown in the New menu directly from the library.

I needed to know how to do this in code, and it looked like PNP automatically does this for you. After doing a get-pnpprovisioningtemplate, when you look at the list you will see a XML element called NewDocumentTemplates, which has a JSON formatted string. The property NewDocumentTemplates exists on the view of the list. This made me think that you could have different menu items for different views, but this doesn’t seem to be the case.

{"contentTypeId":"0x0101009348E1CE5767914598D327EAE7EB97D500245281855DF00844BF9BD64ADD983B7D","isContentType":true,"templateId":"0x0101009348E1CE5767914598D327EAE7EB97D500245281855DF00844BF9BD64ADD983B7D","title":"CF New Request","visible":true},
{"templateId":"NewDOC","title":"Word document","visible":true},
{"templateId":"NewXSL","title":"Excel workbook","visible":true},
{"templateId":"NewPPT","title":"PowerPoint presentation","visible":true},
{"templateId":"NewONE","title":"OneNote notebook","visible":true},
{"templateId":"NewXSLForm","title":"Forms for Excel","visible":true},

Now this seems to work, when applying it back to the same list. However, I found that if I set my own content types with visible to false, and then tried on a different list that did have that content type, it didn’t hide it.

So why not?

The reason is because the contentTypeId it is expecting, is the ListContentTypeId, not the actual ContentTypeId. For each list, the ListContentTypeId will be different.

When you add a site content type to a list:

“SharePoint list makes a local copy of the site content type and adds the copy to the content type collection on the list. The new list content type is a child of the site content type. The value of the Id property for the list content type is different from the value of the Id property for its parent site content type, but otherwise the two content types are initially the same.”

I’m sure PNP will pick up on this and update the code at some point.

PowerShell script

I decided to write my own PowerShell script to solve this issue. First, I needed to understand the JSON format. For the built in Microsoft menu items the JSON looks as follow:

  "title": "Word Document",
  "templateId": "NewDOC",
  "visible": true

There are quite a few built in ones, and from using PNP to export out, I was able to find what they were called. (If you find out any more, that I don’t have listed here please let me know.)

  • TemplateId – Name
  • NewFolder – Folder
  • NewDOC – Word Document
  • NewXSL – Excel workbook
  • NewPPT – PowerPoint presentation
  • NewONE – OneNote notebook
  • NewVSDX – Visio drawing
  • NewXSLForm – Forms for Excel
  • Link – Link

Note: If you don’t have a license for Visio, or do not have Forms enabled for your E3 license, then the menu item will not show up anyway.

The JSON format for content types are the following:

  "title": "<Content Type Name>",
  "templateId": "<ListContentTypeID>",
  "visible": <true/false>,
  "contentTypeId": "<ListContentTypeID>",
  "isContentType": <true/false>

Below is my function script. Set-ListNewMenuItems.ps1 With this script you can hide any content types/default menu items by using the titles.

-Url:’; -ListTitle:’Documents’ -ContentTypesToHide:’OneNote notebook’,’PowerPoint’,’Custom CT Name’

You can also hide all default menu items, and let your content types show. It will still show links and folder.

-Url:’; -ListTitle:’Documents’ -HideDefault:$true

If you want to hide link and folder too, you would need to include their names in the ContentTypesToHide.

-Url:’; -ListTitle:’Documents’ -ContentTypesToHide:’Folder’,’Link’ -HideDefault:$true

Set the New Menu on a document library.
Sets the New Menu on a document libary, allow you to hide content types you don't want to show. This will grab all list content types currently assigned to the library.
Default Content Types used by Microsoft 'Folder', 'Word document', 'Excel workbook', 'PowerPoint presentation', 'OneNote notebook' 'Visio drawing', 'Link'
Updates the SharePoint library New Menu, but hiding given Content Types.
-Url:'; -ListTitle:'Documents' -ContentTypesToHide:'OneNote notebook','PowerPoint','Custom CT Name'
Updates the SharePoint library New Menu, but hiding Default Content Types ('Word document', 'Excel workbook', 'PowerPoint presentation', 'OneNote notebook' 'Visio drawing')
-Url:'; -ListTitle:'Documents' -HideDefault:$true
Updates the SharePoint library New Menu, but hiding Default Content Types ('Word document', 'Excel workbook', 'PowerPoint presentation', 'OneNote notebook' 'Visio drawing') and given Content Types
-Url:'; -ListTitle:'Documents' -ContentTypesToHide:'OneNote notebook','Link' -HideDefault:$true
# The url to the site containing the Site Requests list
$HideDefault = $false
function Add-MenuItem() {
$newChildNode = New-Object System.Object
$newChildNode | Add-Member type NoteProperty name title value:$title
$newChildNode | Add-Member type NoteProperty name visible value:$visible
$newChildNode | Add-Member type NoteProperty name templateId value:$templateId
if ($null -ne $contentTypeId) {
$newChildNode | Add-Member type NoteProperty name contentTypeId value:$contentTypeId
$newChildNode | Add-Member type NoteProperty name isContentType value:$true
return $newChildNode
function Get-DefaultMenuItems() {
$DefaultMenuItems = @()
$DefaultMenuItems += Add-MenuItem title:"Folder" templateId:"NewFolder" visible:$true
$DefaultMenuItems += Add-MenuItem title:"Word document" templateId:"NewDOC" visible:$true
$DefaultMenuItems += Add-MenuItem title:"Excel workbook" templateId:"NewXSL" visible:$true
$DefaultMenuItems += Add-MenuItem title:"PowerPoint presentation" templateId:"NewPPT" visible:$true
$DefaultMenuItems += Add-MenuItem title:"OneNote notebook" templateId:"NewONE" visible:$true
$DefaultMenuItems += Add-MenuItem title:"Visio drawing" templateId:"NewVSDX" visible:$true
$DefaultMenuItems += Add-MenuItem title:"Forms for Excel" templateId:"NewXSLForm" visible:$true
$DefaultMenuItems += Add-MenuItem title:"Link" templateId:"Link" visible:$true
return $DefaultMenuItems
function Set-NewMenuOnList() {
Connect-PnPOnline Url:$URL UseWebLogin
Write-Host "Connected to URL:$Url" ForegroundColor Green
$list = Get-PnpList Identity:$ListTitle
Write-Host "Connected to List:$($list.Title)"
$listContentTypes = Get-PnPContentType List $List
$defaultView = Get-PnpView List:$List | Where-Object {$_.DefaultView -eq $true}
$MenuItems = Get-DefaultMenuItems
$listContentTypes | ForEach-Object {
$ct = $PSItem
if($ct.Name -eq "Folder"){
$MenuItems += Add-MenuItem title:$ct.Name visible:$true templateId:$ct.StringId contentTypeId:$ct.StringId
$hideContentType = $ContentTypesToHide;
if($HideDefault -eq $true){
write-host "Hiding default content types"
$hideContentType += 'Word document', 'Excel workbook', 'PowerPoint presentation', 'OneNote notebook','Visio drawing'
}else {
write-host "Including default content types"
$MenuItems | ForEach-Object {
if ($hideContentType -contains ($_.title)) {
$_.visible = $false
write-host "Hiding content type $($_.title)" ForegroundColor Yellow
else {
write-host "Showing $($_.title)" ForegroundColor Green
$defaultView.NewDocumentTemplates = $menuItems | ConvertTo-Json
Write-Host "Updated $($list.Title)"
Set-NewMenuOnList URL:$URL ListTitle:$ListTitle ContentTypesToHide:$ContentTypesToHide HideDefault:$HideDefault

After running my script to remove Document, OneNote notebook, Word Document and Forms For Excel

-Url:’; -ListTitle:’Documents’ -ContentTypesToHide:’Document’,’OneNote notebook’,’Word Document’,’Forms For Excel’ -HideDefault:$false

As you can see from above, it states that it is Showing Visio drawing. However, on my tenant I do not have a license for it. Below shows you how my New menu has been updated.

The code isn’t perfect, and could do with improvements such as ordering. Please feel free to use it.

View Trace Logs with PnP Provisioning Templates using PowerShell

When running the Get-PnPProvisioningTemplate and the Apply-PnPProvisioningTemplate it is sometime necessary to see what is going on while the call is processing.

Just run the following command first before calling your Get/Apply -PNPProvisioningTemplate command.

Set-PnPTraceLog -On -Level:Debug

If you need to turn it off again

Set-PnPTraceLog -Off

PnP Provisioning Templates Adding – Everyone except external users

On a recent project of mine, I’ve been working with the PnP Provisioning engine using PowerShell. It’s really the first time I’ve really worked with it, and I must say I’m impressed at how easy it is to use.

If you have never used it before I recommend you checking out the following articles on MSDN.

Basic installation setup

If you have a Windows 10 device or have installed PowerShellGet, to check if you have the latest version installed in your PowerShell environment, run the following below

Get-Module SharePointPnPPowerShell* -ListAvailable | Select-Object Name, Version | Sort-Object Version -Descending

I already have the latest version at the time of writing this 2.17.1708.1.

You can install or Upgrade the SharePointPNPPowerShell with the following commands.

To Install

#SharePoint Online
Install-Module SharePointPnpPowerShellOnline

#SharePoint 2016
Install-Module SharePointPnPPowerShell2016

#SharePoint 2013
Install-Module SharePointPnPPowerShell2013

To Upgrade

Update-Module SharePointPnPPowerShell*

Everyone and Everyone Except External Users

The quickest way to create a PnP Provisioning template ready to use again, is to create your site within SharePoint using point and click.

I’ve created my site with Members set up to “Everyone except external users” and Visitors set up to “Everyone”.

After you have created your site, you use the following commands to connect and export the template.

Connect-PnpOnline -Url: -Credentials: (Get-Credential)
Get-PnPProvisioningTemplate -Out 'pnpExample.xml'

If you open the XML file, and look in the <pnp:Security> section, you will see that Additional Members has c:0-.f|rolemanager|spo-grid-all-users/{GUID} and Additional Visitors has c:0(.s|true. These represent Everyone except external users and Visitors respectively.

If you are only using this template to create more sites in the same tenant that you exported it from, then you are good. The GUID after ‘spo-grid-all-users’ will always be that GUID in your tenant. However, when you want to use this template in other tenants – for example you have a staging and production environment – then this GUID will not work and the importing of the template will not add Everyone except external users to your members group.

What is the GUID after spo-grid-all-users?

It turns out that the GUID relates to the Authentication Realm ID of your tenant, and luckily PnP have a PowerShell command


How does that help us?

You can pass parameters into the Apply-PnPTemplate. First we need to change the XML inside <pnp:AdditionalMembers>


<pnp:User Name="c:0-.f|rolemanager|spo-grid-all-users/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx" />


<pnp:User Name="c:0-.f|rolemanager|spo-grid-all-users/{parameter:AuthenticationRealm}" />

Save the XML template and now you can run the following commands to apply the template to a new site in a different tenant. (Please note my team site I’m applying this to has already been created)

Connect-PnpOnline -Url: -Credentials: (Get-Credential)
$AuthenticationRealm = Get-PnPAuthenticationRealm
Apply-PnPProvisioningTemplate -Path:'pnpExample.xml' -Parameters:@{"AuthenticationRealm"=$AuthenticationRealm}

After applying the template to another tenant, you will see your “Everyone except external users” inserted correctly.

Using the Windows Credentials Manager with PnP PowerShell

Do you get fed up keep typing in your username and password when you are connection to SharePoint via PowerShell? Or you have to keep changing between multiple tenants and get fed up keep typing in the username and password? Did you know you could use the built-in Windows Credential Manager to help ease your pain?

  • Open your Credential Manager

  • Under the Generic Credentials, click ‘Add a generic credential

  • For each tenant/user account you need, create a Generic Credential.
    • Put a label name to indicate what the permissions are for (e.g DevAdmin, TestAdmin, TestUser etc)
    • Put the username of the account
    • Put the password of the account

  • After you have created your Generic Credential(s), when you try to Connect to SharePoint using PNP, you can pass your Label to the credentials.
    Connect-PnPOnline -Url "" -Credentials:devAdmin

    In the screenshot below, I’m connecting to my tenant using a label I created called CFAdmin1

Using the above technique of Credential Manager labels, you can make your PowerShell scripts easier by creating a string variable called label and pass it in. This will make running the same script for multiple environments easier.

    # The environment label to use for connection

    # The URL of the tenancy to create the site collections in, do not include the -admin
if ($VerbosePreference) {
    Set-PnPTraceLog -On -Level:Debug
else {
    Set-PnPTraceLog -Off
Connect-PnPOnline -Url:$URL -Credentials:$Label
… #Additional code to update Web

If the above file was called UpdateWebSite.ps1 I would type in the following:

.\UpdateWebSite.ps1 -Url:'' -Label:devAdmin

Building SharePoint 2016 development environment – Part 15 – Configuring Workflow

A few years ago I wrote “Build your SharePoint 2013 development machine on Windows Server 2012” series, I mainly work in the cloud now, but as the blogs was so popular, I thought I would create a new series for the newer version of SharePoint.

You can access other parts of this post below.

The configuration of the Workflow Manager for SharePoint 2016 is the same as it was for SharePoint 2013. Not only do you need to install the separate Workflow Manager components, once installed SharePoint designer will show SharePoint 2013 Workflow in a dropdown when deciding which platform to build the workflow on.

We will be installing Workflow Manager 1.0 CU3. Although my instructions add all this to the SharePoint Machine, the reason why Microsoft have made the Workflow manager separate is for scaling. There is no need for this to be installed on the SharePoint box. You could create another Windows Server 2012 R2 and add that to the domain and run the Workflow manager on that. There are probably a few more steps required in configuring. Here is a full walkthrough provided by Microsoft Technet if you wish to delve in deeper.

Installing SharePoint Designer 2013

Wait! SharePoint Designer 2013? Yes.

There is no SharePoint Designer 2016, there is no plan to release one either. Microsoft have stated that they will support SPD 2013 with SharePoint 2016. We are installing SharePoint Designer here because I can use it to prove if you have correctly configured Workflow Manager with SharePoint 2016 correctly.

SharePoint Designer 2013 is a free tool from Microsoft.

  1. Download SharePoint Designer 2013 32bit from the Microsoft Site.
  2. Once downloaded run the file sharepointdesigner_32bit.exe
  3. Accept the License terms and click Continue.
  4. Click Install Now, (Unless you wish to customise and change file location, user information etc)
  5. Once installed, I’d recommend performing a Windows Update. From the Start Menu, type Windows Update, open the application and run any updates required. Reboot if necessary.

Check to see Workflow settings in SharePoint Designer

  1. From the start menu, type SPD and open SharePoint Designer 2013.
  2. Once it has loaded up, click Open Site
  3. Type the URL click Open
  4. If prompted, enter your credentials
    User: cfcode2016\SP_SetupPassword: Pa55w0rd
  5. From the Navigation menu, select Workflows

  6. On the ribbon menu, select List Workflows > Documents

  7. In the Create List Workflow dialog, at the bottom you will see a dropdown box for Choose the platform to build your workflow on. Only SharePoint 2010 will be listed.

  8. When we come back to this later, we will see SharePoint 2013 Workflow. Close SharePoint Designer for now.

Configuring Workflow Manger accounts

The Workflow Manager will run under new accounts that we haven’t created yet.

  1. On the Domain Controller machine, in the start menu, type Active Directory Users and Computers and open it.
  2. Expand the tree in the left hand pane to see the Managed Service Accounts OU. Select the Managed Service Accounts OU.
  3. Right click in the right hand pane, and select New > User.
  4. Create a user called SP_Workflow. Set the full name and log on name to SP_Workflow. Click Next.
  5. In the password dialog screen, enter the following and click Next
    1. Password and Confirm Password as: Pa55w0rd
    2. Untick User must change password at next logon.
    3. Leave User cannot change password as unticked
    4. Tick Password never expires
    5. Leave Account is disabled as unticked
    6. Click Next. Then click Finished.

Setting up SQL with the correct Security Accounts

  1. On the SharePoint Machine, from the start menu, type SQL Server Management Studio and open up the application
  2. In SQL Server click Connect. (This should be to SQL2016 database instance).
  3. In the left hand menu expand Security. Right click Logins. And select New Login…
  4. In the Login – New dialog box, click the Search button.
  5. Click the Locations button and select Entire Directory.
  6. Type SP_Workflow in the Enter the object name to select, and click Check Names. This will resolve the name. Click OK.
  7. In the left hand panel select Server Roles.
  8. Tick both securityadmin and dbcreator then click OK.
  9. Close down SQL Server Management Studio

Giving SP_Workflow administrative rights on the SharePoint machine.

  1. From the start menu, type Edit local users and groups and open up the application.
  2. In the left hand panel, select Groups
  3. In the right hand pane, double click Administrators
  4. On the Administrators Properties dialog box, click Add
  5. Type SP_Workflow in the Enter the object name to select, and click Check Names. This will resolve the name. Click OK.
  6. Close Edit local users and groups.

Install the Microsoft Web Platform Installer 5.0

  1. Go to the URL and download the latest Microsoft Web Platform Installer
  2. Once downloaded run the file wpilauncher.exe
  3. If like my machine it is already on there, it will just open the Web Platform Installer 5.0 else it will install it for you. Accept the License Agreement and click Install. Then click Finish when complete.

Install Workflow manager

  1. From the Start menu, type Web Platform Installer and open the application

  2. In the search box in the top right of the screen, type Workflow Manager and press Enter.
  3. Click Add on the Workflow manager 1.0 Refresh (CU2) and click Install at the bottom.

  4. Click I Accept

  5. When complete, click Continue.

  6. Click Finish.

  7. Close the Workflow Manger Configuration Wizard that has popped up.

Apply Cumulative Update 3.0 for Workflow Manager 1.0

  1. Close and re-open the Web Platform Installer 5.0 we are going to install the CU 3. (You need to close and re-open otherwise the installer thinks Workflow Manger 1.0 hasn’t been installed)
  2. Type Workflow Manger and press Enter in the top right search box.
  3. Click Add for Workflow Manager 1.0 Cumulative Update 3, then click Install at the bottom.
  4. Click I Accept. Once installed click Finish. Click Exit on the Web Platform Installer.

Configure the Workflow manager

  1. From the start menu, type Workflow Manager Configuration
  2. Click on Configure Workflow Manger with Custom Settings
  3. In the Configure Farm Management Database,
    1. Enter your SQL Server Instance:
    2. Tick Use the above SQL Server Instance and Settings for all Databases
    3. Enter the Database Name: WF_ManagementDB
    4. Click Test Connection button to ensure all working OK.
  4. In the Configure Instance Management Database
    1. Enter the Database Name: WF_InstanceManagementDB
    2. Click Test Connection button to ensure all working OK.
  5. In the Configure Resource Management Database
    1. Enter the Database Name: WF_ResourceManagementDB
    2. Click Test Connection button to ensure all working OK.
  6. In the Configure Service Account
    1. Enter the User ID: CFCODE2016\SP_Workflow
    2. Enter the Password: Pa55w0rd
  7. In Configure Certificates
    1. Leave Auto-generate ticked
    2. Certificate Generation Key: Pa55w0rd
    3. Confirm Certificate Generation Key: Pa55w0rd
  8. In Configure Ports leave default port numbers
    1. https: 12290
    2. http: 12291
    3. Leave Allow Workflow management over HTTP on this Computer unticked
    4. Leave Enable firewall rules on this compute unticked (As we have disabled our firewall)
  9. In Configure Admin Group
    1. Leave BUILTIN\Administrators
  10. Click Next button
  11. On the Service Bus Configuration page, please provide the following
  12. In Configure Farm Management Database
    1. Enter the Database Name: Sb_ManagementDB
    2. Click Test Connection button to ensure all working OK
  13. In Configure Gateway Database
    1. Enter the Database Name: Sb_GatewayDB
    2. Click Test Connection button to ensure all working OK
  14. In Configure Message Container Database
    1. Enter the Database Name: Sb_MessageContainerDB
    2. Click Test Connection button to ensure all working OK
  15. In Configure Service Account
    1. Tick Use the same service account credentials as provided for Workflow Manager
  16. In Configure Certificate
    1. Tick Auto-generate
    2. Tick Use the same certificate generation key as provided for Workflow Manager
  17. In Configure Ports
    1. https: 9355
    2. tcp: 9354
    3. Message Broker Port: 9356
    4. Internal communication Port Range: 9000
    5. Untick Enable firewall rules on this computer (as we have disabled out firewall)
  18. In Configure Admin Group
    1. Leave BUILTIN\Administrators
  19. Click Next button
  20. On the Summary page, click the Tick button at the bottom right of the screen to start installation.
  21. The configuration process can take up to 10 minutes to complete. Once complete, you will see a success pag

Add Workflow Manager Certificate into SharePoint

  1. In Start Menu, type IIS and open Internet Information Services (IIS) Manager
  2. Expand your server name, and Sites. You will now see a site called Workflow Management Site

  3. Click on Workflow Management Site, then on the right hand pane, click Bindings
  4. Select https and click edit.

  5. On the Edit Site Binding, under SSL certificate you will see a Certificate that matches your Server Name. Click the View button.

  6. On the Certificate dialog, click on the Details tab.
  7. Then click Copy to File button.
  8. On the Certificate Export Wizard click Next.
  9. On the Export Private Key page, select No, do not export the private key, click Next

  10. On Export File Format page, select DER encoded binary X.509 (.CER) Click Next
  11. On File to Export page, select a path and filename on your machine. Click Next.

  12. Click Finish. You will receive a successful export message.

Import Certificate into SharePoint Trust

  1. Open SharePoint 2016 central administration
  2. Under Security > General Security click Manage Trust
  3. Click the New button in the ribbon.
  4. On the Establish Trust Relationship page, enter following information:
    1. Name: Workflow Manager
    2. Root Authority Certificate: <Select your file from previous steps>
  5. Click OK.
  6. You will see your certificate in the store.

Register Workflow Service Proxy

  1. In Start Menu, type SharePoint 2016 management Shell (run as administrator) and open the application
  2. In the console type:
    Register-SPWorkflowService -SPSite "" -WorkflowHostUri ""
    view raw RegisterWorkflow.ps1 hosted with ❤ by GitHub

Verify the Configuration of Workflow Manager.

  1. Open SharePoint 2016 central administration
  2. Click Application Management
    > Manage services applications
  3. At the bottom of the Manage Services Applications page, there will be Workflow Service Application Proxy

  4. If you click on Workflow Service Application Proxy it will take you a status page that will show you that workflow is now connected.

Check to see Workflow Settings are working in SharePoint Designer

  1. From the start menu, type SharePoint Designer and open the application
  2. Once SharePoint designer has opened, click Open Site.
  3. Type the URL click Open.
  4. If prompted, enter your credentials
    User: CFCode2016\SP_Setup
    Password: Pa55w0rd
  5. From the Navigation menu, select Workflows
  6. On the ribbon menu, select List Workflow > Documents
  7. In the Create List Workflow dialog, at the bottom you will see a dropdown box for Choose the platform to build your workflow on. Both SharePoint 2010 and SharePoint 2013 should be listed if the Workflow is set up correctly.

We are almost at the end. You SharePoint farm is configured to give you a good start as a development machine. Only thing left now is actual development tools. That will be covered in my final post of the series. Shut down your machines, take a checkpoint. (We will remove checkpoints in the last post)

Building SharePoint 2016 development environment – Part 12 – Configuring Hosting Apps and HNSC

A few years ago I wrote “Build your SharePoint 2013 development machine on Windows Server 2012” series, I mainly work in the cloud now, but as the blogs was so popular, I thought I would create a new series for the newer version of SharePoint.

You can access other parts of this post below.

Before I create the App Management Service, I’m going to create a separate Domain for the Apps. By creating a separate domain, it helps you write apps that won’t allow cross-site scripting between apps and SharePoint site.

Configuring Hosting Apps

First we need to configure DNS

  1. Go to you Domain Controller and from the Start Menu type DNS, and open the application.
  2. In the Left Hand panel, right click Forward Lookup Zones and select New Zone… Click Next
  3. Keep the Primary zone selected and Store the zone in Active Directory ticked.
    Click Next
  4. Leave the option To all DNS servers running on domain controllers in this domain: Click Next
  5. Here you enter the domain name, type Click Next
  6. Leave the top option selected and click Next
  7. Click Finish. You will see your new domain showing in the Forward Lookup Zones in DNS.
  8. Now right click on and select New Alias (CNAME) …
  9. Type * for Name
  10. Set the FQDN of the server that hosts the SharePoint sites, in my case. Click OK.

    If you are using more than one server, you should be pointing to the DNS record of the web server in here. This is either the DNS A record for the web server, or the DNS record of the primary cluster address for NLB environments.

    Now if you open a command window and type in nslookup it will resolve to your SharePoint server.

Configuring SharePoint 2016 for Hosting Apps

I would recommend to copy the following powershell script and running it as a ps1 file (CreateAppService.ps1 from my one drive). Change the Change any of the variables to match your environments.

  1. On the SharePoint box, logged in as SP_Setup, from the Start Menu, type SharePoint 2016 Management Shell.
  2. Run the Script
    if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null)
    #Add SharePoint PowerShell Commands
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
    $DatabaseServerName = "SQL2016"
    $AppPoolName = "Default SharePoint Service App Pool"
    $AppPoolUserName = "CFCODE2016\SP_Services"
    $AppDomain = ""
    $SubSettingsName = “Subscription Settings Service”
    $SubSettingsDB = “SP_SubscriptionSettingsDB”
    $AppManagementName = “App Management Service”
    $AppManagementNameProxy = "App Management Service Proxy"
    $AppManagementDB = “SP_AppManagementDB”
    $SubSvc = New-SPSubscriptionSettingsServiceApplication –ApplicationPool $AppPoolName –Name $SubSettingsName –DatabaseName $SubSettingsDB
    $SubSvcProxy = New-SPSubscriptionSettingsServiceApplicationProxy –ServiceApplication $SubSvc
    Get-SPServiceInstance | where-object {$_.TypeName -eq $SubSettingsName} | Start-SPServiceInstance > $null
    $AppManagement = New-SPAppManagementServiceApplication -Name $AppManagementName -DatabaseServer $DatabaseServerName -DatabaseName $AppManagementDB –ApplicationPool $AppPoolName
    $AppManagementProxy = New-SPAppManagementServiceApplicationProxy -ServiceApplication $AppManagement -Name $AppManagementNameProxy
    Get-SPServiceInstance | where-object {$_.TypeName -eq $AppManagementName} | Start-SPServiceInstance > $null
    Set-SPAppDomain $AppDomain
    Set-SPAppSiteSubscriptionName -Name “apps” -Confirm:$false
    view raw CreateAppService.ps1 hosted with ❤ by GitHub
  3. To verify the script configured SharePoint 2016 correctly open Central Administration
    1. Under Application Management click Manage Service Application.
    2. You should now have two new service application created
      1. App Management Service Application
      2. Subscription Settings Service Application
    3. Now Navigate to System Settings by clicking the link on the left menu
    4. Under Servers
      click the link Manage Services on Server.
    5. Check that the following services have started
      1. App Management Service
      2. Microsoft SharePoint Foundation Subscription Setting Service
    6. On the left hand menu, click on Apps
    7. Under App Management, click the link Configure App URLs
    8. Verify that:
      1. App Domain:
      2. App Prefix: app

Configuring SharePoint Server 2013 for Host-Named Site Collection and create Initial Site Collections.

Here we are going to create Host Named Site Collection (HNSC) for testing and hosting our apps. Microsoft recommends this because the Office 365 environment uses host-named site collections, new features are optimized for these site collections and they are expected to be more reliable. More can be found out directly from the technet article: . The only sites within your environment you should use Path Based Site Collections (PBSC) are Search Center and MySites. HNSC aren’t really needed for Search Center. The only way you can create HNSC is via powershell. So this is what we are going to do.

Register SP_Content

  1. Open SharePoint Central Administration
  2. Select Security > Configure managed Accounts.
  3. Click Register Managed Account
  4. Type Username as cfcode2016\SP_Content and the password as Pa55w0rd. Then click OK.

Create a new Web Application

Open up a PowerShell window and put the following: (change the port number if you wish) (CreateHNSC.ps1)

$applicationPool = "SharePoint - HNSC - 11111"
$ServiceAcct = "cfcode2016\SP_Content"
$WebApp = "SharePoint HNSC Web Application"
$contentDB = "SP_HNSC_ContentDB"
New-SPWebApplication -ApplicationPool $applicationPool -ApplicationPoolAccount $serviceAcct -Name $WebApp -Port 11111 -AuthenticationProvider (new-spauthenticationprovider) -databaseName $contentDB -secureSocketsLayer
view raw CreateHNSC-1.ps1 hosted with ❤ by GitHub

Configuring the Alternative Access Mapping

  • From the Start Menu
    open SharePoint 2016 Central Administration, this ensures it runs as Administrator.
  • Click Application Management, then under Web applications,
    click Configure alternative mappings.
  • On the right hand side of the screen, Change the Alternate Access Mapping Collection to point to SharePoint HNSC Web Application.
  • Click the internal URL for https://cfsp2016:11111 so that you can edit it. Change the URL protocol, host to
  • Click OK.
  • Back on the Alternate Access Mapping Screen, click Add Internal URLs and add a new Internal URL for each of the following listed below. Screenshot below

Add certificates to IIS

  • In Start type IIS and open IIS Manager
  • Navigate to SharePoint HNSC Web Application and then on the right hand panel, click Bindings…
  • On the Bindings dialog, click Add…
  • In the Add Site Binding page, select https from the Type dropdown, leave the IP address as All Unassigned, the Port should say 443. Enter the Host name as,
    and tick Require Server Name Indication then select your certificate you created earlier. Click OK
  • Add the binding for host names and, ensure the Type is https, you have ticked Require Server Name Indication and you have selected your certificate.

Creating the Top level Site

Because the top-level site is an HNSC is not going to be used by anyone in the site. Therefore, this PowerShell script will create a blank site. (CreateHNSC.ps1)

  1. In PowerShell run the following script:

    New-SPSite -Url "" -OwnerAlias "cfcode2016\SP_Setup" -Template STS#1
    view raw CreateHNSC-2.ps1 hosted with ❤ by GitHub

Site Collections

Here we are going to create a TeamSite called and a developer site called Please note you can only create, debug and test apps using a developer site. You could type the PowerShell into notepad, save the file as PS1 and run it from SharePoint 2016 Management Shell, instead of typing each row directly. We are first going to create 2 databases, one for each Site collection. This is good practice for backups and restore purposes.

  1. From the Start Menu, type SharePoint 2016 Management Shell, and open the application. (CreateHNSC.ps1)
  2. Type
    $devdb = “SP_DEVDB”
    $intranetdb = “SP_IntranetDB”
    $webApp = “SharePoint HNSC Web Application”
    #Build Databases
    new-SPContentDatabase -Name $devdb -WebApplication $WebApp -WarningSiteCount 0 -MaxSiteCount 1
    new-SPContentDatabase -Name $intranetdb -WebApplication $WebApp -WarningSiteCount 0 -MaxSiteCount 1
    $hnsc = Get-SPWebApplication | Where-Object {$_.DisplayName -eq $webApp}
    New-SPSite -Name “CF Development” -Url –HostHeaderWebApplication $hnsc -OwnerAlias “cfcode2016\SP_Setup” -Template “DEV#0” -contentDatabase $devdb
    New-SPSite -Name “CF Intranet” -Url –HostHeaderWebApplication $hnsc -OwnerAlias “cfcode2016\SP_Setup” -Template “STS#0” -contentDatabase $intranetdb
    view raw CreateHNSC-3.ps1 hosted with ❤ by GitHub

To verify that the host-name site collections are created:

  1. Open up SharePoint 2016 Central Administration
  2. Under Application Management click View all Site Collections
  3. Ensure the Web Application is pointing to the HNSC web and you should see the two site collections plus the root site.
  4. By clicking on the different site collections, you will also see that the Database Name is assigned correctly to the correct database as set up in our PowerShell script.
  5. You can also navigate in a browser to or Notice that the SSL certificate is valid.

Configuring SSL for Apps

As our App domain is on a different domain to our SharePoint domain, we should create a different SSL certificate for it.

  • Ensure you are on the SharePoint box with a Domain Admin Account. (cfcode2016\Administrator)
  • We have already configured the Certificate Authority earlier on the Domain Controller. Here we are going to request the certificate using Internet Information Services on the SharePoint Server. From the Start Menu, type IIS and open Internet Information Services (IIS) Manager
  • Once IIS opens, click on the Server Name. (CFSP2016) You will be prompted with a dialog asking to get started with Microsoft Web Platform, click do not show this message and then click No.
  • From the IIS section, double click Server Certificates

  • From the right hand side of the screen, click Create Domain Certificate

  • Complete the form for the Domain Certificate as follows (Change to match your environment if not following exactly along)
    • Common Name : *
    • Organisation: CF Code
    • Organizational Unit: Computers
    • City/Locality: London
    • State/Province: London
    • Country/Region: GB

  • Click Next
  • On the Online Certification Authority enter the common name you gave your Authority Name\Server Name. (For example mine is MY-CA\, You can also use the select button if you have configured everything correctly. You can put anything in the friendly name box, ensure it is different from your other certificate friendly name, and easy identifiable as the Apps certificate. Click Finish.

  • You should now see the certificate in the Server certificates window.

  • If there were other servers in your farm, you would need to export the .pfx file so that it can be imported into the other servers.

Configure SharePoint for Apps

We need to configure our SharePoint and IIS to use a different certificate for Apps, and also our Web Application needs to know to use our App Domain.

  1. Sign back into the SharePoint machine as SP_Setup.
  2. Run as administrator, SharePoint 2016 Management Shell
  3. Run the following PowerShell Script

    New-SPWebApplicationAppDomain -AppDomain "" -WebApplication "" -Zone Default -Port 11111 -SecureSocketsLayer

  4. Next we need run the following command:
    $contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
    $contentService.SupportMultipleAppDomains = $true
    view raw AppMultiDomains.ps1 hosted with ❤ by GitHub
  5. In Start type IIS and open IIS Manager
  • Navigate to SharePoint HNSC Web Application and then on the right hand panel, click Bindings…
  • On the Bindings dialog, select the one without the Host Name on Port 11111 and click Edit
  • Change the Port to 443, and select the SSL Certificate as your App Certificate.
  • Click OK.

Add Apps to your Intranet Zone.

To prevent getting prompted for your login, configure the intranet zone in IE.

  • Open up Internet explorer
  • Click on the cog symbol, and select Intranet Options
  • Select the Security tab, and then click on Local Intranet. Then click on the Sites button.
  • On the Local intranet dialog, click the Advanced button.
  • Type * and click Add. (You might need to untick Require server verification (https) for all sites in this zone )
  • Then click Close, OK, and OK

Checking that Apps are new set up for your farm

  1. Open up your intranet site
  2. At the top right of the screen click the cog icon.
  3. From the drop down, click Add an app
  4. On the App page, in the quick launch menu area, click on SharePoint Store
  5. If you have connected up correctly you will now see Apps that you can download from the SharePoint store.
  6. Pick a free one to install. I’m selecting Bright Banner. (Have no idea if it’s any good, so not endorsing, just using for testing purposes)
  7. Click Add it.
  8. Confirm that you wish to add the app. Click Continue
  9. A page will state that you have just go this app for everyone in your organization. Click Return to Site
  10. A prompt will appear, asking if you trust the application. Click Trust It.
  11. After a moment you will be returned to your Site Contents. You will also note that your app that you downloaded is currently being added to your site. Once complete the adding text will disappear.

  12. Click on the App. It will load. Take note of the URL. It is being called from the domain you created earlier Congratulations you have got Apps working!

So glad I finally got Apps certificates to work. Took me a couple of attempts. Thank you to Anupam Shrivastava and his blog post for helping finally cracking it.

I recommend shutting down and taking checkpoints again.

Building SharePoint 2016 development environment – Part 11 – Configuring Services

A few years ago I wrote “Build your SharePoint 2013 development machine on Windows Server 2012” series, I mainly work in the cloud now, but as the blogs was so popular, I thought I would create a new series for the newer version of SharePoint.

You can access other parts of this post below.

In this blog I will talk about setting up your environment, by setting up the services. I will be doing majority of this using PowerShell scripts. The scripts will be within this post, but at the end there is a link to my one drive that will contain the PowerShell scripts.

Setting up Services

At the end of the last blog post we Installed SharePoint 2016, however we told the wizard that we would configure the services ourselves. These steps will configure the following services:

  • Managed Metadata Service
  • Word Automation Service
  • BDC
  • Secure Store
  • Usage and Health Data Collection Service
  • Create Machine Translation Service

We are doing this as a script so that Application and Database names are friendly names instead of GUIDs. Before you run the script, on your SharePoint machine, please make sure there is a folder at D:\SPLogs\ULS. This is the folder required for Usage and Health Data Collection Service.

When you run the script there will be a prompt for the SP_Services password. Pa55w0rd. Lastly ensure that your PowerShell window is running in Administrator mode. (CreateSPServices.ps1)

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null)
#Add SharePoint PowerShell Commands
Add-PSSnapin "Microsoft.SharePoint.PowerShell"
$DatabaseServerName = "SQL2016"
$AppPoolName = "Default SharePoint Service App Pool"
$AppPoolUserName = "CFCODE2016\SP_Services"
$MetaDataName = "Manage Metadata Service"
$MetaDataDBName = "SP_MetadataDB"
$WordASName = "Word Automation Service Application"
$WordAutomationDatabaseName = "SP_WordAutoDB"
$BDCServiceName = “Business Data Connection Service Application”
$BDCDB = “SP_BusinessDataConnectionDB“
$SecureStoreName = “Secure Store Service Application”
$SecureStoreProxyName =“Secure Store Service Application Proxy”
$SecureStoreDB = “SP_SecureStoreDB”
$usageName = “Usage and Health Data Collection Service”
$usageServiceDB = “SP_Usage_HealthDB”
$usageLogLocationOnDisk = “D:\SPLogs\ULS\”
$stateName = “State Service”
$stateServiceDB = “SP_StateServiceDB”
$MTSInst = “Machine Translation Service”
$MTSName = “Translation Service”
$MTSDB = “SP_MachineTranslationDB”
#Check if Service account exist and Create.
Write-Host "Getting Service Account / Creating Service Account"
$SAAppPool = Get-SPServiceApplicationPool -Identity $AppPoolName -EA 0
if($SAAppPool -eq $null)
#Get App Pool account
$AppPoolAccount = Get-SPManagedAccount -Identity $AppPoolUserName -ErrorAction Continue
if($AppPoolAccount -eq $null)
$AppPoolCred = Get-Credential $AppPoolUserName
$AppPoolAccount = New-SPManagedAccount -Credential $AppPoolCred -ErrorAction Continue
$AppPoolAccount = Get-SPManagedAccount -Identity $AppPoolUserName -EA 0
if($AppPoolAccount -eq $null)
Write-Host “Cannot create or find the managed account $appPoolUserName, please ensure the account exists.”
Exit -1
New-SPServiceApplicationPool -Name $AppPoolName -Account $AppPoolAccount -ErrorAction Continue > $null
#Create Manage Metadata Service
Write-host "Creating Manage Metadata Service"
$mmsApp = New-SPMetadataServiceApplication -Name $MetaDataName –ApplicationPool $AppPoolName -DatabaseServer $DatabaseServerName -DatabaseName $MetaDataDBName > $null
New-SPMetadataServiceApplicationProxy -Name “$MetaDataName Proxy” -DefaultProxyGroup -ServiceApplication $MetaDataName > $null
#Start MMS
Get-SPServiceInstance | where-object {$_.TypeName -eq “Managed Metadata Web Service”} | Start-SPServiceInstance > $null
#Word Automation Service
Write-Host "Create Word Automation Service"
Get-SPServiceApplicationPool –Identity $AppPoolName | New-SPWordConversionServiceApplication -Name $WordASName -DatabaseName $WordAutomationDatabaseName
write-Host "Create BDC"
New-SPBusinessDataCatalogServiceApplication –ApplicationPool $AppPoolName –DatabaseName $BDCDB –DatabaseServer $DatabaseServerName –Name $BDCServiceName
#Secure Store and Proxy
write-Host "Create Store and Proxy"
$SecureStoreServiceApp = New-SPSecureStoreServiceApplication –ApplicationPool $AppPoolName –AuditingEnabled:$false –DatabaseServer $DatabaseServerName –DatabaseName $SecureStoreDB –Name $SecureStoreName
New-SPSecureStoreServiceApplicationProxy –Name $SecureStoreProxyName –ServiceApplication $SecureStoreServiceApp -DefaultProxyGroup
#Usage and HEalth Data Collection service
## Begin Variables for usage and health data collection and state service, make sure the C:\Logs\ULS location exists first ##
write-host "Create Usage and Health"
#Change location
Set-SPUsageService -LoggingEnabled 1 -UsageLogLocation $usageLogLocationOnDisk -UsageLogMaxSpaceGB 2
$serviceInstance = Get-SPUsageService
New-SPUsageApplication -Name $usageName -DatabaseServer $DatabaseServerName -DatabaseName $usageServiceDB -UsageService $serviceInstance > $null
$stateServiceDatabase = New-SPStateServiceDatabase -Name $stateServiceDB
$stateSA = New-SPStateServiceApplication -Name $stateName -Database $stateServiceDatabase
New-SPStateServiceApplicationProxy -ServiceApplication $stateSA -Name “$stateName Proxy” -DefaultProxyGroup
$sap = Get-SPServiceApplicationProxy | where-object {$_.TypeName -eq “Usage and Health Data Collection Proxy”}
#ChangeLocation and create new files.
Set-SPDiagnosticConfig -LogLocation $usageLogLocationOnDisk
#Machine Translation Service + Proxy
write-host "Create Machine Translation Service and Proxy"
$AppPool = Get-SPServiceApplicationPool $AppPoolName
Get-SPServiceInstance | ? {$_.GetType().Name -eq $MTSInst} | Start-SPServiceInstance
$MTS = New-SPTranslationServiceApplication -Name $MTSName -ApplicationPool $AppPool -DatabaseName $MTSDB
$MTSProxy = New-SPTranslationServiceApplicationProxy –Name “$MTSName Proxy” –ServiceApplication $MTS –DefaultProxyGroup
write-host "DONE"
view raw CreateSPServices.ps1 hosted with ❤ by GitHub

Once this code is done, if you head to Central Administration > Application Management then under Service Applications click Manage Service Applications you will see all your new services created.

Also now note, if you open explorer and navigate to D\:SPLogs\ULS you will see your SharePoint logs being displayed there instead of the default location of c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\Logs

Configuring Created Services – Secure Store Service Application

Now we have created the services, they all should be working apart from the Secure Store. It requires a little configuration before it can be used.

  1. Open SharePoint 2016 Central Administration (run
    as administrator) and navigate to Application Management > Manage service applications (Under Service Applications)
  2. Click on Secure Store Service Application. You will see an error message saying you must generate a new key for this Secure Store Service Application. Click on Generate New Key in the ribbon.
  3. On the dialog that appears, put in a Pass Phrase and Confirm Pass Phase. I’m using Pa55w0rd. Click OK.

Creating a Search Service Application

This could also be configured using a PowerShell script, but at times I prefer to do point and click.

  • Open the Manage Service Applications screen again.
  • From the ribbon, click on New and select Search Service Application.
  • This will show you a dialog called Create New Search Service Application. Enter the following information:
    • Service Application Name: Search Service Application
    • Search Service Application Type: Leave unticked. (Out of scope of this blog post for hybrid)
    • Search Service Account: Click on Register new managed account.
      • Service Account UserName : CFCODE2016\SP_Search
      • Service Account Password: Pa55w0rd
      • Click OK.
    • Search Service Account: CFCODE2016\SP_Search
    • Application Pool for Search Admin Web Service: Use existing application pool – Default SharePoint Service App Pool
    • Application Pool for Search Query: Use existing application pool – Default SharePoint Service App Pool
  • Click OK
  • You get messages displayed to you as the service is created.
  • You will get a success message when completed.

We will configure it further in a later blog post where we create the Enterprise Search center. You might want to shut down your machines and take a checkpoint before continuing.

Onedrive link to PowerShell scripts