Azure SQL credentials are lost after first publish

[eranse]

Hi,

I have created a query using Desktop version which connects to both Azure App Insight and Azure SQL DB.

The App Insight query was created from Analytics site, and exported to Desktop vesion using M language.

I published the query to Power BI Personal version from the Desktop version and created a report from it. I pinned the report to a dashboard in order to share it with other people and have it refreshed on schedule (once a day is good enough).

When I first published the report I chose OAuth authentication method but after that it disappeared from the authentication options of the datasource.

The report is not refreshed automatically and when I check it I discover that I need to re-enter the credentials.

I enter them (only options are 'Windows' and 'Basic' so I choose Basic) and it is working. But after a few hours it is again asking for credentials.

Here are some of the errors I received:

When trying to use a personal gateway:

Activity ID2d075996-ea69-46ef-a850-35c09badee92

Request ID4067b800-f141-9221-6fb5-cd7ea21b528d

Correlation IDa7481516-5b23-628a-5d30-a203bb4b80b1

Status code400

TimeMon Dec 05 2016 10:54:24 GMT+0200 (Jerusalem Standard Time)

Version13.0.1700.720

Cluster URIhttps://wabi-west-europe-redirect.analysis.windows.net

When clicking 'Refresh now':

Something went wrong.

There was an error when processing the data in the dataset.
Please try again later or contact support. If you contact support, please provide these details.

Data source type

Processing errorThe data source kind doesn't support the credential type. Data source kind: SQL. Credential type: OAuth2.

Cluster URIWABI-WEST-EUROPE-redirect.analysis.windows.net

Activity ID2d075996-ea69-46ef-a850-35c09badee92

Request ID63a77c23-cfe1-b4e1-7bdc-a7f9466169a8

Time2016-12-05 08:56:28Z

From Data Source Credentials section:

Last refresh failed: Mon Dec 05 2016 10:56:28 GMT+0200 (Jerusalem Standard Time)
There was an error when processing the data in the dataset.Hide details

Data source type:
Processing error:	The data source kind doesn't support the credential type. Data source kind: SQL. Credential type: OAuth2.
Cluster URI:	WABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID:	2d075996-ea69-46ef-a850-35c09badee92
Request ID:	63a77c23-cfe1-b4e1-7bdc-a7f9466169a8
Time:	2016-12-05 08:56:28Z

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

Activity ID:	2d075996-ea69-46ef-a850-35c09badee92
Request ID:	394fa4c1-35a4-0042-e159-7aed7cfdf67d
Status code:	200
Time:	Mon Dec 05 2016 10:58:26 GMT+0200 (Jerusalem Standard Time)
Version:	13.0.1700.720

[v-haibl-msft]

@eranse

If you only get data from Azure SQL DB without App Insight in Power BI Desktop, which options can you seen in data source credentials after publish to Power BI Service? And can you update the credential successfully?

Best Regards,

Herbert

[eranse]

@v-haibl-msft

Thanks for your reply.

I tested it and have only the Windows and Basic Authentication methods.

I can update the credentials properly (using Basic method) and data is refreshed properly.

After a while (usually overnight) it is not working anymore.

The Azure SQL DB has only SQL server authentication enabled and I'm using these credentials for connecting from PBI.

A note which I think should not be relevant - I work from a laptop and take it with me at the end of each day. So I connect from several different networks.

[v-haibl-msft]

@eranse

If I get data from Azure SQL Database in Power BI Desktop and publish to Service, personal gateway is not needed. And I can refresh properly using Windows authentication method. I enabled scheduled refresh and waited for six hours, the credential was not lost.

I suggest you to create support ticket at http://support.powerbi.com (see bottom of page) since it may be tenant or account specific.

Best Regards,

Herbert

[eranse]

@v-haibl-msft

Thanks.

I am not using Windows authentication or AD so this could be the issue.

I'll open a support ticket.

[eranse]

I found the problem.

It seems like data from both App Insights and Azure SQL can be shared on the same report because of the different authentication methods of the datasources.

One solution can be to statically import SQL tables to the report but if data should change then the report will become outdated.

For now I left only App Insights analytics query and by making some changes on the data itself managed to create a usable report.

Hopefully this compatibility issue will be solved soon.

[filipvcsw]

I am experiencing a similiar issue. I have a report made of following datasources:

Azure SQL DB and a .csv file stored on sharepoint. 

Everything works fine on PowerBI Desktop. After publishing reports to PowerBI service, i reenter credentials for the Azure SQL DB using basic authentication method. Then I schedule an automatic refresh. Nex day I recieve an email with following error

The data source kind doesn't support the credential type. Data source kind: SQL. Credential type: OAuth2

Technical Information:

Activity Id: f1fd18b3-725c-49ca-9396-0f2d55de6d5d

Request Id: bbbca49f-a628-4593-8085-a711d24b37d5

Cluster Name: WABI-EUROPE-NORTH-B-redirect.analysis.windows.net

Refresh Start Time: 01/03/2017 02:08:03Z

Refresh End Time: 01/03/2017 02:09:09Z

Is this the same issue? I can't shedule datasource refresh for reports with datasources of different authentication method kind?