SQLite connection refresh not working

[Ildiko]

I have an SQLite DB on my localhost installed. There is no authentication on it without any privacy level.

I have the correct x64 ODBC SQlite3 driver installed under System DSN:

Data Source Name: LogisticDB

Database Name: pointing to the SQLite3 DB location on my local computer.

I can connect and get data to the Power BI Desktop, there, it refreshes perfectly. Data Source settings in Power BI desktop:

DSN: LogisticDB, no connection strings (as it is optional)

Premission settings in Power BI desktop: Credential type: Default or Custom ; Privacy level: None

Works fine in Power BI Desktop.

Clearing caches before loading in the Data in desktop. Then publishing it to Power BI Cloud.

I have a On-Premises Data Gateway installed on my local computer (not the personal mode). Opened during refresh, signed in with same cloud email address. It states it's online and ready to be used.

Now, I add the connection after publishing the dashboard.

Uses the Gateway cluster I created and the one that is up and running.

Connection string: dsn=logisticDB

Authentication mode: Anonymous

Privacy level: None

If I don't skip test connection, it gives me an error: 

Unable to update connection credentials. Unable to connect to the data source. Either the data source is inaccessible, a connection timeout occurred, or the data source credentials are invalid.

Details: MyGateway: ODBC: ERROR [HY000] connect failed ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

Clicking on refresh status under CONNECTION, it states online.

Now comes the funny part when I try to run manually the refresh on this connection. I get the below error message every time:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"ODBC: ERROR [HY000] connect failed\r\nERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"Odbc"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"dsn=LogisticDB"}},{"code":"Microsoft.Data.Mashup.ValueError.OdbcErrors","detail":{"type":1,"value":"#table({\"SQLState\", \"NativeError\", \"Message\"}, {})"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: ORDERS.
Cluster URI: WABI-WEST-EUROPE-B-PRIMARY-redirect.analysis.windows.net
Activity ID: 0e601d9d-264d-4dcc-9824-6acd191f93a6
Request ID: 7efa3bfe-8f83-3e5e-55c0-5dee2c83679d
Time: 2023-10-05 16:30:12Z

[lbendlin]

Make sure that the gateway service has access to the folder where the SQLite database file is stored.

[Ildiko]

Hi,

I don't understand what you mean... Can you please describe in more details?

I don't find anything in the On-premises data gateway app when it's open and ready.
I setup everything under the Data gateway connections and it shows it's online.

I don't know what else I could configure...

[lbendlin · Accepted solution]

Unlike other databases SQLite is entirely stored in a single file on disk.  You need to make sure that the user account running the gateway (by default PBIEGWService) has read access to that file. Otherwise your ODBC connection won't be able to read the data.

[Ildiko]

Many many thanks. 😍

I had to add NT SERVICE\PBIEgwService account to the folder's Security settings and it solved the refresh issue.