Schedule data refresh for an on-premise SQL server database configured with ODBC

[nirman]

Hi,

I have an on-premise SQL server database, and I have designed a few reports targetting the database with SQL authentication.

I have designed the report using "PowerBI Desktop", and used ODBC to connect and query the on-premise database. I didn't use "SQL Server" as the data provider because when I try to do that it gives a message saying "The datasource you are trying is access doesn't support encryption" that means, the connection is not encrypted, and so not secured. Correct me if I am wrong.

This is why I have chosen ODBC, and supplied SQL authentication and credentials. I could successfully able to design and the it is working well.. However, when I try to schedule the refresh, I get a message saying - "Your data source can't be refreshed because the credentials are invalid", and now when I try to edit the credentials, it gives only three options - Basic, Windows and Anonymous, and none of them fits in my scenario because I am having SQL authentication.

Could anyone please suggest on this?

[Bjoern]

@nirman I always assumed that the basic authentication IS the SQL authentication. Is this not the case? 

[nirman]

@Bjoern When I try to apply Basic authentication, it says -

Failed to update data source credentials: The correct data provider must be installed on the Gateway computer.

The 'Driver' property with value '{SQL Server Native Client 11.0}' doesn't correspond to an installed ODBC driver.

The server where PowerBI Gateway is installed is having "SQL Server Native Client 10.0". Do you think that could be the reason? Also, the basic authentication is less secure as far as I understand, as it just sends Base-64 encoded data which are easy to hack.

[Lewis]

@nirman, Did you find a solution to this?

Thanks