Azure SQL Database online connection for refresh: data source credentials invalid

[Lukas_Sieber]

Dear Community,

I am currently trying to refresh my dataset in Power BI Online with the "scheduled refresh" possibility. 

Now the issue is the following: If I go under settings-> datasets->"dataset name" in the part datasource credentials the error message appears:

Spoiler

"Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again."" 

The point is that I use the same credentials as in Power BI Desktop. There the connection to the Azure SQL DB works all fine. I also only included one single table to test the connection but it still does not work.

Here the query:

Spoiler

let
  Source = Sql.Database("servername", "databasename"),
  dbo_Countries = Source{[Schema="dbo",Item="Countries"]}[Data]
in
  dbo_Countries

This is the error message if I try to edit the credentials in the online version:

Spoiler

Failed to update data source credentials: Login failed for user 'username'.Hide details

Activity ID:	cdc5fd21-9633-f8ce-3d93-dd0e8a8f5d60
Request ID:	668d0ef3-404d-874b-f1aa-7b1d840626c8
Status code:	400
Time:	Tue Jul 04 2017 16:29:34 GMT+0200 (W. Europe Daylight Time)
Version:	13.0.1781.272
Cluster URI:	https://wabi-north-europe-redirect.analysis.windows.net

Another point: The same user for the same database works fine for scheduled refresh when my colleague uses it for his projects.

I really struggle now to find new ideas how i could solve this issue. Does someone have an idea how I could solve this issue or what might cause it? 

Btw: I could find the following post here in the forum which seems to explain a similar issue. But to be honest, i can not see what i have to do with this solution.

Thanks a lot for your help.

BR

[v-yuezhe-msft]

@Lukas_Sieber,

I am not able to reproduce you issue. Could you please follow the guide in this article to check that if you turn on 'Windows Azure Services' in the allowed services section?

Regards,

[Lukas_Sieber]

@v-yuezhe-msft,

Tanks for your response. 

I just checked this and can tell you that Windows Azure Services were enabled all the time. 

Do you have any other ideas what might cause the issues?

Regards

[v-yuezhe-msft]

@Lukas_Sieber,

Does your colleague use same username and same password as yours? Does your dataset only contain Azure SQL data source?

Regards,

[Lukas_Sieber]

@v-yuezhe-msft,

Yes my colleague uses the exact same username and password. 

About the dataset: I tried it out with different combinations:

- containing only one table of this azure sql database (for testing the connection)

- containing combinations of tables from this azure sql database

- combinations with a excel file

- the exact same Desktop file as my colleague uses

all of it does not work and gives me the same error message.

I even tried it out in a shared workspace which did not work either...

Any other ideas? 

Regards

[v-yuezhe-msft]

@Lukas_Sieber,

When you combine Azure SQL data source and Excel file in a single file, please use on-premises gateway instead of personal gateway to refresh your dataset and check if you get the same issue.

You would need to add both the Azure SQL data source and Excel data source within registered on-premises gateway.

Regards,

[Lukas_Sieber]

@v-yuezhe-msft

Thanks again, 

As far as i understood it until now i do not need a data gateway to connect to a azure sql db in the cloud?!

I only have the azure sql db as only data source in one file. 

In another Power BI file i use an excelfile which lies on the sharepoint (in combination with this azure sql db) so a data gateway should not be needed as well (as far as I got it)

Do I miss something? 

Or is the data gateway needed for that as well? I have no computer which is online every day and would like to refresh online at least daily so that other people can see the refreshed data. 

Thanks again for your help.

Kind Regards

[v-yuezhe-msft]

@Lukas_Sieber ,

If your dataset contains online data sources, gateway is not required.

Regarding to your issue, I would recommend you submit a support ticket on the support.powerbi.com site.

Regards,