Search Service Application stops working after adding a user to Administrators

I came across an interesting bug while trying to add a user the Administrators of a Search Service Application in SharePoint 2013. When I tried adding the user, and clicking OK, and error is returned: “User does not have permission to perform this action” along with a correlation ID. Further investigation in the ULS logs revealed that the problem was SQL permission related: “The EXECUTE permission was denied on the object ‘proc_MSS_GetConfigurationProperty’, database ‘SPSearch’, schema ‘dbo’.” Additionally performing a search fails and logs the error: “There was an exception in the Database. Please retry your operation and if the problem presists, contact an administrator.” (The error message has a typo too).

After researching, it appeared that when adding a user to the Search Service Application Administrators using the web Interface, the SPSearchDBAdmin role is removed from all users, including the Search Service Account, for the search databases (Search, AnalyticsReportingStrore, CrawlStore, & LinkStore). This problem can be resolved by running the follow in SQL script:

USE SPSearch
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your search service account>’;
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your account to add>’;

USE SPSearch_AnalyticsReportingStore
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your search service account>’;
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your account to add>’;

USE SPSearch_CrawlStore
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your search service account>’;
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your account to add>’;

USE SPSearch_LinksStore
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your search service account>’;
EXEC sp_addrolemember ‘SPSearchDBAdmin’, ‘<Your account to add>’;

It has also determined that adding a user using PowerShell will not break the Search Service Application. Here is a sample script:

Add-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue

$administrator=”<Your account to add>”
$SearchServiceApplication = Get-SPEnterpriseSearchServiceApplication
$principal = New-SPClaimsPrincipal $administrator -IdentityType WindowsSamAccountName
$security = Get-SPServiceApplicationSecurity $SearchServiceApplication –Admin
Grant-SPObjectSecurity $security $principal “Full Control”
Set-SPServiceApplicationSecurity $SearchServiceApplication $security -Admin

Small .pdf files reporting – “The item has been truncated in the index because it exceeds the maximum size”

Recently, I encountered an issue with SharePoint 2013 search crawls where .pdf files smaller than 1 MB were reporting a warning: “The item has been truncated in the index because it exceeds the maximum size”. The default MaxDownLoadSize for documents in SharePoint is 64MB, which was more than enough the handle these relatively small .pdf files.

After I reached out to some co-workers; one had suggested that the error might be a false-positive and the entire document had been crawled. I tested this by first searching for words at the end of the document and no matches were found; this would be expected if it were truncated. Next, I tried searching for some text in the middle of the document and no matches were found either. I thought it must have truncated a lot of text and tried searching for text contained at the very beginning of the document. No results were found! So when the warning said it had truncated the item, it had truncated the whole document.

I decided to test a Microsoft Word document of approximately the same size and found that it did not throw a warning. I then exported that Word document to .pdf and crawled it and surprisingly no warning was thrown. I took a look at the properties of both .pdf files. I found that problem .pdf file was version 1.3 (Acrobat 4.x) and had been generated by Microsoft SQL Server Reporting Services (SSRS), while my test document was version 1.6 (Acrobat 6.x).

Figure 1. PDF Version 1.3 (Acrobat 4.x) generated by SQL Server Reporting Services (SSRS).

Figure 2. PDF Version 1.5 (Acrobat 6.x) generated by Microsoft Word

This got me thinking that problem was version 1.3, so I converted my .pdf to 1.3 and crawled it. I was surprised to find that it did not throw the warning as I expected it too. This would mean that either 1) the culprit was SSRS report generation (not the version 1.3), or 2) there was something about converting a version 1.5 to a version 1.3 that fixed the issue. With this in mind, I preferred to find a centralized solution rather than converting every .pdf document in our site.

I decided to try using the official Adobe IFilter in place of the one that is built-in to SharePoint 2013. In order to leverage the custom IFilter, I did the following:

[sourcecode language="powershell"]
Add-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue
$iFilterPath = "C:\Program Files\Adobe\Adobe PDF iFilter 11 for 64-bit platforms\bin\"
$oldPath=(Get-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment' -Name PATH).Path
    Write-Host "Adding Environment Path"
    Set-ItemProperty -Path 'Registry::HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\Environment' -Name PATH –Value $newPath

Write-Host "Setting Up Registry entries"
$path = "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\15.0\Search\Setup\Filters\.pdf"
if(Test-Path $path) {Remove-Item -Path $path -Recurse}
New-Item $path
New-ItemProperty -Path $path -Name Extension -PropertyType String -Value "pdf"
New-ItemProperty -Path $path -Name FileTypeBucket -PropertyType DWord -Value 1
New-ItemProperty -Path $path -Name MimeTypes -PropertyType String -Value "application/pdf"

$path = "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\15.0\Search\Setup\ContentIndexCommon\Filters\Extension\.pdf"
if(Test-Path $path) {Remove-Item -Path $path -Recurse}
New-Item $path
Remove-Item -Path $path\* -Recurse
New-ItemProperty -Path $path -Name "(Default)" -PropertyType MultiString -Value "{E8978DA6-047F-4E3D-9C78-CDBE46041603}"

$ssa = Get-SPEnterpriseSearchServiceApplication
$filter = Get-SPEnterpriseSearchFileFormat -SearchApplication $ssa -Identity pdf
    Write-Host "Enabling IFilter for SharePoint"
    Set-SPEnterpriseSearchFileFormatState -SearchApplication $ssa -Identity pdf -UseIFilter $true -Enable $true

    Write-Host "Restarting Search Service"
    $service = Get-SPServiceInstance | ? {$_.TypeName -eq "Search Host Controller Service"}
    $service | Stop-SPServiceInstance -Confirm:$false
    while(-not (($service | Where-Object {$_.Status -eq "Disabled"}).Count -eq $service.Count)){
        write-host -ForegroundColor Yellow $service.Status; sleep 5;
        $service = Get-SPServiceInstance | ? {$_.TypeName -eq "Search Host Controller Service"}
    $service | Start-SPServiceInstance
    Write-Host "Restarting IIS"


After executing the script, I performed a full crawl and was pleased to find that all of the .pdf size-related warnings were gone. I performed a search using the same criteria as before from the end, middle, and beginning of document and this time results were returned.


While I am not certain if the cause of this issue is SSRS document generation or legacy .pdf version 1.3 documents, I was able to solve this issue using the official IFilter from Adobe. If someone else encounters this problem, hopefully this will help.

SharePoint 2013 Default KeywordQuery Results Columns

Here is a list of the columns that are returned by default for relevant results of a KeywordQuery:

  • Rank
  • DocId
  • FileType
  • SecondaryFileExtension
  • TitleAuthor
  • Size
  • Path
  • Description
  • EditorOWSUSER
  • LastModifiedTime
  • CollapsingStatus
  • HitHighlightedSummary
  • HitHighlightedProperties
  • FileExtension
  • ViewsLifeTime
  • ParentLink
  • ViewsRecent
  • IsContainer
  • DisplayAuthor
  • docaclmeta
  • ResultTypeIdList
  • PartitionId
  • UrlZone
  • AAMEnabledManagedProperties
  • ResultTypeId
  • RenderTemplateId
  • piSearchResultId

Setting up SharePoint 2010 in Amazon Web Services

I was recently given the task to migrate a SharePoint environment to Amazon Web Services (AWS). With limited exposure to cloud computing, I was trying to figure out what shopping on Amazon had to do with moving a SharePoint environment. After a little bit of research, I learned about the cloud concept of an Infrastructure as a Service (IaaS) and that Amazon has been offering IaaS since 2006 with the introduction of Amazon Web Services.

Note to the Reader

The scenario that this blog post is based on required the complete migration of all servers out of a datacenter into the cloud with no on-premise domain controller (like many of you might encounter). The approach describe in t post might not be the best cloud solution for your situation, be sure to do your homework before choosing a path to the cloud. There are other IaaS providers; or perhaps a Platform as a Service (PaaS) such as Microsoft Azure is more suited for your situation.

What is Amazon Web Services?

Amazon Web Services (AWS) in the simplest of terms is a complete datacenter in the cloud. Traditionally, when a business starts, IT assets are purchased, installed, and maintained at a physical location (on-premise). The amount equipment required to set up a datacenter might include routers, switches, cabling, servers, cooling, power, battery backup, monitors, and much more. AWS can provide virtual equivalents of these assets for a relatively small price, reducing the total cost of ownership for the consumer. If the business or idea does not work out, simply shut down the devices and/or cancel your account.

Key Terms

While many of us are might be familiar with the hardware required to set up a

  • Elastic Computer Cloud (EC2) – EC2 is effectively your virtual server room, providing you access to other resources (ex. virtual machines, virtual private network, etc.)
  • Amazon Machine Image (AMI) – AMI is Amazon’s version of a virtual machine; you may be familiar with virtual machines if you have worked with VMware Workstation, Oracle VirtualBox, etc.
  • Virtual Private Cloud (VPC) – In the simplest of terms, a VPC is your virtual private network. If you have ever set up a home network, you probably know that all of the machines on your network have internal IP addresses (ex. 192.168.X.X or 10.0.X.X) and they all share a static IP to communicate with the rest of the Internet; this is your virtual private network.
  • Simple Storage Service (S3) – S3 is a scalable storage solution that allows you to store and retrieve any amount of data anytime.
  • Elastic Block Storage (EBS) – EBS allows you to create and store volumes (virtual hard drives) that can then be attached to AMI Instances.
  • Elastic IP – Is an IP address that can be allocated from Amazon at any time for a cost. An elastic IP can then be associated to any Instance of an AMI for public access.

Setting up a SharePoint Environment

Creating a VPC

If you plan on having more than one server (such as a 3-tier SharePoint environment), the first thing that you will want to do is set up a VPC; so that you can add your instances to it when you create them.

  1. Navigate to the VPC tab.
  2. Click Create a VPC
  3. Select the VPC that best fits your needs (VPC with a Single Public Subnet will be appropriate for most situations)
  4. Click Continue
  5. Edit your VPC IP CIDR Block and Public Subnet as needed (Defaults should be fine for most situations)
  6. Click Create VPC

Creating Security Groups

There are a variety of ways that security groups can be set up. What I ended up doing was creating one for each server type: Domain Controller (dc-sg), Database (db-sg), and SharePoint (sp-sg). For the dc-sg, db-sg, and sp-sg, I opened all incoming traffic for the private subnet ( and port 3389 from anywhere ( For sp-sg, I also opened incoming port 80 traffic from anywhere (

To create a security group:

  1. Navigate to the VPC tab
  2. Click Security Groups
  3. Click Create Security Group
  4. Enter Name and Description
  5. Select the VPC that you will adding your servers to
  6. Click Yes, Create


With your virtual network out of the way, it is time to stand up some servers. Depending on the type of environment that you are standing up, the type of instance and AMI used can vary as well as the price.

Choosing an Instance Type

At the time of this writing, AWS offers 4 standard instances that should be considered:

Name Memory CPU Storage
Small (m1.small) 1.7 GB 1 virtual core 160 GB
Medium (m1.medium) 3.75 GB 1 virtual core 410 GB
Large (m1.large) 7.5 GB 2 virtual cores 850 GB
Extra Large (m1.xlarge) 15 GB 4 virtual cores 1690 GB

View all Instance Types
View Instance Pricing

At the time of this writing, Microsoft minimum hardware requirements are as follows:

Tier Scenario Memory CPU Storage
Domain Controller All 8 GB 4-core 80 GB
Database Small deployment 8 GB 4-core 80 GB
Database Large deployment 16 GB 8-core 80 GB
SharePoint All 8 GB 4-core 80 GB

View Microsoft SharePoint 2010 minimum requirements

So what instance should you use? In my opinion, the answer is, like most answers in development, it depends. While AWS recommends Extra Large instances for all servers and High Memory Quadruple Extra Large (not listed) for the database server, this might necessarily be true for all situations. For example, if you were setting up a development environment, you could probably get away using all Small instances. If your SharePoint environment has relatively small usage, Large instances might work for you. Additionally, you need to consider licensing; for example, you could pay Amazon licensing for SQL Server by using one of their SQL AMIs or you could use a regular AMI and install SQL Server using your licensing. Take the time to know your environment; you might want to read the Amazon Web Services SharePoint whitepaper.

Creating Instances

Once you have decided the type of instance(s) that you plan to use:

  1. Navigate to the EC2 tab
  2. Click Instances
  3. Click Launch Instance
  4. Locate an AMI (ex. Microsoft Windows Server 2008 R2 Base) and click Select
  5. Select the Instance Type
  6. Select the VPC tab and select the VPC that you created previously
  7. Click continue
  8. (Optional) Select Prevent against accidental termination (this will prevent you from accidently deleting your instance)
  9. (Optional) Enter an IP Address (if you want your IP address to be in order like I do)
  10. Click Continue
  11. (Optional) Enter a Name (good way to know which server is which) (note: this is not the computer name, just a name within AWS)
  12. Click Continue
  13. Select or create a Key Pair (these are used to encrypt/decrypt the initial Windows administrator password) (make note of where you store your .pem file)
  14. Select the Security Group that you created previously or create one if needed
  15. Click Continue
  16. Check everything and then click Launch

Connecting to an Instance

At this point, an Instance is created from the AMI and is started. Monitor the State and Status Checks and wait for them to both to be green (running and 2/2 checks passed). This can take several minutes, so be patient. Once ready, we can give the instance an Elastic IP Address.

  1. Navigate to EC2
  2. Click Elastic IPs
  3. Click Allocate New Address
  4. Select VPC
  5. Click Yes, Allocate
  6. Check the box next to the Elastic IP that was just created
  7. Select Associate Address
  8. Select the desired instance
  9. Click Yes, Associate

Now that the instance has a public IP, we are ready to connect:

  1. Navigate to EC2
  2. Click Instances
  3. Check the box next to the desired instance
  4. Select the Instance Actions drop down list
  5. Select Get Windows Admin Password
  6. Open the .pem file that you downloaded when you created your key pair, copy ALL of its contents and paste it into the box labeled Private Key
  7. Click Decrypt Password
  8. Your password will be displayed (copy it, save it, memorize it….whichever you choose)
  9. Select the Instance Actions drop down list
  10. Click Connect
  11. From here, you can either download the RDP file or set one up manually and connect to your instance

Making Software Available

Now that you have your instances available, you might be wondering how you are going to install software such as SQL Server and SharePoint; after all, there is no virtual DVD drive. Like most things in the computer world, there are many ways to anything; I tend to stick to what I consider to be simple. I simply created a semi large volume, attached it to an instance, and copied/pasted in RDP. Alternatively, you could download it from MSDN from the server, setup Internet Explorer Enhanced Security (IEEC), and so on. Either way, I believe that it is a good idea to have this software on a volume somewhere that can be used by all machines (either by file share or dismount/mount). To create a volume and mount it to an instance:

  1. Navigate to EC2
  2. Click on Volumes
  3. Click Create Volume
  4. Enter the Size
  5. Select the Availability Zone that your instances reside in
  6. Do not select a Snapshot
  7. Click Yes, Create
  8. Check the box next to the volume that was just created (it is most likely the only one with a State of available)
  9. Select the …More drop down list
  10. Click Attach Volume
  11. Select the instance that you would live to attach it to
  12. Enter xvdf for the Device
  13. Click Yes, Attach
  14. Connect to the Instance
  15. In Windows, navigate to Administrative Tools > Computer Management > Disk Management
  16. Locate the volume that you just attached (it should be Offline)
  17. Right Click on the volume and select Online
  18. You should now be able to use the drive


Amazon Web Services, LLC. (2012). About AWS. Retrieved May 22, 2012, from

Amazon Web Services, LLC. (2012). Amazon EC2 Instance Types. Retrieved May 23, 2012, from

Cloudiquity. (2012). Difference between S3 and EBS. Retrieved May 23, 2012, from


Using Powershell to export & import farm solutions

Here is a handy script that will export all farm solution .wsp and .cab files at once:

[sourcecode language=”powershell”]
Add-PSSnapin Microsoft.SharePoint.PowerShell
$farm = Get-SPFarm
$farm.Solutions | ForEach-Object{$_.SolutionFile.SaveAs(“c:\export\” + $}

Once you have moved the solutions to another machine, you can use the following script to add the solutions to the new farm:

Add-PSSnapin Microsoft.SharePoint.PowerShell
$files = Get-ChildItem “c:\install\”
ForEach ($file in $files) {Add-SPSolution $file.FullName}