Forum Discussion

NSRjecross's avatar
NSRjecross
Frequent Visitor
6 years ago

reading access databases hosted in sharepoint libraries: works in desktop, fails on service

I realised I had necroed an old thread with a previous reply when I suspect I should have made a new one as I suspect this is a bug somewhere rather than looking for workaround like the previous post...
Need Help
NSRjecross's avatar
NSRjecross
Frequent Visitor
6 years ago

Though in this case if I could force the connection via one of our on-premise gateways, then I'd be able to ensure the libraries were installed. Wonderr which is easier to fix - oauth2 for the gateway or Access.database() for sharepoint [contents]

I haven't tried running it outside premium capacity though.. I'll give that a go monday

Any other suggestions? Does anyone have this arrangement actually working?

Jen

Herding cats is easy with a can opener. Not sure quite what to feed O365 (apparently cash and souls isn't enough)

 

NSRjecross's avatar
NSRjecross
Frequent Visitor
6 years ago

Update: as of this morning it appears OAuth2 authentication has been added to the on-premise gateway for Sharepoint connectors. (But you need to upgrade the gateway software before you can define a connection using it). 

This will at least let us route the query past somewhere we have installed the required modules.

 

I also found you can examine the connection string used to query the access database via the xmla interface (provider=microsoft.powerbi.olddb in case anyone was interested)

Getting closer I think

Jennifer

  • chaz2jerry's avatar
    chaz2jerry
    Advocate IV
    6 years ago

    NSRjecross  I am jumping into this thread from the older one where you and I both commented.  

    FYI my current workaround is not using Sharepoint.  I have to put the AccessDB file on Network Drive and use gateway to connect dataflow to it. 

     

    When I tried putting AccessDB on Sharepoint and connect using dataflow it times out.  When I tried using PBI Service dataset to refresh (after connecting AccessDB on Sharepoint from Desktop and published), failed with same error as you pointed out (OAuth2 and need gateway update).  I will update gateway and see what happens.

  • NSRjecross's avatar
    NSRjecross
    Frequent Visitor
    6 years ago

    Our IT team updated the gateway, however now I get an error:

    Unable to connect: We encountered an error while trying to connect to . Details: "Please upgrade your On-premises data gateway (or gateway cluster) to support this feature."

    Even though we have the latest gateway software from MS downloads. (3000.45.7)

    The error is triggered as soon as I set Oauth2 authentication and try to enter the credentials.

    Full error: 

    Unable to connect: We encountered an error while trying to connect to . Details: "Please upgrade your On-premises data gateway (or gateway cluster) to support this feature."Hide details

    Activity ID:40da91fc-d0d8-4835-a577-6200e6620fd6
    Request ID:8d118612-be86-457a-cec4-26f4db543de6
    Cluster URI:https://wabi-australia-southeast-redirect.analysis.windows.net/
    Status code:400
    Error Code:DMTS_PowerBIDataMovementGatewayNotSupportedError
    Time:Mon Jul 13 2020 16:15:00 GMT+0800 (Australian Western Standard Time)
    Service version:13.0.13860.51
    Client version:2007.1.01830-train

     

     

    So forcing the service to send the request via our servers isn't going to be an option.

     

    Back to "why doesn't the service work when desktop does"? (We'll worry about the the option to route sharepoint requests via on-premise fails later.  Refreshing cloud sources via on-premise is a weird thing to do anyway.)

  • chaz2jerry's avatar
    chaz2jerry
    Advocate IV
    6 years ago

    NSRjecross Yes I am running into same error.  I guess the "upgrade gateway" error message is a generic catch-all message and not tailored for this specific scenario.  At this time my only working option is to use Network Drive instead of Sharepoint.