Wednesday, May 10, 2017

Presentation - Build real-time applications using SharePoint, Azure Service Bus and SignalR

I did a session on real-time applications with SignalR and Azure service bus for SharePoint Saturday Colombo 2017. I explained the concept and different technologies we can employ to build rich and real-time applications. Later I explained the approach to host such applications within SharePoint.

I will do a separate blog post explaining the steps required to a host real-time web application within SharePoint.

image

Following is the presentation I did

 
We had a room full of active participants Smile. It was nice to see some familiar faces after a long time.
 
DSC_0019   DSC_0059
DSC_0077 
We had a productive panel discussion as well.
DSC_0111

Wednesday, April 12, 2017

Resolve SQL Server error “Cannot generate SSPI context” in a Kerberos environment

Recently I came across following error when I tried to create an ODBC connection to one of my SQL Servers using TCP/IP.

image

The SQL server belongs to a SharePoint farm which is configured with Kerberos authentication. The same SQL server was initially configured with local system account and later reconfigured with a domain account (e.g trs\SQL_Admin).

Logs showed that there is an issue with SPN records

Following are the steps I followed to resolve the issue

  • Check existing SPN records for the service account

setspn –l trs\sql_admin

It lists all SPN records registered. However it did not contain an entry for

mssqlsvc/TRS-AUS1-AS-01.trs.local:1433

  • Add missing SPN record using following command

setspn -a mssqlsvc/TRS-AUS1-AS-01.trs.local:1433 trs\sql_admin

Unfortunately I received an error saying that there is a duplicate record. Let’s investigate that

  • Execute following command to find out any SPN records for that particular server

setspn -l TRS-AUS1-AS-01

It had a record with same signature. May be it was created for my local service account when SQL server was initially configured

image

  • We need to remove those SPN entries. To remove them use following commands

SetSpn -d MSSQLSvc/TRS-AUS1-AS-01.TRS.local:1433 TRS-AUS1-AS-01
SetSpn -d MSSQLSvc/TRS-AUS1-AS-01.TRS.local TRS-AUS1-AS-01

  • Then we need to add new SPN entries with our service account

setspn -a mssqlsvc/TRS-AUS1-AS-01:1433 trs\sql_admin
setspn -a mssqlsvc/TRS-AUS1-AS-01.trs.local:1433 trs\sql_admin

  • You can verify the delegation through Active Directory Users and Computers

image

That’s all we have to do. Now I can create an ODBC connection using TCP/IP

Monday, March 20, 2017

Presentation - A deep dive on SharePoint Authentication

We discussed about different authentication options in SharePoint at SharePoint Sri Lanka forum on 8th of March 2016.

17022096_10154847202392530_5755041721635751879_n

Following is the presentation I did

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