QlikView / QMC to extract data from SP2013 with authentication

[#1]

HI,

I am facing difficulties when I schedule a task in the QMC.

I need to schedule a SharePoint list data extract, where the connection URL contains a service account and password.

The service account is part of the connection URL and can log into the SharePoint server and has the Read permission to pull the data.

In the QV server the manual reload working as expected.

In QMC the reload fails. Based on the QVW log: access denied.

Can somebody help and confirm if it is required to allow the service account assigned to the QMC to allow to logon to the SharePoint server as well?

Thanks in advance!

Bye

Rob

[markodonovan]

Hi Robert,

In the past I have had to query Sharepoint lists in Office 365.

I would execute the powershell script from within the loadscript as follows:

EXECUTE C:\Windows\System32\WindowsPowerShell\v1.0\powershell -File  $(powershellcmd);

I have attached a powershell script I used to do this in case the credentials code helps.

Probably a bit different for SP2013 though.

Thanks

Mark

http://techstuffybooks.com

======================================

$webUrl = "https://xxxx.sharepoint.com"

$username = "MYUSERNAME"

$plainpassword = "MYPASSWORD"

$password = "MYPASSWORD" | ConvertTo-SecureString -AsPlainText -Force

$sourceListName = "hireman_documents"

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($webUrl)

$ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)

[amit_saini]

Hi Robert,

Yes for loading Sharepoint data , service account should be configured.

Thanks,

AS

[#4]

HI AS,

Thanks for the quick reply.

The QV service account has been updated to include the relevant server name in the 'Allow logon to' parameter. Unfortunately the problem is still the same - reload working in QV application manually, but fails in the Management Console, with 'access is denied' error.

[#5]

@HI Mark,

thank you for sharing the script. I think it would be an option for me as a workaround.

My primary objective is to fix the current RPC connection URL (if needed) or figure out why the reload fails in Management Console only and works in QlikView server when I reload manually.

Here is a sample script

Process: 

LOAD ows_LinkTitle, ows_ID, ows_SVProcessDefinitionName, ows_SVProcessDuration, ows_SVProcessId, ows_SVProcessStatus, ows_SVSite, ows_SVUTCCompletionDate, ows_SVUTCCreationDate, ows_SVUTCDueDate, ows_SVUTCProcessDefinitionCreationDate, ows_SVWasForced, ows_Modified

FROM [http://seviceaccountID:password@sharepointsite/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&...{ListGUID}&View={ViewGUID}] (XmlSimple, Table is [xml/data/row])

WHERE ows_Modified >= (calculated date);

STORE Process INTO ..\..\QVDs\T1\Process.qvd;

DROP TABLE Process;

[#6]

How to resolve this?

[#7]

Hi Mo,

still no solution.

I use a workaround: I created a job in the server's task scheduler to reload the QVW daily.

I use this action: start a program

-program: "D:\Program Files\QlikView\Qv.exe"

-arguments: /r D:\Qlikview\Sandbox\APP_test\T1_DataExtracts\T1_Test_ExtractSpList.qvw

In a test environment it is more or less working, but in a production environment this workaround should not be used.

[#8]

Hi Robert,

Thanks for your suggestion.

For now I create a task schedule to refresh the excel which is export from sharepoint list.

Then QS to read the excel daily.

[#9]

QMC reload is something that I have had trouble with for months now . . . Qlikview couldn't assist with it either barring I pay their consultants an arm and a leg to look at my problem. My suggestion is to look at a command-line reload and schedule the .bat file to run through your windows task scheduler. Worked like a charm for me. Here is the best article that I found on the community for it:

Reload and save using command line

QlikView Maven: QlikView Command Line and Automation

Taylor