cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirman
Advocate I
Advocate I

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

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?

3 REPLIES 3
Bjoern
Responsive Resident
Responsive Resident

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

@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
Regular Visitor

@nirman, Did you find a solution to this?

 

Thanks

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors