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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors