Thursday, January 19, 2017

SharePoint 2013 PowerPivot workbooks–Resolving error “We cannot locate a server to load the workbook data model”

I have a collection of PowerPivot Excel workbooks in a SharePoint 2013 farm. I had configured everything including a SQL Server Analysis Services (SSAS) server in PowerPivot mode and SQL Server PowerPivot Service Application.

Suddenly filters and sliders in my all PowerPivot reports stopped working throwing following error message.

          1

Then I checked ULS logs, which showed me the cause of the error

“Uncaught CLR exception crossing the Interop boundary: Microsoft.AnalysisServices.Streaming.ServerNotFoundException: There are no servers available or actively being initialized.   
at Microsoft.AnalysisServices.Streaming.OnPremise.ServerPool.Microsoft.AnalysisServices.Streaming.OnPremise.IServerPool.GetAvailableServers()”
   

That means, the Excel Services cannot locate my SSAS server. Following are the diagnostics I did

  • Check if the SSAS server is up – Success
  • Ping the SSAS server from SharePoint serve and vice versa – Success
  • Check if my SSAS PowerPivot instance is listed in Excel Services –> Data Model settings – Success
  • Check if relevant services are running from SQL Server Configuration Manager – Success
  • Check if firewall is enabled or ports are blocked – Gotcha !!

My domain firewall is enabled after a restart. Initially I kept it disabled to make things simple. But it seams that I need a concrete solution.

Following are the steps I used to open required ports

1. What are the ports need to be open?

As a practice I will open 2383, 2382 ports for a SSAS server. Since we have a named instance <SSAS Server>/PowerPivot, we need to open the custom port used by that instance.

2. How to find custom port for named SSAS instance

  • Navigate to “C:\Program Files (x86)\Microsoft SQL Server\90\Shared\ASConfig”
  • Open msmdredir.ini in text editor
  • Get the port specified in that file

         image

3. Configure a firewall rule

  • Open Windows Firewall with Advanced Security
  • Create a new Inbound rule
  • Specify following ports to open

           image

That fixed the issue.

Tuesday, September 27, 2016

Presentation – Data Loss Prevention in SharePoint 2016

We had the September 2016 meetup of the SharePoint Sri Lanka forum in 14th of September. I did a session on Data Loss Prevention (DLP) in SharePoint, which is a new feature of SharePoint 2016.

14355167_10154467911322482_5256737244406549925_n

Following is the presentation I did

Thursday, September 1, 2016

SharePoint JSOM : Alternative approach to nested executeQueryAsync with loops

Let’s assume that we need to get all pages in a site. Following are the steps we have to perform

  • Get all libraries
  • Get items in each library

Traditionally we would write a code like the below, but it will not provide expected response. This is due to the asynchronous nature of operations

Code that does not work

var context = SP.ClientContext.get_current();
var hostContext = new SP.AppContextSite(context, decodeURIComponent(getQueryStringParameter("SPHostUrl")));

var web = hostContext.get_web();
var lists = web.get_lists();
context.load(lists);

context.executeQueryAsync(
function () {
    var listEnumerator = lists.getEnumerator();
    while (listEnumerator.moveNext()) {
        var oList = listEnumerator.get_current();
        if (oList.get_baseType() === 1) {

            var camlQuery = new SP.CamlQuery();
            var items = list.getItems(camlQuery);
            context.load(items);
            context.executeQueryAsync(
                function () {
                    var listEnumerator = lists.getEnumerator();

                }, function (sender, args) {
                    console.log('error!');
                });
        }
    }
}, function (sender, args) {
    console.log('error!');
});

As the solution, I broke the functionality in to two methods using Deferred objects. I’ve written a blogpost on Deferred objects and you can find it from here. Following is the preferred approach.

Working code with Deferred objects

function getSitePages()
{
    var context = SP.ClientContext.get_current();
    var hostContext = new SP.AppContextSite(context, decodeURIComponent(getQueryStringParameter("SPHostUrl")));

    var web = hostContext.get_web();
    var lists = web.get_lists();
    context.load(lists);

    context.executeQueryAsync(
    function () {
        var listEnumerator = lists.getEnumerator();
        while (listEnumerator.moveNext()) {
            var oList = listEnumerator.get_current();
            if (oList.get_baseType() === 1) {

                var promise = getListItems(oList).then(function (state) {
                    console.log(state);
                });

            }

        }
    }, function (sender, args) {
        console.log('error!');
    });
}

function getListItems(list) {
    var dfd = $.Deferred();

    var context = SP.ClientContext.get_current();
    var hostContext = new SP.AppContextSite(context, decodeURIComponent(getQueryStringParameter("SPHostUrl")));
           
    var camlQuery = new SP.CamlQuery();
    camlQuery.ViewFields = "<FieldRef Name='FileExtension' /><FieldRef Name='Title' />";
    camlQuery.set_viewXml("<View><Query><Where><Or><Contains><FieldRef Name='FileLeafRef' /><Value Type='Text'>.aspx</Value></Contains><Contains><FieldRef Name='FileLeafRef' /><Value Type='Text'>.html</Value></Contains></Or></Where></Query></View>");
    var items = list.getItems(camlQuery);
    context.load(items);

    context.executeQueryAsync(
        function () {
            var itemsCount = items.get_count();
            if (itemsCount > 0) {
                obj = {};
                obj.ListName = list.get_title();
                obj.ListPages = [];

                for (var i = 0; i < itemsCount; i++) {
                    var item = items.itemAt(i);
                    obj.ListPages.push(item.get_item('FileRef'));
                }

                dfd.resolve(obj);
            }
                   
        }, function (sender, args) {
            dfd.reject(sender, args, errorMsg);
        });

    return dfd.promise();
}

Thursday, August 25, 2016

Resolving SharePoint PowerPivot Service error–“We're no longer connected to the database and can't refresh your connection. please close and reopen the workbook and refresh it”

One of my SharePoint environment had all SharePoint related services configured in one server (Single server topology). PowerPivot service application was one of them. Due to various reasons the Server had very limited resources.

Due to resource limitation, I was getting the above issue repeatedly. Until I upgrade the server I performed following actions to resolve the issue.

1. Restart PowerPivot instance in my SQL Server

image

2. Navigate to Manage Service Applications > Excel Services Application > Data Model Settings, and check the PowerPivot instance is available

image

3. Navigate to “Service on this Server” and restart “Excel Calculation Services” service

image

4. Navigate to “Service on this Server” and restart “SQL Server PowerPivot System Service ” service

image

5. Check the firewall and the network connectivity between SharePoint server and PowerPivot SQL instance

Wednesday, August 17, 2016

Collab365 - SharePoint Framework the future of SharePoint development

The largest online conference for SharePoint, Office365 and Azure is just around the corner. I will be speaking on the topic of “SharePoint Framework the future of SharePoint development

600x200_speakerbadge_GC

You can register by navigation to this site

SharePoint Framework

SharePoint Framework, a page and part model that enables fully supported client-side development, easy integration with the Microsoft Graph and support for open source tooling. This will be the trend for any SharePoint customization. In this session I will describe the SharePoint framework and walkthrough a practicel sample which uses AngularJS and Kendo UI. Furthermore I will talk about the approach we take to migrate current SharePoint solutions to the SharePoint Framework

In this session I will try to talk about the following

  • SharePoint Framework in general
  • Advantages of SharePoint Framework
  • How to migrate current projects
  • SharePoint Framework with AngularJS
  • SharePoint Framework with Kendo UI
  • Client side / OpenSource tooling
  • New solution deployment and release mangement
  • Demonstrations

Sunday, July 17, 2016

Disaster Recovery for SharePoint farms in Microsoft Azure IaaS using Azure Recovery Services

I had to implement Disaster Recovery (DR) strategy to one of my SharePoint farms deployed in Microsoft Azure. I used Azure Recovery Services to achieve the goal in addition to SharePoint site collection backups.

Following are the steps I used to create a recovery vault in my Microsoft Azure environment to protect my Virtual Machines

1. Look for Recovery Services vaults in Azure portal

image

2. Create a new vault

image

3. Select if you need locally redundant or geo-redundant storage. I selected locally redundant storage as my budget was limited

image

4. In this section we are creating a new Backup policy for Virtual Machines

image

image

5. In backup policy, specify required retention

image

6. Add virtual machines to the backup policy

image

7. Click on backup jobs to see the current status

image

8. We can see the backup configuration task has completed

image

9. But that’s not all. We have to execute the initial backup. Click on Azure Virtual Machines node to navigate to check current status

image

image

10. We have to select one Virtual Machine at a time to execute the initial backup

image

image

11. Once we run the initial backup job for each and every Virtual Machine in our SharePoint farm, we can see a result similar to the following

image

12. Once it is backed up according to the schedule, we can check the latest restore point as shown below

image

image

That is the easiest way to implement disaster recovery for a SharePoint farm hosted in Microsoft Azure.

Sunday, July 10, 2016

SharePoint 2013 in Azure IaaS–Map public url to internal DNS

One of my SharePoint environments was recently deployed in Microsoft Azure IaaS. I have following servers in that environment

  • Active Directory
  • SQL Server
  • Analysis Services Server
  • SharePoint Server

I have deployed the farm accordingly and created host named site collection using public URL “portal.contoso.com”.

I used following command to create the host based site collection

New-SPSite 'https://portal.contoso.com' -HostHeaderWebApplication 'https://portal.contoso.com' -Name 'Portal' -Description 'Portal' -OwnerAlias 'SP\SP_Admin' -language 1033 -Template 'BLANKINTERNETCONTAINER#0'

Then I mapped the public IP of the SharePoint server with my external DNS. Thereafter my SharePoint site is accessible from outside as below

But the public URL was not accessible within my server farm (e.g SharePoint server, or Analysis Services Server), And I need to have a more concrete solution than host files.

Following are the steps I used

1. Log in to domain controller and navigate to DNS Manager. Right click on Forward Lookup Zones and create new

image

 

 

 

 

 

 

image

image

image

image

2. Create new A record to SharePoint server

image

image

3. Test the connectivity

First clear the DNS cache

  • ipconfig /flushdns

image

That’s all we have to do. In the same way I had to map public URLs of my analysis services as well.