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.

SPRootAuthority1

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

SPRootAuthority2

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.

SharePoint date metadata is off by 1 day when added as field in a document

A while ago, I was at a customer providing support for their Office 365 implementation and they had a strange issue regarding date metadata which had been added to the document itself as a field.
When they opened the document in Word Online (View mode), it actually displayed the date -1 day. If they opened the document in “Edit” mode, it showed the correct date. Editing the document in the Word client application also displayed the correct date.

I started testing this in our own Office 365 tenant to see if this was a general issue or just a glitch.

I added a date/time column to a document library. I created an empty document and added it to the library. I set the review date for the document.

ReviewDate1

I opened the document in the Word client and added the field inside the document.

ReviewDateThe correct date is inserted. When I saved the file and reopened it in the Word client application, again, the correct date was displayed.

ReviewDate2

I returned to my document library and opened the document in Word Online.

ReviewDate3

And there you go… instead of showing me the correct date, it shows me the date -1 day.

I had this tested by some other people as well and they also confirmed this behaviour.

SharePoint 2013 Trial License – License Upgrade FAIL

I consider myself as someone who knows a lot of SharePoint. I don’t know everything and I’m learning new things every day. Which is good because this motivates me. One of those things I learned (the hard way) in the last couple of weeks is about licensing and the consequence of using a SharePoint TRIAL license. Let me elaborate a bit on this.

Last year, I installed 2 SharePoint farms at a client. When you install SharePoint, the first thing you need to provide, is the license key. Because nobody was able to provide me the key at that time, I used a trial key which is valid for 180 days. You can get this key over here.

A few weeks ago, I was notified that a Microsoft audit was on the way concerning licensing and they ran the Microsoft Assessment and Planning Toolkit to have an overview of the licenses. The result was that all involved SharePoint servers were identified as ENTERPRISE servers. This was a problem since they don’t have Enterprise licenses and were expecting Standard servers.

I was convinced I never activated any Enterprise features in both environments and ran some PowerShell scripts to see on all kind of levels if Enterprise features were active and to my surprise, they were activated on all sites. Since none of those features are used, I proceeded to deactivate all of them.

The report was created again… still Enterprise servers.

Then I remembered that I used a trial key and the Upgrade License page in Central Administration allows you to see the current license and replace that license with a different license.

And there was the culprit… the Trial key for SharePoint 2013 is a “SharePoint Server Trial with Enterprise Client Access License

My first reaction was: “I never saw an option to choose between a Standard and Enterprise Trial”. And that’s correct… there’s only 1 trial and that’s Enterprise!

I checked the page where you can find the evaluation version and you won’t find any reference or notification of the fact that the trial version is an Enterprise license!!!! Come on!? Why not? The only possible hint of this being an Enterprise version is the mentioning of “full-featured” in the Preinstall Information section. But that’s interpretation, right?

One would think that you could simply put in your Standard license key and “upgrade” that Enterprise trial to a Standard server license, right? Well, I wish it was that simple. enterprisetrial

It’s simply not possible. You cannot replace the Trial license with a Standard Server license. It expects an Enterprise server license key. Somehow this makes sense. When you look at SQL Server, the same thing applies. You can’t downgrade an Enterprise Edition to a Standard Edition either.

Options?

Well, not many I’m afraid. The only viable option I found was to uninstall SharePoint completely. And by uninstalling, I’m referring to this. That’s removing the binaries from the servers and reinstalling them. Upon installation, provide the Standard Server License key and get that show on the road. Luckily, you can hook up the databases from the old trial environment without issues.
This is such a situation where having a detailed installation/configuration documentation pays off. I make a habit of documenting everything in full detail with screenshots in OneNote, with the scripts I use and the parameters which are used for them. This makes it very easy to redo it if needed. So, doing this reinstallation was a breeze for me.

But I can imagine that if you don’t have such documentation or you have it but some wannabe professionals came in and started modifying things manually, not documenting a thing… you might be in a world of hurt.

As a seasoned SharePoint professional, I have to admit that I was a bit shocked of the fact I didn’t know this. This seems like a “Duh! Basic knowledge!” kind of thing. Definitely not my best day when I found out about this. 😳

 

Customize SSRS extension settings in SharePoint Integrated Mode

SQL Server Reporting Services (SSRS) is powerful way of presenting reports and data to your end users in SharePoint. But sometimes, the out of the box experience of some features of SSRS doesn’t work for your situation. A nice example of this is the field delimiter for CSV exports. This is by default a comma. But in some regions (like mine), the default delimiter is a semicolon and using a comma doesn’t work.
Another example is the different formats which are available for exporting. What if you want to remove some of those supported export formats?

When you start searching the net, you will find some information on how to achieve the above customizations by adding or changing some settings in the RSReportServer.config file. Check the link below for the information on that configuration file.

http://msdn.microsoft.com/en-us/library/ms157273(v=sql.110).aspx

While this works fine for a regular SSRS installation, it will not work for a SharePoint integrated SSRS installation because you don’t have that config file on your system.

The solution for this is PowerShell. All of those customizations can be done this way by means of changing SSRS extension settings.

Change the CSV field delimiter

Remove export formats from the supported list

The script above removes the “TIFF” and “MHTML” formats from the list of available export formats. The complete list of formats which you can include or exclude is the following:

  • XML
  • CSV
  • ATOM
  • PDF
  • RGDI
  • EXCEL
  • EXCELOPENXML
  • RPL
  • IMAGE
  • WORD
  • WORDOPENXML
  • HTML4.0
  • MHTML

If you want to add it to the list, specify “True” as value for the “Visibility” element in the -ExtensionAttributes parameter.

Process items in a Drop Off library using PowerShell

Everyone knows the Drop Off library in SharePoint, right? It routes documents to a final destination, based on rules you define.
This works OK for documents which are uploaded manually to the Drop Off library but I found out that it doesn’t work for documents which are uploaded with PowerShell (or any other method which uses the object model). Even if I fill in all required metadata and make sure the document it checked in… it still won’t leave the Drop Off library.

Seems that there’s a timer job in SharePoint (Content Organizer Processing) which runs daily and processes items which are left behind in Drop Off libraries. That’s great but what if you want to process that document immediately?

I found an article from Steve Lineberry which explains how to do this in C#. He used reflection to break open the timer job and found out that it was calling an internal method.

I needed this in PowerShell, so I did some translation and created a PowerShell function which does the same.

SharePoint 2013 Promoted Links Wrapping

Earlier this week, I was creating a page in SharePoint which contained 2 Promoted Links web parts. One of those web parts had 12 links. The annoying thing about this web part is that there’s no wrapping when the tiles are displayed. If you have a limited number of links, you won’t have an issue. But if you have 12, like me, not all tiles will fit the screen and you will get scroll buttons. Not nice.

promotedLinks1

One of the solutions to have the tiles wrapped is Javascript. I found a script over here.

This script works well but has a limitation… it only works when you have 1 Promoted Links web part on your page. Once you have more, the results are somewhat… unpredictable, depending on the number of combined tiles of all the web parts.

Since I know a little of Javascript and JQuery, I figured… hey, why not enhance it and make it work for more web parts on a single page.

The results…

promotedLinks2 Looks a lot better, no? 😎

The script looks at each web part individually and wraps the tiles independently within the web parts.

Here’s the updated script.

I’m not an expert in JQuery, so if you like this script and you have suggestions to improve it, please get back to me so I can improve it and give the proper credits.

Filter a View on Liked By in SharePoint 2013

SharePoint 2013 allows users to rate individual items in a list or library. This rating can be in the form of a “star” rating from 0 to 5 or in the form of “likes”. This is disabled by default for a list or library.

The rating data is stored in 5 columns which are added to your list or library when you enable this functionality:

  • Number of Ratings
  • Number of Likes
  • Rating (0-5)
  • Rated By
  • Liked By

The columns “Number of Ratings”, “Number of Likes” and “Rating (0-5)” are visible to the end user. The other 2 columns “Rated By” and “Liked By” are hidden.

This information is transformed by SharePoint in a nice view which makes it somewhat sexier for the end user. You can see this in the screenshot below where the rating information is visualized in the “Favorited” column of my library.

050614_1507_CreatingaMy4

The user will see a “Like” or “Unlike” link. At the same time, the number of likes is displayed together with a smiley.

So far so good.

Imagine you want to use this rating information to filter the list or library to show you only those items which have been liked or rated by you.

The columns you need for this information is the “Liked By” and “Rated By” columns. These columns are collections of users. The problem with this is that those 2 fields are not available for filtering when you use the UI.

You can use CAML to create a query which returns you only those items where the current user is contained in one of these fields.

The query above returns items which are liked by the user who executes the query.

When you look at the SPViewCollection.Add method, you notice that it accepts a “query” parameter.

So, to have a view filtered using the query, we can simply create the view using PowerShell.

This works… on premise.
But what if you have a SharePoint Online. You can still use PowerShell but you need a slightly different approach because instead of using the server object model of SharePoint, you need to use the client object model. You can use something like below to do the same on SharePoint Online. I must admit, I’m not an expert on CSOM with PowerShell (yet :mrgreen: ) and I’m still getting up to speed with this. I suppose the code below can be simplified. But hey… it gives you an idea on the general approach what you need to do to create a view with a query on a library which is hosted on a SharePoint Online .

SharePoint Foundation which is installed during TFS 2013 installation has no search capability

This week, I have been doing some tests with Team Foundation Server 2013 with regards to the search capability in a SharePoint which is installed during the installation of Team Foundation Server 2013.

I installed a Team Foundation Server 2013 application server and a separate SQL Server to host the databases. I opted to install SharePoint Foundation 2013 during the TFS installation on the same application server. The installation went well and everything looked fine. TFS was running and the SharePoint teamsites were also purring like a kitten.

But then I tried to do a search from the default team site which was created for me. I got the following message:

“Your search cannot be completed because no Search service is available. Contact your administrator for more information.”

This would make sense because I never configured anything remotely resembling a search service. I headed over to Service Applications in Central Administration and this is what I saw.

TFS search capability - 1

I clicked “New” and nothing… nothing happened. No dropdown.

Then I remembered that in order to get your Search Service Application provisioned you have to create it using the configuration wizard in Central Administration.

I started the wizard and when I got to the page where I needed to select the services I want, I saw only 1 service. The Usage and Health Data Collection service.

TFS search capability - 2

Not really encouraging. No Search Service Application. I decided to look in the Services console of my server and see if I had 2 services which you normally should have when you install SharePoint (SharePoint Server Search 15 and SharePoint Search Host Controller).

TFS search capability - 3

Wow! Really!? Where’s the SharePoint Server Search 15? Now what?

Time for a little test. I reverted back to a snapshot I took before I started the TFS installation and did a new installation in 2 steps.

  • Install SharePoint Foundation 2013, which I downloaded here.
  • Install TFS 2013 and opt to use the default SharePoint location. TFS detects the presence of a local SharePoint installation.

When I installed SharePoint Foundation manually, I ran the configuration wizard and saw that there were more service applications to provision.

TFS search capability - 4

One of them… Search Service Application. I continued with the wizard and let it provision all of the service applications. When the wizard had finished, I turned to the Services console on the server and saw that both Search services were there and were running.

TFS search capability - 5

Naturally, when navigating to the Service Applications page, the Search Service Application was also available.

TFS search capability - 6

I uploaded some documents to the site which was created for the default team project collection, kicked off a full crawl and performed a search… and voila, instant results!

TFS search capability - 7

What does this mean? If you let TFS install SharePoint Foundation in the process, you don’t have search capability? It sure looks that way.

Let’s see… when you install SharePoint Foundation separately, the “SharePoint Server Search 15” service points to c:\program files\Windows SharePoint Services\15.0\Bin\mssearch.exe.

After going to this location on a server where SharePoint was installed by the TFS installer, there is a folder c:\program files\Windows SharePoint Services\15.0\Bin but there’s no mssearch.exe!!!!

That’s bad. Really bad! This clearly indicates that the SharePoint Foundation which ships with TFS is not a full SharePoint Foundation at all. Too make sure it wasn’t just a “glitch”, I installed it 3 times and got 3 times the same result. Even another colleague who did the same thing… also the same result.

Moral of the story:

Never let the TFS installer install SharePoint for you! Install it manually before you install TFS and then reuse it during TFS installation.

If there’s somebody who says “Bart, my friend, you’re doing it wrong. It should definately work without having to install SharePoint manually”, well, put your money where your mouth is and show me how it’s done. I will gladly admit that I was wrong here.

Update 21/03/2014:
We filed a bug report for this on Microsoft Connect and we received an answer from the Visual Studio product team which confirms my suspicion: SharePoint Foundation 2013 which is included with TFS2013 is a stripped version of the complete SharePoint Foundation.

Here’s the answer from Microsoft on this:

Hello – If you choose to install SharePoint through TFS, we will only configure services that you would need to use for TFS-supported integration features.  We do this to conserve resources.  Search does not fall into this category, and moreover is one of the largest consumers of memory in SharePoint, so it is not advised to turn it on if you are running SharePoint and TFS on the same box.  I would encourage you to reach out to the SharePoint team to ask why you cannot add this service after initial configuration.  In the meantime, you should install SharePoint yourself on a separate server if you want to pick and choose services for yourself.  The SharePoint installation that TFS performs is simply a convenience for customers who only want to use SharePoint Foundation for integration with TFS.

Reference: https://connect.microsoft.com/VisualStudio/feedback/details/835522/sharepoint-foundation-which-is-installed-during-tfs-2013-installation-has-no-search-capability#tabs

Getting Sync To Outlook to work in SharePoint 2013

Earlier this week, a customer requested me to look into an issue they had with the “Sync To Outlook” functionality in SharePoint 2013.

When you click that buttton in the ribbon when you are on a tasks list, you get a popup where you can “check” a checkbox  to “Sync tasks”. When everything works, the popup will disappear and it’s done. But everytime they tried it, they got the message :

“We weren’t able to start syncing your tasks because one or more pre-requisites for connecting to Exchange Server could not be found. Please contact your administrator”

To get this working, there are indeed some pre-requisites. All of them can be found in the following article:

Configure Exchange task synchronization in SharePoint Server 2013

I started to look at the requirements and quickly noticed that everything should be OK.

So, I decided to have a look at the ULS. The message which appeared also includes a correlation ID. Hoorah!

Opened my ULS Viewer on the WFE and looked for all entries for the correlation ID I got. Disappointment… not a single hint of something going wrong.

Perhaps on the other servers?

I opened the created file and hello!!! There were a lot more entries than I expected. Most of them were coming from the APP server and one of them was the one I needed…

02/25/2014 09:37:00.43 w3wp.exe ([APPSERVER]:0x0DE0) 0x100C SharePoint Portal Server Exchange Task Synchronization ah8z3 Medium Exchange sync – Missing Exchange Managed API library d7a5779c-6452-8013-6174-40db0fdedf78

The Exchange  Web Services API was missing … on the APP server. I replaced the real name of the server above with [APPSERVER]. And this was indeed correct since the article specified that you need to install this on each WFE.

There’s a nice white paper from Microsoft which explains how the My Tasks Aggregation and Exchange Task aggregation works and after reading this I’m starting to think that it’s not specifically the WFE where the Exchange Web Services API has to be installed but the server where the Work Management Service is running.

After installing the Exchange Web Services API and doing an IISRESET, the error disappeared and the sync was working.