Friday, March 4, 2016

How to generate pfx certificate using cer certificate?

When we request SharePoint server to server trust certificates from the company, we are receiving the following two files.

Mycert.cer
Mycert.key

However, we do not receive the pfx file that required for SharePoint servers. We has been using different ways to construct the pfx file and I would like to summarize here in order for me to refer in the future.

There are different situations that we need to generate pfx certificate. The way to generate it will depends and I’ll show two different ways.

The first situation is you have received both cer and kay file, you have to generate the pfx file from scratch. You could use the following command to generate the pfx file. You MUST put the key file with same name as cer file in the same directory as described here.

certutil -MergePFX Mycert.cer Mycert.pfx

The second situation is you have pfx file already imported to IIS but accidentally deleted. Now you only have the cer file but NO key file. Here is the way to generate the pfx file.

Upload the cer file to IIS as described in Microsoft support blog. Run the following command to restore the pfx file.

certutil -repairstore my "SerialNumber"

SerialNumber is the serial number that you find for the cer file uploaded.


Now you have the cert file for SharePoint server to use.

Friday, January 29, 2016

How to resolve SharePoint list display issue after applying January 2016 Microsoft Critical Security Patch MS16-004?

After applying January 2016  Microsoft Critical  Security Patch MS16-004 on January 12, we have run into SharePoint issue that some lists could not be displayed. The error message is “Unable to get property ‘replace’. See the screenshots.



After working with Microsoft support, we have identified two solutions.

1. The initial solution is to install the full SharePoint Server 2013 Jan 2016 CU and running configuration wizard. This cause significant concern on the server down time and regression testing. Some company may not have this option since they are on older version or SharePoint that could not be upgraded to this CU directly.  Because we do not have time to install full SharePoint Server 2013 Jan 2016 CU, we continue worked with Microsoft on any fixes.

2. On January 15, Microsoft published the official SEE communication on this issue and the fix.  “If you are looking for a minimal change to resolve the issue it you can just install the following fix which contains the missing msp file containing the localized files:

KB 3114508Download location: https://www.microsoft.com/en-us/download/details.aspx?id=50667 “. We are able to apply the fix and fix the list display issue.


In the past three years, we run into issues almost every time we apply monthly security patch that contains SharePoint features. Sometimes even security patch without SharePoint patch may cause issues. The previous issues we run into were August s4ecurity patch for the following two issues.



We were able to apply a workaround for issue #2 but are waiting the fix for issue #1 in Oct. 2015 CU. However, August, Oct, Nov, Dec 2015 CUs breaks the hybrid search.


We will track the SharePoint issues caused by monthly security patch closely in the future.

Tuesday, November 10, 2015

Strike through SharePoint 2013 document item based on its status using JS Link

We have a requirement to strike through SharePoint 2013 document item based on its status "Obsolete". If the obsolete is "yes", the column named "Part Number" should be strike through and highlighted as red as in the below screenshot.


The easiest way to implement this is to use SharePoint 2013 new feature JS Link. There are several good JS Link references to highlight a SharePoint list row and color list item with different color. You could refer my previous blog to configure the JS Link. If you add the following javascript as JS Link named Obsolete.js to the display web part, you will get the desired result.

(function () { 

    // Create object that have the context information about the field that we want to change it's output render  
    var priorityFiledContext = {}; 
    priorityFiledContext.Templates = {}; 
    priorityFiledContext.Templates.Fields = { 
        // Apply the new rendering for Obsolate field on List View 
        "Part_x0020_Number": { "View": obsolateFiledTemplate } 
    }; 

    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(priorityFiledContext); 

})(); 

// This function provides the rendering logic for list view 
function obsolateFiledTemplate(ctx) { 

    var title = ctx.CurrentItem[ctx.CurrentFieldSchema.Name]; 

var obsolate = ctx.CurrentItem.Obsolate;
console.log(obsolate);

      // Return html element with appropriate color based on priority value 
if (obsolate == "Yes")
{
return "<span style='color :#f00'>" + "<del>" + title + "</del>" + "</span>"; 
}
else{
return "<span style='color :#000000'>" + title + "</span>"; 
}


The following script will highlight the item to red when obsolete value is "Yes".


(function () {
    var overrideCtx = {};
    overrideCtx.Templates = {};
    overrideCtx.OnPostRender = {
        HighlightRowOverride
    };
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);
})();

function HighlightRowOverride(ctx) {

for (var i=0; i< ctx.ListData.Row.length; ++i){

var lid = GenerateIIDFromListItem(ctx, listItem);
var row = document.getElementById(iid);

if (listItem.Obsolate == "Yes") {
row.style.backgroundColor = "rgba(255, 0, 0, 0.5)";
}
}

ctx.skipNextAnimation = true;

}

You will see this implementation is very flexible and we could use this for many other different implementations.

If you need to strike through the Document Name field, you would look at the field using view source. Here is the field definition.

{"Name": LinkFilename",
"FieldType": "Computed",
"RealFieldName": "FileLeafRef",
"DisplayName": "Name",
"ID": "5cc6dc79-3710-4374-b433-61cb4a686c12",
"ClassInfo": "Menu",
"Type": "Computed",
"Filterable": "FALSE",
"listItemMenu": "TRUE",
"CalloutMenu": "TRUE",
"AllowGridEditing": "TRUE"}


You can change the two line of the javascript.

    priorityFiledContext.Templates.Fields = {
        "LinkFilename": { "View": obsolateFiledTemplate }
    };

 var title = ctx.CurrentItem.FileLeafRef;

Please note that you could use powershell or server side API to automatically set the JSLink to the webpart if there are many lists or libraries you need to configure.

Friday, November 6, 2015

Use JSLink to auto populate the SharePoint item field without server side code like custom field

Recently we have a requirement to automatically generate a unique number string for one field called “Part Number” for many SharePoint Document libraries. This unique number string would need to follow some business logic like below.



There are several ways we could implement this. One of the old school method is to implement the field as custom field with business logic to generate unique number string. However, we had several custom fields on SharePoint that caused SharePoint upgrade complexity. In this blog, we will use a new client side implementation JSLink that will not have any server side component. In this way, the solution will be potable and the upgrade will be extremely simple.

The first step is to write a JSLink script named PartNum.js to replace the out of box “Part Number” text field behavior. You would need to find out the field internal name using the way published here. The internal field name for “Part Number” is “Part_x0020_Number“ in our case.  The script will look like below. You would need to update the guid() function with real business logic.

(function () {
    var overrideCtx = {};
    overrideCtx.Templates = {};
    overrideCtx.Templates.Fields = {
        'Part_x0020_Number': { 'NewForm': renderTitle, 'EditForm': renderTitle }
    };
    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);
})();

function renderTitle(ctx) {

var formCtx = SPClientTemplates.Utility.GetFormContextForCurrentField(ctx);

RegisterCallBacks(formCtx);

        var fieldVal = ctx.CurrentFieldValue ? ctx.CurrentFieldValue.toString() : null;
       
        if(fieldVal) { 
var html = '<div>';
html += '<input id="uniquePartNum" type="text" value=' + fieldVal + '>';
html += '</input>';
html += '</div>';
return html;
}
else{
var newGUID = guid();
var html = '<div>';
html += '<input id="uniquePartNum" type="text" value=' + newGUID + '>';
html += '</input>';
html += '</div>';
return html;  
}
}

//registers call back functions from SharePoint
function RegisterCallBacks(formCtx) {
                //This is what happens when the user clicks save\submit
                formCtx.registerGetValueCallback(formCtx.fieldName, function () {
                                //get the choosen value from the select element
                                var e = document.getElementById("uniquePartNum");
                                return  e.value;
                });
}

// Generate GUID
function guid() {
  function s4() {
    return Math.floor((1 + Math.random()) * 0x10000)
      .toString(16)
      .substring(1);
  }
  return s4() + s4() + '-' + s4() + '-' + s4() + '-' +
    s4() + '-' + s4() + s4() + s4();
}

The second step is to open SharePoint Designer and go to your SiteAssets library, drop PartNum.js there.

The third step is to configure the document item edit form to use this script. Go to your document library and from your library settings ribbon choose ‘Form Web Parts > Default Edit Form’. Edit the webpart. Then add the script to the JS Link field.






Now if you save the changes, you will see the “Part Number” field will be automatically populated with GUID. You could modify the script to generate the string based on your business requirement.

Since Microsoft have changed the development direction for SharePoint 2013 and 2016, you should always think about SharePoint add-in approach instead of server side solution. There are more samples from Microsoft you could refer here.

Friday, October 2, 2015

Debug SharePoint JavaScript issue with web services

If you are using JavaScript calling web services on SharePoint site,  you might see web pages as “Loading…” without the results. The following site has three ContentQuery webpart and all displayed as “Loading…”.



The quick way to debug this is to use Chrome and click More Tools -> Developer Tools. Click console and you might be able to find out the “500” exception as below.



Another quick way to what exactly is the issue, you could call the web service like http://spsbx08/sites/DocCenter/_vti_bin/client.svc in this case to see the real error.

Memory gates checking failed because the free memory (214859776 bytes) is less than 5% of total memory. As a result, the service will not be available for incoming requests. To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element.

In this case, the issue is the search services are taking too much memory on the server. We could restrict the service not to take too much memory or bounce the server as short term solution. 

Friday, September 25, 2015

How to resolve issue SharePoint OOTB 2010 approval workflow does not create tasks for everyone when large AD list used for the approvals

We have one SharePoint 2013 list that has an out of box SharePoint 2010  approval workflow running. The workflow has an AD group as approval. The AD group has 66 users across five different AD domains. The workflow was running fine for years until recently. Now it did not create all tasks for 66 everyone. Instead it only created 62 tasks and four tasks were missing. In addition, here are some other interesting findings. 
  • If we add all 66 users individually to the workflow approval field, all tasks created for everyone
  • If we separate the large AD group to three AD groups evenly, and add the three AD groups to the workflow approval field, all tasks created for everyone
  • We have different larger AD group with 300 people and it create all 300 tasks for everyone
  • Many workflows with different large AD group as approval have similar issues
  • Some users do not have proper display names but have userID as display name
  • We have another group with 105 users and it only create 102 tasks as in the following screenshot



There three different exceptions in ULS logs for workflows.


1. The first one is transaction timeout.
“Error in persisting workflow: System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.TimeoutException: Transaction Timeout     -
-- End of inner exception stack trace ---   
 at System.Transactions.TransactionStateAborted.CreateAbortingClone(InternalTransaction tx)   
 at System.Transactions.DependentTransaction..ctor(IsolationLevel isoLevel, InternalTransaction internalTransaction, Boolean blocking)   
 at System.Transactions.Transaction.DependentClone(DependentCloneOption cloneOption)   
 at System.Transactions.TransactionScope.SetCurrent(Transaction newCurrent)   
 at System.Transactions.TransactionScope..ctor(TransactionScopeOption scopeOption)   
 at Microsoft.SharePoint.Workflow.SPWinOePersistenceService.Commit(Transaction transaction, ICollection items)”


2. The second is group cannot be found.
SharePoint Foundation General 8vid Verbose Could not initialize group data from https://sharepointdev.mycompany.com/mydepart/Finance/SalesCert: Microsoft.SharePoint.SPException: Group cannot be found. c7a11c9d-0430-e0c9-8fa7-25092a0dc879

3. The third one is some users is treated as window account not claims.
SharePoint Foundation Security ahluw Verbose Entering: GetByLoginNoThrow(user1@qca.mycompany.com) c7a11c9d-0430-e0c9-8fa7-25092a0dc879
SharePoint Foundation Security ahluz Verbose Exiting: GetByLoginNoThrow(user1@qca.mycompany.com) c7a11c9d-0430-e0c9-8fa7-25092a0dc879

Based on the three exceptions, we tried to increase the workflow time out and the SharePoint timeout, however, the issue was not resolved. We also tried to create same workflow in different SharePoint farm, different web application, different site, with different AD groups, different number of users in the AD group, and different Domain controller. The workflow failed inconsistently.  

Finally we suspect there might be a issue to get ALL the users from the group during the workflow execution. We had seen SharePoint people picker performance issue before on SharePoint 2007. As a result, we set the SharePoint people picker AD search property to help the AD query. The syntax is as below. 

stsadm -o setproperty -pn peoplepicker-searchadforests -pv "forest:corp.mycompany.com;domain:na. mycompany.com;domain:ap. mycompany.com;domain:sa. mycompany.com;domain:mea. mycompany.com;domain:eu. mycompany.com" -url https://webapp.mycompany.com

You could verify the configuration using the following command.
stsadm -o getproperty -pn peoplepicker-searchadforests –url https://webapp.mycompany.com

After this configuration, all the workflows are able to create all tasks for every approval! 




Thursday, September 24, 2015

Access excel workbook content through excel REST API on SharePoint 2013

The Excel Services REST API is a new feature of Excel Services that enables you to access Microsoft Excel workbook data by using a uniform resource locator (URL) address. Using the REST API, you can retrieve resources such as ranges, charts, tables, and PivotTables from workbooks stored on SharePoint Server 2013.

The REST API provides a flexible way to use Excel data. In addition to directly linking to Excel resources using the REST API, you can also make basic HTTP calls programmatically to use Excel data within external programs. In the REST API, code is portable. There are no assemblies to distribute, no additional references are required in your project, and you do not have another object model to learn. As long as you understand the URL convention for accessing resources, you only have to build the URL and then call it with a standard HttpWebRequest object. As a result you could use REST API though HTTP, you could call it through any program like PHP, Perl, Java, C#, and javascript.

In this blog, I’ll use a simple C# console application example to use the Excel Services REST API return all data on the PivotTable. It demonstrates a basic pattern for retrieving resources from an Excel workbook on SharePoint Server 2013 using the REST API. The code will use the user name and password to authenticate to SharePoint. The code example retrieves a PivotTable named " ProjectData" from a workbook named "QMET - Project Data.xlsx" that is stored in a Reports library named "Reports" in a ProjectBICenter.

The key to using the REST API is in understanding how to construct the correct URL to a resource. A REST URL in Excel Services consists of three required parts and one optional part.
  • The first part is the location of the ExcelRest.aspx page. ExcelRest.aspx is the entry point to the Excel Services REST API.
  • The second part is the workbook location relative to the ExcelRest.aspx page.
  • The third part is the path of the requested resource inside the workbook. As an example for the excel book named "QMET - Project Data.xlsx".
  • The forth part is the format you would like to return. The valid formats are html, atom, and json.

This is example of the the REST URL to get PivotTable data as json format.
https://webapp.mycompany.com/projectsite/ProjectBICenter/_vti_bin/ExcelRest.aspx/Sample%20Reports/QMET%20-%20Project%20Data.xlsx/Model/PivotTables('ProjectData')?$format=json

You could directly use the browser to test the URL listed above with different formats and validate the data. Then the following C# code you could use as example to port to other different languages.

using System;
using System.Net.Http;
using System.IO;
using System.Net;

namespace RestExcelClient
{
    class Program
    {
        static void Main(string[] args)
        {
            // REST to get all data from PivotTable named ProjectData
            string jsonRequest = "https://webapp.mycompany.com/projectsite/ProjectBICenter/_vti_bin/ExcelRest.aspx/Sample%20Reports/QMET%20-%20Project%20Data.xlsx/Model/PivotTables('ProjectData')?$format=json";

            // Set up HttpWebRequest call to use local user name and password
            CredentialCache credCache = new CredentialCache();

           // If run on window, you could use the login user account – uncomment the below line
           //credCache.Add(new Uri(jsonRequest), "NTLM", CredentialCache.DefaultNetworkCredentials);

            credCache.Add(new Uri(jsonRequest), "NTLM", new NetworkCredential("username", "passeord", "domain.mycompany.com"));
            HttpWebRequest spRequest = (HttpWebRequest)HttpWebRequest.Create(jsonRequest);
            spRequest.Credentials = credCache;
            spRequest.UserAgent = "Mozilla/4.0+(compatible;+MSIE+5.01;+Windows+NT+5.0";
            spRequest.Method = "GET";
            spRequest.Accept = "application/json;odata=verbose";
            HttpWebResponse endpointResponse = (HttpWebResponse)spRequest.GetResponse();

            try
            {
                // Get HttpWebResponse with Json data as string
                Stream webStream = endpointResponse.GetResponseStream();
                StreamReader responseReader = new StreamReader(webStream);
                string response = responseReader.ReadToEnd();

                Console.WriteLine(response);

                // Next task is to parse the json string result
                //DataContractJsonSerializer jsonSerializer = new DataContractJsonSerializer(typeof(Response));
                //object objResponse = jsonSerializer.ReadObject(response.GetResponseStream());
                responseReader.Close();

            }
            catch (Exception e)
            {
                Console.Out.WriteLine(e.Message);
            }      

        }


    }

}


There are several key configurations we would need to point out here.  
  1. Add the Excel Services Application Trusted Data Connection Libraries
  2. Add Excel Services Application Trusted File Locations 
  3. Make sure the security token app user has access  to the external data
  4. Enable the "Data refresh from REST enabled." for the Excel Services Application Trusted File as described in this blog

You might need to parse the result json data as described here. You could use the handy online json viewer to view the json returns.