How to Connect QlikSense to a 2013 SharePoint List

[wdchristensen]

** Prerequisite – Install Qlik Sense REST Connector **

1) Get guid from SharePoint list (Maybe your SP Administrator can provide this?)

•    Navigate to the aspx page that contains the list:

     Example page: http://sharepoint/company/home/folder/project/new/newer/_formats/001/default.aspx

• Click on the Site Contents link

• Click on Settings

• Doesn’t matter if you actually have permission to the object the Access Denied page has the information you need! Look at the URL and search the string from RIGHT to LEFT and find the first instance of the word “List” and copy everything from that point to the end of the URL. 

Example url: http://sharepoint/company/home/folder/project/new/newer/_formats/001/AccessDenied.aspx?Source=http%3...List%3DZY98XW76%252DVU54%252DTS32%252DRQ19%252DPO87NM65LK43

• The %3D in the url encoding just means the equal sign (=) so it can be ignored. Change all “%2D” to “-“ (I used notepad)

• Wrap the result in curly brackets and you have the List ID

Example GUID List ID = {ZY98XW76-VU54-TS32-RQ19-PO87NM65LK43}

2) How to view xml code of a list view

• Build URL: http://{0}/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={1}

          {0} – The URL to your site.  This could be the root web or a child site.

          {1} – The GUID for your list.

Example URL for viewing XML in browser:

http://sharepoint/company/home/folder/project/new/newer/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&Ro...{ZY98XW76%252DVU54%252DTS32%252DRQ19%252DPO87NM65LK43}

• Copy and paste the new url into your favorite browser and you should see a page of XLM. If you can't view the XML in a browser you won't be able to view it in QLIK so make sure you can see the information before continuing.

3) Open your QlikSense application and create a new connection using the "Qlik Rest Connector".

4) Create new rest connection.

• Enter the url you created in step 2.
• Set the authentication and create a meaningful name (cnSharePointExampe)
• Fill out the remaining options as follows and test the connection

5) You can now load data (similar to any other data load process)

[Anonymous]

I have the same issue. Has anyone come across a reoslution?

Thanks,

Zach

[wdchristensen]

I posted a related question which is still open:

Rest Connection with Windows Authentication qlik sense ENTERPRISE feb 2018

I ended up using a service account. Might be able to do something like:

https://help.qlik.com/en-US/sense/February2018/Subsystems/PlanningQlikSenseDeployments/Content/Deplo...

[skyline01]

Hi William,

I am having the same issue with REST in Qlik Sense Enterprise February 2018 relative to 3.2.  You noted above that you ended up using a service account.  From your screenshot above, it appears to be an AD service account.  What about this AD service account that made the REST connection work relative to an AD user account?  My SharePoint admin is claiming that the issue has nothing to do with the different versions of Qlik Sense, but rather that REST does not support FBA.  I'm not so sure that this is really the cause, since Qlik Sense 3.2 supported REST connections via FBA.

Thanks,

Casey

[wdchristensen]

There are 2 different approaches to this problem:

The simple way, which is to create the connection with a shared service account (in my case an AD account) and you essentially hardcode the service account credentials in the creation of the connection for all those who can access it to share. This can create an issue with users who can access the same object but have different permissions. So for example, suppose you have 2 groups who can access the master database. The group db_owner can view all objects in a database where db_reader can view tables and views but can’t execute a stored procedure. If you create a shared connection for the 2 groups to share you will either grant db_reader too much access (execute sp) or you will restrict the access of db_owner so that they can no longer do anything other than read tables and views. This creates the potential for unauthorized access to objects where it will be exceedingly difficult to audit since all the logs will be associated with the service account activity.

IMHO, the ideal solution is to pass the AD credentials from the source application (Qlik Sense) to the destination application requiring authentication (SharePoint, SQL Server, Linked Server). It turns out that I had the “Kerberos Double Hop Problem” with a SQL Server Linked Server. Once I was able to get the destination server to accept pass through credentials from the Linked Server, my Qlik connection to the SQL Server seemed to pass the credentials to the database server where it previously didn’t accept the pass through credentials. This makes management of the connections much easier since all connections can essentially be public since they will use the credential of the logged in user. So management can be handled primarily in AD. I have not tried applying this solution to SharePoint at this time but it could possible be similar. If you get the pass through credentials to work for SharePoint, please post your solution since I know there are several people interested including me. Good luck! 

How to set up a Kerberos Authentication Scenario with SQL Server Linked Servers

Kerberos Configuration Manager for SQL Server

[Anonymous]

Thank you SO MUCH for this post !!!

You just saved me hours of work + removed dependency on Excel and Access uploads.

[baltazarj]

I also experience this issue.

I can successfully connect to XML file

But I encounter HTTP Protocol error 403 (FORBIDDEN): using all the Authentication Schema.

I am currently using Qlik Sense June 2019 desktop

[manojciti]

HI ,

Did you find any solution to this