power bi gateway to connect sqlite via odbc

[oleks]

hi, We've been using Sqlite as a local data storage and manage to connect Power Bi to it via ODBC.

As the next stage, we want to use it via Gateaway, so that we can easily refresh the data.

Gateway installation process passes well and even passes test. 

However, when I try to refresh dataset on BI Service, I get:

yes, I saw similar post here, but it was not answered yet. Thanks a lot for your help

[oleks · Accepted solution]

Hi.

The issue was fixed after sessions with Microsoft Support Team.

The problem came from Authentification method. Sqlite is a serverless database (so no login nor password is required). So I just selected Anonymous authentification method and such connection even passed the test.

However, it is required to put Windows Autentification method (login  -you windows login, password - windows password). Now it works well.

I asked why it passed tests and I was tols, that Anonymous Connection behaves as "skip connection", so it was not passed, it was just skipped.

[kyko]

I know it's been a minute, but thank you so much for posting this solution!

[Anonymous]

Hi  @oleks ,

1. Have you created a user DSN or a system DSN in the ODBC administrator, you can try to create a system DSN instead, create a System DSN in the ODBC administrator to point to your data source, and then add the ODBC data source under the gateway. When adding an ODBC data source under the gateway, make sure to enter the same connection string as the one obtained in Power BI Desktop.

2. Check if your gateway and ODBC are the latest version.

3. Whether the password has been changed recently, and Power bi is still using the old password. Before reconnecting, you can clear the permissions in the data source settings.

This is the related document, you can view this content:

https://community.powerbi.com/t5/Power-Query/Personal-Gateway-X-On-Premises-Gateway/m-p/70945#M5526

https://stackoverflow.com/questions/23228716/oracleodbcoraora-12170-tnsconnect-timeout-occurred-erro...

https://community.powerbi.com/t5/Power-Query/Error-refreshing-ODBC-connection/m-p/340898

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[oleks]

1. Yes, it is system DSN. You can find screenshots in others answers in this thread.

2. Yes. all apps are updated.

3. Password was not changed.

[GilbertQ]

Hi @oleks 

Can you confirm that you have got the 64bit version of the ODBC driver installed on the On-Premise Gateway?

[oleks]

Yes.

Attaching screenshots: