Moving from Direct query to Import mode and Publishing the report does not work

[hari_krishnan]

Hi,

I have a Power BI report already created in Direct query mode and the data source is SQL server. My organisation uses a Report server hosted inside the intranet. The access to the database is using my Windows authentication in Power BI desktop, I also have a Power user account creds to use when it is hosted in the Report server. Development and Hosting is working fine in the direct query mode.

Today for some features to be needed I changed the mode to Import mode and it successfully pulled the data and converted to import mode. Working fine in Power BI desktop.

1. The Issue is when I published to the cloud intranet running Power BI server. I changed the windows authentication from the data source to the credentials for Power BI (I use this creds when it was in Direct query mode). When I click the "Test Connection" it says:

Couldn't connect

The report server couldn't connect to the data source using the information you entered. Make sure you've entered the connection string and any credentials correctly.

Hide error details 

       Keyword not supported: 'subdomain.domain.com,14330;database_name;integrated security'.

So I tried giving a invalid credentials for the Testing, and it says: Log on failed. Ensure the user name and password are correct.

So it means the actual credentials above is working fine for the data source, but some other error is happening, which I am finding very strange. Because Power BI desktop is working fine.

2. The Scheduled refresh tab for the Power BI report is showing the same error when it was in Direct query mode which is "Scheduled Data Refresh is not available for reports using Direct Query or a Live Connection to SQL Server Analysis Services."

I tried deleting the report and reuploading but the issue is not changing no matter what. Please help me on this.

[vanessafvg]

have you tried deleting the both the dataset / report  and re publish not just delete the report but both.

[hari_krishnan]

@vanessafvg yes I tried, deleted entire queries and then tried to upload a error report, still its recognising as direct query. Seems to me like a bug. which keeps some sort of metadata still in the pbix file.

[vanessafvg]

have you tried to remove any data connections in your data model under file / options and settings / data source settings that might be creating this issue?

[hari_krishnan]

@vanessafvg Thanks for the reply.

I am using Version May 2019 and it does not have any such option under settings for deleting the connection.

[vanessafvg]

hi there its not under options its data source settings, maybe delete your connections and start recreate them

[hari_krishnan]

@vanessafvg 

I checked, there is not option to delete and start over. I can only change the server and database name and some options. Tried changing that. Also tried clearing permissions and then uploading to server, but still the Scheduled refresh tab recognises report as direct query.

[vanessafvg]

and the m (power query code)  in the advanced settings for the connection can you post that here?

[hari_krishnan]

Hi @vanessafvg , Here is the code for a table in Advanced editor

[hari_krishnan]

No one having any thoughts, I need this to be accomplished.

Update: I tried creating a very new report with the same data source, but this time directly in Import mode when selecting Data connectivity mode.

This time hosting is working fine, I have the Scheduled refresh tab available now and everything loads perfectly.

This means data source and creds are fine but the Power BI file is corrupted? Stores the old info of the connectivity mode in the file? How can I clear that? Please help

[hari_krishnan]

Please help me on this, @v-alq-msft  @v-lid-msft