Setting up a new data source in on-premise gateway (Folders within a internal network)

[OscarIvanCampos]

Hi all,

We are currently setting up the on-premise gateway in our organization. I have managed to set up the connection to our SQL server. But I am just having issues when attending to set up a data source type "Folder". 

I got the following error message:

Unable to connect: We encountered an error while trying to connect to. Details"We could not register this data source for any gateway instances within this cluster.  

Has anyone encountered and solved this issue in the past? Any help would be really appreciated. 

Thank you, Oscar

[GilbertQ · Accepted solution]

Yes I would make sure that the Service Account has got access to that folder.

[GilbertQ]

Hi there

It would be that the folder you are specifying does not exist on the Gateway Server.

For example if the folder is stored on your PC in C:\Intel

The Gateway Server is a different physical machine and does not have the folder C:\Intel

You would need to share the folder and then use the Folder Share Name to connect to it.

[OscarIvanCampos]

Hi Gilbert, 

Thanks for your replay and help with this.

The folder actually exits on the server where the gateway has been installed. And the path works if I copy and paste it in any new folder. So, opened the location where the data is. 

What else could be happening? 

Thanks, Oscar

[GilbertQ]

Hi there

Does the account that the Power BI Service uses for authentication to the folder have access to the folder?

[OscarIvanCampos]

Yes, it does. 

[GilbertQ]

From the error, it would appear that it does not, if you could possibly enable the verbose logging in the data gateway to see where the error is coming from.

[OscarIvanCampos]

How can I do that? 

[GilbertQ]

Hi there

here is a link explaining how to do it.

It is also on the gateway under Diagnostics and then "Additional Logging"

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot

[OscarIvanCampos]

Thanks Gilbert for your help and support so far. 

I have configurated the verbose logging. I also followed the steps in the link provided, but I did not find this file Microsoft.PowerBI.EnterpriseGateway.exe.config  in the C:\Program Files\On-premises data gateway path. 

[GilbertQ]

Hi there

Double check if you have enabled the option to show system files, maybe it is hidden?

[OscarIvanCampos]

Hi Gilbert, 

I have done it, but it still does not show up. 

I have got this one Microsoft.PowerBI.EnterpriseGateway.exe. (I got it before I enabled the option to show hidden files) 

That's its content

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6"/>
</startup>
<system.diagnostics>
<trace autoflush="true" indentsize="4">
<listeners>
<remove name="Default" />
<add name="ApplicationFileTraceListener"
type="Microsoft.PowerBI.DataMovement.Pipeline.Common.Diagnostics.RotatableFilesManagerTraceListener, Microsoft.PowerBI.DataMovement.Pipeline.Common"
initializeData="%LOCALAPPDATA%\Microsoft\On-premises data gateway\,GatewayInfo.log,GatewayErrors.log,GatewayNetwork.log,20,50" />
</listeners>
</trace>
</system.diagnostics>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Microsoft.PowerBI.AdomdClient" publicKeyToken="89845dcd8080cc91" culture="neutral" />
<bindingRedirect oldVersion="11.0.0.0-14.00.00.00" newVersion="14.00.00.00" />
<publisherPolicy apply="no" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Microsoft.Diagnostics.Tracing.TraceEvent" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-1.0.41.0" newVersion="1.0.41.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-12.0.0.0" newVersion="12.0.0.0" />
</dependentAssembly>
</assemblyBinding>
</runtime>
<system.net>
<defaultProxy useDefaultCredentials="true" />
</system.net>
<system.data>
<DbProviderFactories>
<add name="Microsoft Mashup Data Provider"
invariant="Microsoft.Data.Mashup"
description="Ado.NET Provider for Power Query Mashups"
type="Microsoft.Data.Mashup.MashupProviderFactory, Microsoft.Data.Mashup"/>
</DbProviderFactories>
</system.data>
</configuration>

[GilbertQ]

Hi @OscarIvanCampos 

What do the actual log files say?

That is the information which will detail what the error is?

[OscarIvanCampos]

This is what basically says.

Sorry for the amount of text copied. 

[DM.Pipeline.Diagnostics] Exception object created [IsBenign=True]: Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: .; ErrorShortName: MashupDataAccessValueException[ErrorCode=-2147467259,HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259,HResult=-2147467259]

[DM.Pipeline.Diagnostics] Exception data: DM_ErrorDetailNameCode_UnderlyingErrorMessage = <pi><pi>Could not find a part of the path 'h:\0. data collection\calendar.xlsx'.</pi></pi>

GatewayPipelineErrorCode=DM_GWPipeline_Gateway_MashupDataAccessError
GatewayVersion=
Reason= --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: MashupValueException:<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): Could not find a part of the path 'h:\0. data collection\calendar.xlsx'.
at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass44_2`1.<StartEvaluationAndGetResultSource>b__0()
at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__3.MoveNext()</pi>

GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
GatewayVersion=
InnerType=MashupValueException
InnerMessage=<pi>Could not find a part of the path 'h:\0. data collection\calendar.xlsx'.</pi>
InnerToString=<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): Could not find a part of the path 'h:\0. data collection\calendar.xlsx'.
at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass44_2`1.<StartEvaluationAndGetResultSource>b__0()
at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader()
at Microsoft.Data.Mashup.DataSourceReference.TestConnection(String connectionString)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.GatewayDataAccessMashupUtils.DSRTestConnectionAsync(String dsrJson, MashupCredential credential, Nullable`1 activityId, Int32 testConnectionTimeout, String[] exemptions, String containerPool)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayDataAccess.MashupOleDbConnectionProvider.<TestConnectionAsync>d__3.MoveNext()</pi>

[GilbertQ]

Hi there

If you log into the Gateway Server and you paste in the following below, will it find the Excel file?

h:\0. data collection\calendar.xlsx

[OscarIvanCampos]

No, at all. 

This is the error message I am getting:

Unable to connect: We encountered an error while trying to connect to . Details: ""Hide details

Activity ID:	d2fdf6c8-0e16-46ed-ad8e-c9f7841e8d51
Request ID:	1ad618dc-6ce2-c35e-e8f8-fe365f55d0bf
Cluster URI:	https://wabi-australia-southeast-redirect.analysis.windows.net
Status code:	400
Error Code:	DMTS_UpdateClusterDatasourceCredentialsErrorCode
Time:	Mon Jul 15 2019 15:47:13 GMT+1000 (Australian Eastern Standard Time)
Version:	13.0.9933.297

Smart refresh:	Unable to connect to the data source undefined.
Underlying error code:	-2147467259
Underlying error message:	File or Folder: We couldn't find the folder 'h:\0. data collection\calendar.xlsx\*'.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.ValueError.Reason:	DataSource.NotFound

[GilbertQ]

Hi there,

If you look at the error it says it cannot find the file or folder.

It looks like the folder name is "0. data collection"

[OscarIvanCampos]

Yes, I know. But the folder is in that path. 

If I am to copy the link (path) and paste in windows explorer it opens it up. 

h:\0. data collection

That's what I do not understand at all. I do not know if it is related to network connectivity or the firewalls? 

[GilbertQ]

Hi there

Does it work also when you have logged into the Gateway Server (where the On-Premise Data Gateway is installed) and tested from there?

[OscarIvanCampos]

I think we have just found the issue. 

Does the gateway have an independent account?  What I mean is that even though, I am putting my credentials, it says that I need to ensure that the gateway service account has permission to access the folder.  And its account is      NT SERVICE\PBIEgwService.

I imagine I need to talk to IT in order to create that account in our internal network. is that right? 

Thanks for being so patient. 

Oscar 

[GilbertQ · Accepted solution]

Yes I would make sure that the Service Account has got access to that folder.

[OscarIvanCampos · Accepted solution]

Hi Gilbert,

Thanks for your help and support. All your suggestions actually help me to sort this out. 

Another additional step I did was to set up the UNC path as below:

\\name-of-server\share-name\folder\file

Instead of 

H:\0. Data Collection

Sorry for the late reply. 

Btw, I have attended one of your presentations on Power Bi and it was pretty good. Well done on sharing your knowledge.  

Oscar