Question

sharepoint

perry
Badge +2

Hello FME-specialists,

 

I use FME(R) 2020.0.2.1 (20200511 - Build 20238 - WIN64)

I made a succesfull webconnection with SharePoint.

I tried to read the database of MSPROJECTS.

 

According the manager of MSPROJECTS my credentials has all the rights needed to access the msproject database (user and password are correct and user has sufficient rights).

 

The HTTPCALLER unfortunately gives errors: 'HTTP/1.1 403 Forbidden' from ...' (see attatchment)

QUESTIONS

  1. What goes wrong and how can I connect properly to MSPROJECTS with FME?

(I also tried to fill the clientID and the client-secret in my web connection but it resulted in no connection with sharepoint)

2 . Does FME V2020 HTTPCaller support SAML (SharePoint Online) authentication?

3. Do you have a working example HTTPCALLER with SharePoint?

 

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

WEB_CONNNECTION Misrosoft Sharepoint Online

I have made a web-connection with sharepoint.

URL: https://barorganisatie.sharepoint.com/.default" target="_blank">https://login.microsoftonline.com/common/oauth2/v2.0/authorize?scope=https://barorganisatie.sharepoint.com/.default offline_access&response_type=code&prompt=consent

This Microsoft-sharepoint web-connectie does work correct without errors.

WORKSPACE

So I made a workspace with a HTTPCALLER method GET:

ERRORS: Unfortunately I get the following errors.

https://barorganisatie.sharepoint.com/sites/pwa/_api/Projectdata

<?xml version="1.0" encoding="UTF-8"?>

<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><m:code>-2147024891, System.UnauthorizedAccessException</m:code><m:message xml:lang="en-US">Access denied. You do not have permission to perform this action or access this resource.</m:message></m:error>

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

LOGFILE:

2020-06-10 20:02:17| 0.6| 0.0|INFORM|Updating access token for 'piep0804 Microsoft SharePoint BAR-Organisatie'

2020-06-10 20:02:19| 0.6| 0.0|INFORM|HTTPCaller (HTTPFactory): HTTP/FTP Transfer: Downloading resource 'https://barorganisatie.sharepoint.com/sites/pwa/_api/Projectdata' to internal memory buffer

2020-06-10 20:02:19| 0.6| 0.0|INFORM|HTTPCaller (HTTPFactory): HTTP transfer summary - status code: 403, download size: '270 bytes', DNS lookup time: '0.124865 seconds', total transfer time: '0.578038 seconds', url: 'https://barorganisatie.sharepoint.com/sites/pwa/_api/Projectdata'

2020-06-10 20:02:19| 0.6| 0.0|ERROR |HTTPCaller (HTTPFactory): Received HTTP response header: 'HTTP/1.1 403 Forbidden' from 'https://barorganisatie.sharepoint.com/sites/pwa/_api/Projectdata'

2020-06-10 20:02:19| 0.6| 0.0|STATS |Storing feature(s) to FME feature store file `C:\\Users\\Piep0804\\AppData\\Local\\Temp\\10\\wb-cache-MS_PROJECTS_20200610_PVO-cnkzoa\\Main_HTTPCaller -1 3 fo 1 _lt_REJECTED_gt_ 0 2f29072d15a3cc7adf99d1de6a2dcf52f3287f34.ffsupdating'

2020-06-10 20:02:19| 0.6| 0.0|FATAL |The below feature caused the translation to be terminated

2020-06-10 20:02:20| 0.6| 0.0|STATS |Storing feature(s) to FME feature store file `I:\\APPDATA\\FME\\FME_Scheduling\\Workbenches\\MS_PROJECTS\\MS_PROJECTS_20200610_PVO_log.ffs'

2020-06-10 20:02:20| 0.6| 0.0|INFORM|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

 

12 replies

redgeographics
Rank
Userlevel 5
Badge +25

Did you try the Sharepoint reader/writer as well?

Not a Sharepoint user myself but maybe the Sharepoint tutorial has some useful tips.

david_r
Rank
Userlevel 4

Safe is currently working on a Sharepoint Online connector, which will be using the MS Graph API connector for authentication: https://hub.safe.com/publishers/gerhardatsafe/web-connections/microsoft-graph

In the meantime, you can use the Graph connector to manually authenticate with Sharepoint, then reference that connection in the HTTPCallers. While it's a bit fiddly to configure, it seems to work.

perry
Badge +2

@ redgeographics: unfortunately the SharePoint reader does not work.

@ david_r: Ik have imported the graph connector and configured it conform the specs the manager gave me. Testing I get the message:

AADSTS700016: Application with identifier 'b44f7b1e-5d5c-4319-b8ca-f1fe9e126241' was not found in the directory '8ba51362-c8d6-45ee-ab86-ec045428b469'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant.

What went wrong? Is this an authorisation problem so the identifier does no rights?

david_r
Rank
Userlevel 4

@ redgeographics: unfortunately the SharePoint reader does not work.

@ david_r: Ik have imported the graph connector and configured it conform the specs the manager gave me. Testing I get the message:

AADSTS700016: Application with identifier 'b44f7b1e-5d5c-4319-b8ca-f1fe9e126241' was not found in the directory '8ba51362-c8d6-45ee-ab86-ec045428b469'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant.

What went wrong? Is this an authorisation problem so the identifier does no rights?

I can't tell where the problem is, but basically you have to be very meticulous about following all the steps here: https://medium.com/@hubfly/generate-graph-api-access-token-in-sharepoint-provided-hosted-app-2ff23f0eccf2

Also, be sure to modify the Graph connector settings inside FME Options / Web Connections / Manage Services. You have to modify, at a minimum, the client ID and secret, as well as the scope parameter in the URLs.

perry
Badge +2

@david_r Although I have the full permissions I still get the same errors: 1 403 Forbidden' from" or "no permission" and "1 401 Unauthorized" . With the HTTP CALLER the request URL I use is an ODATA feed.Is this maybe the problem. I also tried several web_connections without result.

david_r
Rank
Userlevel 4

@david_r Although I have the full permissions I still get the same errors: 1 403 Forbidden' from" or "no permission" and "1 401 Unauthorized" . With the HTTP CALLER the request URL I use is an ODATA feed.Is this maybe the problem. I also tried several web_connections without result.

I couldn't say, these connexions are complex beasts and there are a lot of things that have to match up for everything to work. Your best bet may be to contact Safe Support and try to get in touch with Gerhard, the developer of this connector.

perry
Badge +2

 

@GerhardAtSafe

Hi Gerhard,

My goal is to read data from MS Projects.

I have to make a connection to sharepoint.

As suggested y David_R I have downloaded and installed Microsoft Graph.xml.

Futhermore the manager of sharepoint has given me credentials according to the description. (register an app, Copy the application ID ...)

When I make a test-connection I have an ‘token refresh’ error. What went wrong?

I use these variables

 

Client ID: 2016d.....ae55

PW: nkEYp.....gC9T

 

Redirection URL: https://login.microsoftonline.com/common/oauth2/nativeclient

 

URL: https://barorganisatie.sharepoint.com/.default" target="_blank">https://login.microsoftonline.com/common/oauth2/v2.0/authorize?scope=https://barorganisatie.sharepoint.com/.default offline_access&response_type=code&prompt=consent

Retrieve token: https://barorganisatie.sharepoint.com/sites/pwa/_api/ProjectServer" target="_blank">https://login.microsoftonline.com/common/oauth2/v2.0/token?scope=https://barorganisatie.sharepoint.com/sites/pwa/_api/ProjectServer

Refresh token: https://barorganisatie.sharepoint.com/sites/pwa/_api/ProjectServer" target="_blank">https://login.microsoftonline.com/common/oauth2/v2.0/token?scope=https://barorganisatie.sharepoint.com/sites/pwa/_api/ProjectServer

 

- are these variables correct filled?

- can you help me to make the connection with sharepoint/ms projects?

so I can read the data of sharepoint/ms projects in FME.

G
Badge

Hi @perry,

I think the "scope" is not correctly configured. The "scope" parameter should be identical to the permission you configured in the Azure App Registration and not the URL to your site. If you want to configure it specifically to your site you can replace the "common" path parameter with your tenant (as shown in the doc below). But the "scope" needs to list the permission of your app.

 

 

Please review the Microsoft documentation on this topic:

 

https://docs.microsoft.com/en-us/graph/auth-v2-user

Update:

So the scope is incorrect if you try to use Microsoft Graph API. But looking into MS Project it seems like this is not supported by the Graph API anyways so your initial approach was correct using the existing Sharepoint template Web Service and adding your tenant as a scope.

I am not too familiar with the Sharepoint API or MS Project so I am not sure what permissions you would need to make a successful request, but error seems to suggest that it is a permission issue.

 

Feel free to share the API documentation for the requests you are trying to send. That will make it easier for the community to help with this.

I hope this helps!

B

Hi @[gerhardatsafe]​ . 

 

There seems to be some problems regarding the Microsoft SharePoint List reader. My understanding of @[david_r]​ comment is that you are working on a new version of the reader?

 

Using FME 2020.1.1 Build 20608 the connector returns a Azure AD error AADSTS70011. 

 

Using FME 2019.2.2.0 Build 19817 returns this error: Failed to retrieve feature types.

 

Microsoft SharePoint List Reader: Error authenticating via SAML. Verify that the username and password are correct, and that this is a SharePoint Online instance. Response message was: '200'

 

The log file: 

Creating reader for format: Microsoft SharePoint List
Trying to find a PYTHON plugin for reader named `SHAREPOINT'
Loaded Python module `sharepoint' from file `C:\Program Files\FME2019\python\python37\sharepoint\__init__.pyc'
Creating reader for format: Microsoft SharePoint List
Trying to find a PYTHON plugin for reader named `SHAREPOINT'
Loaded Python module `sharepoint' from file `C:\Program Files\FME2019\python\python37\sharepoint\__init__.pyc'
Microsoft SharePoint List Reader: Items Per Request format parameter is set to '5000'
Microsoft SharePoint List Reader: Logging into 'https://geonett.sharepoint.com/sites/Support' as 'birk.slipersaeter@geodata.no' using authentication mode 'SAML'
Microsoft SharePoint List Reader: SSL certificate verification failed for host 'geonett.sharepoint.com'. HTTPS connections may not be secure. Disabling verification of SSL certificates. Message: HTTPSConnectionPool(host='geonett.sharepoint.com', port=443): Max retries exceeded with url: /sites/Support (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:1051)')))
Microsoft SharePoint List Reader: Error authenticating via SAML. Verify that the username and password are correct, and that this is a SharePoint Online instance. Response message was: '200'
A fatal error has occurred. Check the logfile above for details
Failed to obtain any schemas from reader 'SHAREPOINT' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information
UniversalReader -- readSchema resulted in 0 schema features being returned
Reader Parameter(0) = >https://geonett.sharepoint.com/sites/Support<
Reader Parameter(1) = >USER<
Reader Parameter(2) = >birk.slipersaeter@geodata.no<
Reader Parameter(3) = >PASSWORD<
Reader Parameter(4) = >********<
Reader Parameter(5) = >AUTHENTICATION<
Reader Parameter(6) = >SAML<
Reader Parameter(7) = >HIDDEN_LISTS<
Reader Parameter(8) = >No<
Reader Parameter(9) = >ALL_FIELDS<
Reader Parameter(10) = >No<
Reader Parameter(11) = >_MERGE_SCHEMAS<
Reader Parameter(12) = >YES<
Reader Parameter(13) = >RETRIEVE_ALL_TABLE_NAMES<
Reader Parameter(14) = >YES<
Reader Directive(0) = >RUNTIME_MACROS<
Reader Directive(1) = >USER,birk.slipersaeter@geodata.no,PASSWORD,********,AUTHENTICATION,SAML,HIDDEN_LISTS,No,ALL_FIELDS,No,_MERGE_SCHEMAS,YES,RETRIEVE_ALL_TABLE_NAMES,YES<
UniversalReader -- readSchema resulted in 0 schema features being returned
Failed to obtain any schemas from reader 'SHAREPOINT' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

After turning off 2FA for a new user, it worked in FME 2019.2.2, but not FME 2020.1.1. Is there some way around this that doesn't need the MS Graph configuration?

 

Kind regards,

Birk

david_r
Rank
Userlevel 4

Hi @[gerhardatsafe]​ . 

 

There seems to be some problems regarding the Microsoft SharePoint List reader. My understanding of @[david_r]​ comment is that you are working on a new version of the reader?

 

Using FME 2020.1.1 Build 20608 the connector returns a Azure AD error AADSTS70011. 

 

Using FME 2019.2.2.0 Build 19817 returns this error: Failed to retrieve feature types.

 

Microsoft SharePoint List Reader: Error authenticating via SAML. Verify that the username and password are correct, and that this is a SharePoint Online instance. Response message was: '200'

 

The log file: 

Creating reader for format: Microsoft SharePoint List
Trying to find a PYTHON plugin for reader named `SHAREPOINT'
Loaded Python module `sharepoint' from file `C:\Program Files\FME2019\python\python37\sharepoint\__init__.pyc'
Creating reader for format: Microsoft SharePoint List
Trying to find a PYTHON plugin for reader named `SHAREPOINT'
Loaded Python module `sharepoint' from file `C:\Program Files\FME2019\python\python37\sharepoint\__init__.pyc'
Microsoft SharePoint List Reader: Items Per Request format parameter is set to '5000'
Microsoft SharePoint List Reader: Logging into 'https://geonett.sharepoint.com/sites/Support' as 'birk.slipersaeter@geodata.no' using authentication mode 'SAML'
Microsoft SharePoint List Reader: SSL certificate verification failed for host 'geonett.sharepoint.com'. HTTPS connections may not be secure. Disabling verification of SSL certificates. Message: HTTPSConnectionPool(host='geonett.sharepoint.com', port=443): Max retries exceeded with url: /sites/Support (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:1051)')))
Microsoft SharePoint List Reader: Error authenticating via SAML. Verify that the username and password are correct, and that this is a SharePoint Online instance. Response message was: '200'
A fatal error has occurred. Check the logfile above for details
Failed to obtain any schemas from reader 'SHAREPOINT' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information
UniversalReader -- readSchema resulted in 0 schema features being returned
Reader Parameter(0) = >https://geonett.sharepoint.com/sites/Support<
Reader Parameter(1) = >USER<
Reader Parameter(2) = >birk.slipersaeter@geodata.no<
Reader Parameter(3) = >PASSWORD<
Reader Parameter(4) = >********<
Reader Parameter(5) = >AUTHENTICATION<
Reader Parameter(6) = >SAML<
Reader Parameter(7) = >HIDDEN_LISTS<
Reader Parameter(8) = >No<
Reader Parameter(9) = >ALL_FIELDS<
Reader Parameter(10) = >No<
Reader Parameter(11) = >_MERGE_SCHEMAS<
Reader Parameter(12) = >YES<
Reader Parameter(13) = >RETRIEVE_ALL_TABLE_NAMES<
Reader Parameter(14) = >YES<
Reader Directive(0) = >RUNTIME_MACROS<
Reader Directive(1) = >USER,birk.slipersaeter@geodata.no,PASSWORD,********,AUTHENTICATION,SAML,HIDDEN_LISTS,No,ALL_FIELDS,No,_MERGE_SCHEMAS,YES,RETRIEVE_ALL_TABLE_NAMES,YES<
UniversalReader -- readSchema resulted in 0 schema features being returned
Failed to obtain any schemas from reader 'SHAREPOINT' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

After turning off 2FA for a new user, it worked in FME 2019.2.2, but not FME 2020.1.1. Is there some way around this that doesn't need the MS Graph configuration?

 

Kind regards,

Birk

The SharepointOnlineConnector was relased a couple of weeks ago, it's available here: https://hub.safe.com/publishers/safe/packages/microsoft-sharepoint

It uses the MS Graph interfaces and we've successfully used it to access all the contents of a Sharepoint site.

G
Badge

Hi @[gerhardatsafe]​ . 

 

There seems to be some problems regarding the Microsoft SharePoint List reader. My understanding of @[david_r]​ comment is that you are working on a new version of the reader?

 

Using FME 2020.1.1 Build 20608 the connector returns a Azure AD error AADSTS70011. 

 

Using FME 2019.2.2.0 Build 19817 returns this error: Failed to retrieve feature types.

 

Microsoft SharePoint List Reader: Error authenticating via SAML. Verify that the username and password are correct, and that this is a SharePoint Online instance. Response message was: '200'

 

The log file: 

Creating reader for format: Microsoft SharePoint List
Trying to find a PYTHON plugin for reader named `SHAREPOINT'
Loaded Python module `sharepoint' from file `C:\Program Files\FME2019\python\python37\sharepoint\__init__.pyc'
Creating reader for format: Microsoft SharePoint List
Trying to find a PYTHON plugin for reader named `SHAREPOINT'
Loaded Python module `sharepoint' from file `C:\Program Files\FME2019\python\python37\sharepoint\__init__.pyc'
Microsoft SharePoint List Reader: Items Per Request format parameter is set to '5000'
Microsoft SharePoint List Reader: Logging into 'https://geonett.sharepoint.com/sites/Support' as 'birk.slipersaeter@geodata.no' using authentication mode 'SAML'
Microsoft SharePoint List Reader: SSL certificate verification failed for host 'geonett.sharepoint.com'. HTTPS connections may not be secure. Disabling verification of SSL certificates. Message: HTTPSConnectionPool(host='geonett.sharepoint.com', port=443): Max retries exceeded with url: /sites/Support (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:1051)')))
Microsoft SharePoint List Reader: Error authenticating via SAML. Verify that the username and password are correct, and that this is a SharePoint Online instance. Response message was: '200'
A fatal error has occurred. Check the logfile above for details
Failed to obtain any schemas from reader 'SHAREPOINT' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information
UniversalReader -- readSchema resulted in 0 schema features being returned
Reader Parameter(0) = >https://geonett.sharepoint.com/sites/Support<
Reader Parameter(1) = >USER<
Reader Parameter(2) = >birk.slipersaeter@geodata.no<
Reader Parameter(3) = >PASSWORD<
Reader Parameter(4) = >********<
Reader Parameter(5) = >AUTHENTICATION<
Reader Parameter(6) = >SAML<
Reader Parameter(7) = >HIDDEN_LISTS<
Reader Parameter(8) = >No<
Reader Parameter(9) = >ALL_FIELDS<
Reader Parameter(10) = >No<
Reader Parameter(11) = >_MERGE_SCHEMAS<
Reader Parameter(12) = >YES<
Reader Parameter(13) = >RETRIEVE_ALL_TABLE_NAMES<
Reader Parameter(14) = >YES<
Reader Directive(0) = >RUNTIME_MACROS<
Reader Directive(1) = >USER,birk.slipersaeter@geodata.no,PASSWORD,********,AUTHENTICATION,SAML,HIDDEN_LISTS,No,ALL_FIELDS,No,_MERGE_SCHEMAS,YES,RETRIEVE_ALL_TABLE_NAMES,YES<
UniversalReader -- readSchema resulted in 0 schema features being returned
Failed to obtain any schemas from reader 'SHAREPOINT' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information

After turning off 2FA for a new user, it worked in FME 2019.2.2, but not FME 2020.1.1. Is there some way around this that doesn't need the MS Graph configuration?

 

Kind regards,

Birk

@birkslip​ The SharePointOnlineConnector is not replacing the Microsoft SharePoint List reader. It's designed for uploading & download files and not specifically for interaction with lists. The SharePointOnlineConnector does return list IDs for uploaded files to be used in the SharePoint List Format or customer MS Graph requests via HTTPCaller. The Share Point List Format does not use MS Graph but the native Sharepoint API.

 

Regarding the issue that you are describing I would recommend to post this as a separate question or open a support ticket, because this question is probably unrelate as it's more about accessing MS Projects via Sharepoint.

B

@birkslip​ The SharePointOnlineConnector is not replacing the Microsoft SharePoint List reader. It's designed for uploading & download files and not specifically for interaction with lists. The SharePointOnlineConnector does return list IDs for uploaded files to be used in the SharePoint List Format or customer MS Graph requests via HTTPCaller. The Share Point List Format does not use MS Graph but the native Sharepoint API.

 

Regarding the issue that you are describing I would recommend to post this as a separate question or open a support ticket, because this question is probably unrelate as it's more about accessing MS Projects via Sharepoint.

Thank you for the explanation. I'll make a new question.

Reply


Community Stats

28,723
Posts
109,510
Replies
36,559
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.