Add a SQL Alias using PowerShell

Setting a SQL alias on every SharePoint server is a common task when you are installing SharePoint. You use the SQL Server Client Network Utility (cliconfg.exe) for this. This tool is available on every SharePoint server because it’s part of the SQL Server Native Client prerequisite.

Setting a SQL alias is a best practice because it makes your life a whole lot easier when you want to change the actual database server in some point of time. If you use an alias, the only thing you need to do at that moment, is change the target of your alias and you’re good to go. If you install SharePoint and you reference the database server directly, your only way of pointing SharePoint to the new database server painlessly, is to create an alias at that time, set the name of the alias to the name of the old database server and have it point to the new server. Definately not cool because when someone looks at the Servers in Central Administration, it will list the old name and it’s not clear that this is not a server anymore but an alias.

When you want to set an alias, you run cliconfg.exe on each SharePoint server. In this tool, you have an “Alias” tab, where you can set it. You provide a name, the type of connection (Named Pipes, TCP/IP) and a server name. You can also select a custom port if you use TCP/IP or keep the default.

sql alias - 01

If you don’t want to do this manually, there’s also a way of doing this with PowerShell. The only thing this tool does, is create a string value in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

sql alias - 01

So, doing this using PowerShell is easy.

You can find this script in my PowerShell Repository on GitHub.

Toggle audience targeting using PowerShell

Audience targeting in SharePoint is a great way of hiding irrelevant information from users and likewise, only show relevant information. You could create a list with a lot of items and target those items to specific groups of users. Users who are targeted will see the items while other users will not see them… audience targeting. This has nothing to do with permissions. It’s just a filter. Users who are not targeted can still access those items if they want.

To be able to use audience targeting on a list, you have to enable it first. It’s disabled by default. To enable this, you can go to the list settings of your list and open the Audience Targeting settings. There you will find a checkbox to enable this.

Once it’s enabled, you will see an extra column in your list “Target Audiences” of the type “Audience Targeting”.


To disable it, go back into the audience targeting settings and uncheck the checkbox. The extra column will be removed.

That’s the UI way… now, how about PowerShell? Can you enable/disable audience targeting? Offcourse!

We only need to add a field with a specific ID and attributes to the list and it’s done.

Read more

Get Web Template Usage in SharePoint

Having a SharePoint platform which is widely adopted and used is cool. But as a SharePoint administrator I’m also interested in how this platform is used and where it’s used for. One of the things I like to do is to find out which kind of sites are used and how many of them exist.

This can also be quite useful when you are considering a migration to a new SharePoint version. Not all site templates are supported anymore in SharePoint. For example, the document workspaces, meeting workspaces, and group work templates. These are partially supported when migrating from SharePoint 2010 to SharePoint 2013 or are just supported for backward compatibility. At the same time, there are also custom solutions out there which add site templates and it’s good to know when preparing for a migration, if you have sites which are based on those kind of templates.

The tricky thing is… how to get that information. There’s a cmdlet which is called Get-SPWebTemplate. This gives you a list of all web templates which have been installed in the environment.


This doesn’t give you which templates are actually used. For this, you need to combine some information. The name of a web template consists of 2 parts:

  • Web Template
  • Configuration ID

When you look at the properties of an SPWeb object, you find these 2 specific properties in the list of properties. You need to combine these 2 with a “#” between them to have the webtemplate.Get-SPWebTemplate1

This allows you to identify the template for each web which exists in your farm. But this is only part of the info we need. We need the usage count of each used template and get something like this:


To get this output, I wrote a small script.

If you would like to sort the output of the script to have the most used templates first, you can execute it like this:

which gives you the following:



SharePoint 2016 Fast Site Creation vs. Traditional site creation

Creating site collections in SharePoint has always been a task which requires some patience. It can take a lot of time to create a site. With the release of SharePoint 2016, a new feature is introduced that listens to the name “Fast Site Creation”. I’m not going to explain what it does because there are enough places out there which do an excellent job in explaining it. You can find some here, here, and here.

The basic idea is that the Fast Site Creation feature gives an administrator the ability to create site collections rapidly by offloading the actual creation completely to the database server where a site is created by copying an existing “master” site.

That’s the theory. In this post, I’m going show you the actual difference between both methods in the context of execution time. I have been searching for this on the internet and apparently, everybody who is talking about this feature, is just repeating what Microsoft says. Seems that nobody is actually wondering if this feature is actually faster.

So, I did it myself. I ran some tests, comparing both methods.

Read more

User Profile Sync DB is read-only in SharePoint 2016

When you provision a new User Profile Service application in SharePoint 2016, you will notice that the Sync DB name and server is read-only and that you cannot change it.


I don’t know why exactly Microsoft made it impossible to change the name of the Sync database in the UI. Has it something to do with the absence of the synchronization service? Either way, this DB is created and despite all good efforts to have a consistent naming convention for your SharePoint databases, you end up with this rebelling database.

But then again, who uses the UI to get things done in SharePoint anyway, right? We do everything with PowerShell and in PowerShell, you have a cmdlet New-SPProfileServiceApplication which allows you to specify the name of the databases:

Solved it! When you open the properties of your created user profile service application, you see that this small rebellion was suppressed swiftly and without making a lot of fuss.


Get a document from an unattached SharePoint content database

Retrieving a lost document in SharePoint is easy. You go to the recycle bin and you restore it. That is, if it hasn’t been deleted more than 60 days ago. But what do you do when you need to retrieve a specific document which has been overwritten with an older version and yes, Murphy is in town… you don’t have versioning enabled?

In that case you better be having backups of the content database which contains the site where the document is stored because you need to retrieve the document directly from a backup of the content database.

Now, restoring a backup of a content database is probably not something you want to do to get a specific document because you will restore all sites in that content database to a previous state. 1 happy user vs. hundreds of angry ones… ouch! But if you don’t restore it, how are you going to get a good copy of the document?

Well, you need to restore the content database under a different name. Once it has been restored, you can use PowerShell to access it as an “unattached content database” where you can access the content as if it was attached to your web application.

The Get-SPContentDatabase cmdlet has a -ConnectAsUnattachedDatabase parameter which gives you a reference to an unattached content database.

Once you have the database reference, you can use the Get-SPSite cmdlet to get your site reference from that database.

From there on, you need the web and the list where the document is stored and offcourse, the document name. With all of this information, you can get a reference to the document item in the database. Only thing left to do is to extract this item as a document.

I combined everything into one convenient script which takes a number of parameters and get you the item from the database.

Read more

SharePoint 2016 farm configuration using PowerShell

With the arrival of SharePoint 2016, farm configuration changed a bit. A new feature was added which allows you to choose the role of the server when you create a new SharePoint farm, or when you are joining a server to an existing farm. This feature is called MinRole. In PowerShell, there are 2 cmdlets which are involved in the creation of a farm or in the joining in an existing farm.

These cmdlets have a new parameter -LocalServerRole which accepts a Microsoft.SharePoint.Administration.SPServerRole value.

I created 2 complete scripts which use these cmdlets. These scripts have a parameter -SPVersion which accepts 3 values: 2010, 2013, and 2016. This makes these scripts usable for SharePoint 2010, 2013, and 2016. When 2016 is specified, the user will be prompted to select the role.

Update 04/10/2016 : The Join-Farm script has been updated. After adding a server to a farm, there are 2 services which are not started immediately:

  • SharePoint Timer Service
  • App Fabric Caching Service

The script has been updated to check for these services and will start them if needed. The App Fabric Caching service is obviously only started if the server has been chosen to be a distributed cache host.

Update 10/11/2016 : I have updated the scripts below somewhat.

  • They now include the extra 2 server roles which came with Feature Pack 1.
  • The parameters are changed to be a bit more dynamic. For example, you now have a switch parameter to indicate if it’s a 2010, 2013 or 2016 farm. Depending on that switch, a different set of parameters is applicable.

Read more

Export/Import the SharePoint Root Authority Certificate using PowerShell

Installing SharePoint is mostly a repetitive process with lots of small tweaks and actions. One of those small actions you need to do after adding a server into a SharePoint farm, is adding the “SharePoint Root Authority” Certificate to the Trusted Root Certification Authorities store of the server. You would think this happens automatically during the configuration process. Well, no.

The result is that when you add a server to an existing farm, or you create a new farm, SharePoint will add 3 certificates to the “SharePoint” certificate store on the server.


All of these certificates will have a status: “The issuer of this certificate could not be found”.


Is this a big problem? Does it break SharePoint? Well, no. SharePoint will work happily without it, but users can experience delays when logging into a site, performing a search and even experience HTTP timeouts when doing these things.

The reason is that since the certificate chain is not complete, the CRL (Certificate Revocation List) check is done over the internet. If the CRL server cannot be contacted (let’s say, due to the isolation of the server from the internet), the operation will time out after 15 seconds and the rendering of the page will happen after those 15 seconds. At the same time, 2 events are logged in the eventlog, which can be found in the CAPI2 eventlog. You need to enable the CAPI2 event logging first to see them.

This behaviour is well documented in KB2625048. This article is for SharePoint 2010, but it’s also valid for SharePoint 2013 and it provides 2 workarounds.

To fix this, 2 actions are required:
– Export the SharePoint Root Authority certificate from SharePoint
– Import it into the local certificate store

These tasks are outlined in the KB article but because it involves point and click, wizard style… I created a small script for this task, just because I can! And I really hate doing these kind of things every time again. It costs time, it costs money, and it’s so much more fun creating scripts to make you more efficient in what you do.

Change UPN Suffix using PowerShell

A few weeks ago, I was implementing AD synchronization between an on-prem Active Directory and Office 365. One of the prerequisites is that the UPN (User Principal Name) suffix for the users which are synchronized to Office 365, has to be a public domain name. The domain I was working with, was a local domain (.local). So, I had to change the UPN suffixes for all users to the public domain name. Because I had to change thousands of users, I created a PowerShell script which does it for me. The script does a few checks before it actually tries to update the UPN:

  • It checks if the “ActiveDirectory” module is installed. We need this to interact with the objects in Active Directory. If it’s installed, it’s loaded automatically if not already loaded.
  • It checks if the new UPN suffix, which needs to be provided by means of a parameter, is registered as a UPN suffix in the domain.

The script has 4 parameters:

  • OldUPNSuffix : This is UPN Suffix which is going to be replaced with the new one.
  • NewUPNSuffix : This is the new UPN suffix
  • Filter : This is a filter which is used in the Get-ADUser cmdLet and is used to retrieve all user objects we are working with. For more information on the filters which can be specified, check the documentation for the Get-ADUser cmdLet.
  • Mode : I made the script to run in a “List” and “Modify” mode. When you specify “List” as value for this parameter, the actual change is not done but it’s only logged in a file. Comes in handy when you want to see the results before you actually unleash it with the “Modify” value. And yes, I know… you can do this with the -whatif also. But then again, I prefer my logfile output over a scrolling command console with a massive amount of output.

Now, for the script… here it is. Nothing to fancy really.