Win7 SharePoint 2016 SSL and Office 2013

Some times you have to get older clients to talk to newer servers, and this was the situation I was in recently. 2 new SharePoint 2016 Farms has been build (Pre and Prod) on Windows 2016 – same server build and same SharePoint build, both running over SSL.

From a Windows 10 client running Office 2016/365 everything was fine with Office integration on both systems, but on a Windows 7 client running Office 2013 Office integration was failing with the following error when doing anything with Office Documents on the Pre system but worked perfectly on Prod (same client)

 

This article seemed to suggest that the issue was based in the TLS settings on the Windows servers as Windows 7 by default will only support TLS1.0.

According to IISCrypto both systems fully support TLS1.0 – but clearly something was different.

A lot of organisations are moving to newer versions of TLS and Windows 7 does has an update to allow it to work with later version of TLS, but in this instance we didn’t have time to test and release an update, so it was time to fire up Wireshark and do some tracing.

From Win 7 client opening a Word doc against Prod.

from the same  client against Pre

A bit hard to see but we were getting TLS1.0 Handshake Failures when using Office 2013.

We tracked the issue down to the Ciphers used at Load Balancer level – once this was updates everything worked perfectly.

SharePoint 2013 User Profile Service Application Publishing

While working on a recent project one of the tasks we had to perform was to publish a Service Application across 2 SharePoint 2013 farms.

The steps involved are clearly set out on this TechNet Article

https://technet.microsoft.com/en-gb/library/ff621100.aspx

The SA we were publishing was the User Profile.

The first step is to Exchange the Trust Certificates between the Publishing and Consuming farms, this is very straight forward with clear instructions. https://technet.microsoft.com/en-gb/library/ee704552.aspx

The next step is to publish the service application from the publishing farm, again a straightforward task.

https://technet.microsoft.com/EN-US/library/ee704545.aspx#ProcCA

The Service Applications that can be published are

• Business Data Connectivity

• Machine Translation

• Managed Metadata

• User Profile

• Search

• Secure Store

The next step is to set permissions on the published Service Application on the publishing farm

https://technet.microsoft.com/EN-US/library/ff700211.aspx

using the ID of the consuming farm.

Finally connect to the published Service Application from the consuming farm

https://technet.microsoft.com/EN-US/library/ee704558.aspx

We followed all the above steps to publish and consume the User Profile Service Application all this seemed to work fine until we came to use a site on the consuming farm.

On a site on the consuming farm we were getting the “Sorry, this site hasn’t been shared with you” message from site contents, site, settings and the site sharing screens from an account in the site owners group, trying to get to user settings was throwing a correlation error.

Checking the ULS log on the consuming farm, we were showing errors trying to connect to the SQL instance for the publishing farm.

As both the publishing and consuming farms were built with a SQL alias the fix was to create the alias for the publishing farm on the servers for the consuming farm, this allowed the consuming farm to be able to connect to the SQL instance for the publishing farm.

#sp2013 Site Collection Audit Reports for Business Users

While working on recent 2013 project we had a requirement to allow business users to view site collection audit reports but not be site collection administrators.

Looking into this we could see that there was no way simple way to do this via permissions, but it was actually a very simple request to fulfil.

Firstly we setup site collection auditing for the opening of items (this was the only requirement) and set daily log trimming to a location that the end users can get to.

 

The next step was to configure the audit log trimming job from being monthly to daily to force the log generation.

 

Now we have a library with daily audit reports that business users can access.

 

 

Follow @NeilKing41

Automating SharePoint 2013 Usage Reports

In SharePoint 2010 the site collection Web Analytics  reports could be viewed by end users or business owners via the ‘View Web Analytics’  permission.

With SharePoint 2013 and usage reporting move into the Search Service Application this is much more difficult unless you want to grant business owners full control on your site.

Recently we had to try and grant business owners the ability to read the usage reports without being sites owners, as this was impossible via permissions we had to look for another method.

After some searching I came across this excellent link from Mike Smith that shows how to save the central search reports back the SharePoint Server.

http://techtrainingnotes.blogspot.co.uk/2015/04/run-sharepoint-2013-search-reports-from.html

I updated this to run and save all the site collection usage reports then upload the reports to a library in SharePoint, finally I set a daily task to run the report to create self service solution for end users.

 

Add-PSSnapin “Microsoft.SharePoint.PowerShell" -ea Continue

# Set the intranet portsl location
$WebURL = “http://intranet.corp/”

#This is the URL for site collection usage reports
$url = "http://intranet.corp/_layouts/15/Reporting.aspx?Category=AnalyticsSiteCollection"

# This is the path to write the reports to must exist on server running the script
$path = "D:\SearchReports\"

function Get-SPSearchReports ($url, $searchreport, $path)
{
  # function to run the usage reports and store locally on server
  # Usage reports ID’s must match the environment, use View source on the page _layouts/15/Reporting.aspx?Category=AnalyticsSiteCollection to get the ID’s

  # Usage Reports for Prod
  
   $Usage                   = "6bbf6e1c-d79a-45da-9ba0-d0c3332bf6e2"
   $Number_of_Queries         = "df46e7fb-8ab0-4ce8-8851-6868a7d986ab"
 
  # Search Reports forProd

  $Top_Queries_by_Day         = "06dbb459-b6ef-46d1-9bfc-deae4b2bda2d"
  $Top_Queries_by_Month       = "8cf96ee8-c905-4301-bdc4-8fdcb557a3d3"
  $Abandoned_Queries_by_Day   = "5dd1c2fb-6048-440c-a60f-53b292e26cac"
  $Abandoned_Queries_by_Month = "73bd0b5a-08d9-4cd8-ad5b-eb49754a8949"
  $No_Result_Queries_by_Day   = "6bfd13f3-048f-474f-a155-d799848be4f1"
  $No_Result_Queries_by_Month = "6ae835fa-3c64-40a7-9e90-4f24453f2dfe"
  $Query_Rule_Usage_by_Day    = "8b28f21c-4bdb-44b3-adbe-01fdbe96e901"
  $Query_Rule_Usage_by_Month  = "95ac3aea-0564-4a7e-a0fc-f8fdfab333f6"

  # set the file path and name
  $filename = $path + (Get-Variable $searchreport).Name + ".xlsx"
  $reportid = (Get-Variable $searchreport).Value

  $TTNcontent = "&__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId%3D" + $reportid

  # setup the WebRequest
  $webRequest = [System.Net.WebRequest]::Create($url)
  $webRequest.UseDefaultCredentials = $true
  $webRequest.Accept = "image/jpeg, application/x-ms-application, image/gif, application/xaml+xml, image/pjpeg, application/x-ms-xbap, */*"
  $webRequest.ContentType = "application/x-www-form-urlencoded"
  $webRequest.Method = "POST"

  $encodedContent = [System.Text.Encoding]::UTF8.GetBytes($TTNcontent)
    $webRequest.ContentLength = $encodedContent.length
    $requestStream = $webRequest.GetRequestStream()
    $requestStream.Write($encodedContent, 0, $encodedContent.length)
    $requestStream.Close()

  # get the data
  [System.Net.WebResponse] $resp = $webRequest.GetResponse();
    $rs = $resp.GetResponseStream();
    #[System.IO.StreamReader] $sr = New-Object System.IO.StreamReader -argumentList $rs;
    #[byte[]]$results = $sr.ReadToEnd();
    [System.IO.BinaryReader] $sr = New-Object System.IO.BinaryReader -argumentList $rs;
    [byte[]]$results = $sr.ReadBytes(10000000);

  # write the file
  Set-Content $filename $results -enc byte

}

Function Upload-Report($URL, $DocLibName, $FilePath)
{
# function to upload the locally stored reports to SharePoint
# Get a variable that points to the folder
$Web = Get-SPWeb $URL
$List = $Web.GetFolder($DocLibName)
$Files = $List.Files

# Get just the name of the file from the whole path
$FileName = $FilePath.Substring($FilePath.LastIndexOf("\")+1)

# Load the file into a variable
$File= Get-ChildItem $FilePath

# Upload it to SharePoint
$Files.Add($DocLibName +"/" + $FileName,$File.OpenRead(),$true)
$web.Dispose()
}

# delete anything in the d:\SearchReports folder
Remove-item D:\SearchReports\*

# run the reports

Get-SPSearchReports $url "Usage" $path
Get-SPSearchReports $url "Number_of_Queries" $path
Get-SPSearchReports $url "Top_Queries_by_Day" $path
Get-SPSearchReports $url "Top_Queries_by_Month" $path
Get-SPSearchReports $url "Abandoned_Queries_by_Day" $path
Get-SPSearchReports $url "Abandoned_Queries_by_Month" $path
Get-SPSearchReports $url "No_Result_Queries_by_Day" $path
Get-SPSearchReports $url "No_Result_Queries_by_Month" $path
Get-SPSearchReports $url "Query_Rule_Usage_by_Day" $path
Get-SPSearchReports $url "Query_Rule_Usage_by_Month" $path

# upload the reports

Upload-Report $WebURL "Audit Reports\Site Collection Usage" “D:\SearchReports\Usage.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Number_of_Queries.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Abandoned_Queries_by_Day.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Abandoned_Queries_by_Month.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\No_Result_Queries_by_Day.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\No_Result_Queries_by_Month.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Query_Rule_Usage_by_Day.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Query_Rule_Usage_by_Month.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Top_Queries_by_Day.xlsx”
Upload-Report $WebURL "Audit Reports\Search" “D:\SearchReports\Top_Queries_by_Month.xlsx”

SharePoint 2013 Blank Usage Reports

While working on a SharePoint 2013 Portal project we noticed that the Usage Reports were blank on one Farm, exactly as described in this article.

Following the steps in the article we tested 2 farms. one farm had the receivers defined and was showing information in the usage reports, the other farm has no receivers defined and had blank usage reports.

We noticed that the farm with working Usage Reports was running SharePoint Server with Enterprise Client Access License, the farm with the non working Usage Reports was running SharePoint Server with Standard Client Access License.

Checking on Technet we found that Usage Analysis is an Enterprise only license feature

https://technet.microsoft.com/en-us/library/jj819267.aspx#bkmk_FeaturesOnPremise

 

So seems to be a by design feature, adding the receivers to the Standard farm would probably make it work, but may break the license agreement.

Happy SharePointing

Follow @NeilKing41

Excel Calculation Services and RBS

On a recent project I came across a strange problem with Excel Calculation Services.

On a SharePoint 2013 farm with multiple web applications, Excel services was working perfectly on a couple of the web applications but not on others. The trusted locations and process identity settings were set and checked but were not at fault, digging through the ULS logs was showing this.

10/01/2014 15:37:57.58  w3wp.exe (0x7130)        0x4220  Excel Services Application            Excel Calculation Services                ech0      Medium               ExcelService.PostProcessRequest: web method: OpenWorkbook, got exception Id=GenericFileOpenError; Microsoft.Office.Excel.Server.CalculationServer.FileOpenException: The workbook cannot be opened. —> Microsoft.Office.Excel.Server.Host.HostFileException —> Microsoft.SharePoint.SPException: Cannot complete this action.  Please try again. —> System.Runtime.InteropServices.COMException: Cannot complete this action.  Please try again.

    at Microsoft.SharePoint.Library.SPRequestInternalClass.GetFileAsStream(String bstrUrl, String bstrWebRelativeUrl, Boolean bHonorLevel, Byte iLevel, OpenBinaryFlags grfob, String bstrEtagNotMatch, Object punkSPFileMgr, Boolean bHonorCustomIrm, IrmProtectionParams fileIrmSettings, UInt32& pdwVirusCheckStatus, String& pVirusCheckMessage, String& pEtagNew, String& pContentTagNew, SPFileInfo& pFileProps)

    at Microsoft.SharePoint.Library.SPRequest.GetFileAsStream(String bstrUrl, String bstrWebRelativeUrl, Boolean bHonorLevel, Byte iLevel, OpenBinaryFlags grfob, String bstrEtagNotMatch, Object punkSPFileMgr, Boolean bHonorCustomIrm, IrmProtectionParams fileIrmSettings, UInt32& pdwVirusCheckStatus, String& pVirusCheckMessage, String& pEtagNew, String& pContentTagNew, SPFileInfo& pFileProps)     — End of inner exception stack trace —

    at Microsoft.SharePoint.SPGlobal.HandleComException(COMException comEx)

    at Microsoft.SharePoint.Library.SPRequest.GetFileAsStream(String bstrUrl, String bstrWebRelativeUrl, Boolean bHonorLevel, Byte iLevel, OpenBinaryFlags grfob, String bstrEtagNotMatch, Object punkSPFileMgr, Boolean bHonorCustomIrm, IrmProtectionParams fileIrmSettings, UInt32& pdwVirusCheckStatus, String& pVirusCheckMessage, String& pEtagNew, String& pContentTagNew, SPFileInfo& pFileProps)

    at Microsoft.SharePoint.SPFile.GetFileStream(SPWeb web, String fileUrl, Boolean honorLevel, SPFileLevel level, OpenBinaryFlags openOptions, String etagNotMatch, SPFileStreamManager spMgr, SPFileRightsManagementSettings rightsManagementSettings, Boolean throwOnVirusFound, SPVirusCheckStatus& virusCheckStatus, String& virusCheckMessage, String& etagNew, String& contentTagNew, SPFileInfo& fileprops)

    at Microsoft.SharePoint.SPFile.GetFileStream(OpenBinaryFlags openOptions, String etagNotMatch, String& etagNew, String& contentTagNew)

    at Microsoft.SharePoint.SPFile.OpenBinaryStream(SPOpenBinaryOptions openOptions, String etagNotMatch, String& etagNew)

    at Microsoft.SharePoint.SPFile.OpenBinaryStream(SPOpenBinaryOptions openOptions)

    at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.FetchStreamInternal()     — End of inner exception stack trace —

    at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.FetchStreamInternal()

    at Microsoft.Office.Excel.Server.MossHost.MossHostFileLoader.FetchStream(IIdentity currentIdentity)

    at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.FetchStream()     — End of inner exception stack trace —

    at Microsoft.Office.Excel.Server.CalculationServer.SharePointFileLoader.FetchStream()

    at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbookManager.CacheStream(CachedFile cachedFile, FileLoader loader)

    at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbook.Microsoft.Office.Excel.Server.CalculationServer.ICachedFileProvider.SaveFile(CachedFile cachedFile)

    at Microsoft.Office.Excel.Server.CalculationServer.CachedFile.SaveFile(ICachedFileProvider fileProvider)

    at Microsoft.Office.Excel.Server.CalculationServer.LocalDiskCache.ReserveCachedFileAndMarkUsed(FileId fileId, ICachedFileProvider fileProvider, Boolean replaceExistingFile)

    at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbook.ReserveCachedFileAndMarkUsed(FileLoader fileLoader, SessionId previousSessionId)

    at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbook.<FileStreamJob>b__e(IssueLoadThreadArg arg)

    at Microsoft.Office.Excel.Server.CalculationServer.BaseWorkbook.ExecuteWorkbookJob(WorkbookJobDelegate workbookJob, Object param)     24e5bd9c-6708-8070-314e-c1443430d9e0 

 

Looking at the Excel Services file cache for a working request we were seeing this.

 

but for a non working request we were seeing this.

 

the only commonality seemed to be that the non-working web applications were using RBS for BLOB storage.

Digging through the ULS logs some I finally found the following error.

 

System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object ‘rbs_fn_get_blob_reference’

A quick google turned up some blogs about database permissions for service accounts, following this blog http://blog.sharepointsite.co.uk/2014/01/sp-2013-ssrs-failing-after-rbs-enabled.html

I added the db_rbs_admin database role to the Excel Services service account for each RBS enabled database and Excel services sprang into life.

SharePoint 2013 SSRS Migration “User cannot be found”

A recent project I worked on involved a SharePoint 2010 to 2013 migration, part of this migration was to migrate an SSRS 2012 install from SP2010 to SP2013, the steps are well documented in MSDN http://msdn.microsoft.com/en-gb/library/hh759331.aspx

As part of the migration process we moved from classic to claims authentication, this is a straight forward 2 step process

1 ) Create a web app in classic mode in the 2013 farm and mount the content database from SP2010.

 

2) Call the Convert-SPWebApplication cmdlet to migrate the user accounts to claims authentication.

To perform the SSRS migration we installed SSRS 2012 SP1, moved across the reporting database and Encryption key, when testing the reports a few worked but lots were showing the error “User Cannot be Found” error as outlined in this blog post.

http://chanakyajayabalan.wordpress.com/2011/06/17/ssrs-report-server-has-encountered-a-sharepoint-error-user-cannot-be-found/

The post above suggests that a user has been deleted from the content db, we checked this but no accounts were marked as deleted.

The next step was to run a SQL trace while opening the reports and see what is being called.

The site ID was DDDC5D75-A52C-4FD9-8DE9-405246F54C4A, in the trace file I found the following

proc_SecGetPrincipalById @SiteId=’DDDC5D75-A52C-4FD9-8DE9-405246F54C4A’,@PrincipalId=9,@GetSTSToken=0,@GetExternalToken=0,@RequestGuid=’0873A39C-9913-7078-5AE6-AF4064C5D2B6′

 

proc_SecGetPrincipalById @SiteId=’DDDC5D75-A52C-4FD9-8DE9-405246F54C4A’,@PrincipalId=10,@GetSTSToken=0,@GetExternalToken=0,@RequestGuid=’0873A39C-9913-7078-5AE6-AF4064C5D2B6′

 

Checking back in the content db UserInfo table both ID’s were for valid users but Id 9 was for a migrated user (an account that starts i:0#.w|domain\username) however Id 10 was a non-migrated user account (an account in the format domain\username) .

Checking the account for Id 10 there was no matching entry in AD, I can only assume this user account had been previously deleted from AD but not removed from SharePoint, hence the “User cannot be found” error.

Some more searching turned up this MS support article http://support.microsoft.com/kb/2781008 describing the same issue but different circumstances, the fix is rolled into SQL Server 2012 SP1 CU2, installing this patch fixed the issue for us and we could open all the reports again.

SharePoint 2010 Designer Issues

While it is always good to work with the latest and greatest versions of everything, there are still solutions running on 2010 that need maintenance.

So recently I found myself having to update a SharePoint 2010 solution that was made up of an SPD2010 workflow, a content type and library template. I had to update the content type, workflow and library template with some new and updated columns.

The library template was using a custom Edit form with some of the  content type fields hidden, so I had to update the Edit form to show some new fields. To add a new field to a form you would normally edit the form table to and new rows where needed then select the field from the data source and select as ‘Insert as List Form Field’, unfortunately this option was no available; updating an edited form seems to be a common problem with SPD2010, as the same issue is outlined here.

http://social.msdn.microsoft.com/Forums/sharepoint/en-US/ac032a6b-5195-4ecf-a0ab-6e0117fd1a77/insert-as-list-form-field-not-available-on-data-source-details-panel-when-editing-editformaspx?forum=sharepointcustomizationprevious

My solution was as follows:

Select a field in the form where I wanted the new field and insert a new row into the form table, copy an existing control and insert into the new row, using the Common FormField Tasks control select the new control from the Data Field picker

 

Now Pick ‘List Form Field’ from the Format as  picker

 

In the Quick Tag Selector bar the new field will be called <SharePoint:FormField#FormField1>

From the Quick Control select Edit Tag…

The Tag code is now displayed with the ID value of  “FormField1”

Now check the other fields and find the control with the highest ID value and set the new tag value the be higher in the format “ff<id>{$Pos}”, ensure both references to the ID value are set

If the ID numbering is incorrect a duplicate

Once completed the new control will be available on the form.

SharePoint 2013, Nintex 2013 Workflow

Recently I have been working with the various Nintex products to create a few demos and prototypes.

Recently while working with a new VM recently I noticed that some of the actions were not performing correctly and the message templates were blank from site to farm level.

None of the normal things seemed to help (IIS Reset, Reboot, Deactivate / Reactivate, Reinstall), until I came across this post on the Nintex forum.

It turned out my VM had the currently withdrawn SharePoint 20103 SP1 applied. As soon I installed the fix normal Nintex service was resumed.

Opening emails in SharePoint

As part of a recent project we used SharePoint to store emails, both via incoming emails and one of the 3rd party drag and drop tools.

Most users were happy with the solution once we had updated the MIME types to allow emails to be opened rather then downloaded, but some still mentioned they would rather that email opened directly from SharePoint rather than seeing the yellow download bar that Internet Explorer puts up.

A bit of digging around on the internet came up with the solution from this Microsoft KB article

http://support.microsoft.com/kb/2678934

To enable emails to be opened directly from SharePoint the trick is to disable the Internet Explorer download bar for email file types, to do this add the following registry key to your local machine.

HKEY_CURRENT_USER\Software\Microsoft\Windows\Shell\AttachmentExecute\{0002DF01-0000-0000-C000-000000000046}

then run the assoc command below to get your .msg file extension type (mine is for Office 2013)

Now add that file type as a zero length binary value to the key created above.

Emails will open directly from SharePoint.