Couldn't connect Unsupported keyword

[mturcotte_epq]

Since we upgraded PBIRS (from August 2018 to May 2019), we are experiencing problems with new PBI reports (either published/saved as on Report Server or Uploaded from PBIRS Portal) not able to connect to a SQL Server 2012 Data Source that other reports have no problem with.

"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.
Keyword not supported: 'eppv-sql12-2c\epqdw_prod;warehouse;integrated security'."

The odd thing is we are using the same data source configuration details as in other reports that Test the connection just fine on the same data source.

Any help appreciated.

MT

[jcollinson2001]

Problem experienced here too.. Please fix Microsoft!

[mturcotte_epq]

[d_gosbell]

Your connection string does not look to be complete (although I'm not sure how the test is working)

If you try the following format does it work?

Data Source=eppv-sql12-2c\epqdw_prod;Initial Catalog=WAREHOUSE

[mturcotte_epq]

Thank you for your reply.

Actually, the connection string in the screenshot at the bottom is from the report that works fine and whose Test Connection also works. Yet, it is the same connection string as I have in the Report that experiences the error, but the portal Data Source configuration interface does not allow me to edit it (i.e it was apparently set by the Desktop upon publishing).

Any other ideas?

Thanks again!

MT

[mturcotte_epq]

I think I found a BUG by comparing metadata created in PBIRS' metadata table dbo.DataModelDataSource, specifically by comparing my problem reports values with those of another report working fine with exact same data source...

I hope engineering will pay attention to this post because this happens whether I use the Save as... -> Report Server or simply upload the report's .pbix from the file system.

For reasons I cannot explain, the problem report gets the following columns in table dbo.DataModelDataSource set with the following values:

,[DSType] = 'Direct Query'
,[DSKind] = 'SQL'
,[AuthType] = 'Integrated'

,[Username] = <some hex DIFFERENT from the report working fine>
,[Password] = <some hex DIFFERENT from the report working fine>

As soon as I issued the following SQL UPDATE statement to configure the broken report like the working one, it started working fine:

UPDATE [dbo].[DataModelDataSource]
SET [ItemId] = '44B78E67-C3D6-4A28-B559-DE486CAA3701'
,[DSType] = 'Import'
,[DSKind] = 'SQL'
,[AuthType] = 'Windows'
,[Username] = <the EXACT hex value from the report working fine>
,[Password] = <the EXACT hex value from the report working fine>
WHERE DSID=15
GO

What is STRANGE is that the broken report was NOT in Direct Query, but rather in Import mode. At least not in the version I published or uploaded, but it *MAY* have started in Direct Query mode and then I *MAY* have switched it to Import to see if performance improved (It did because the data volumes were small enough and the sources were slow). My memory is fuzzy on this... But this *MIGHT* mean that the "DirectQuery: Enabled (Click to change)" feature in Power BI Desktop optimized for Power BI Report Server (from the French download section at least) is not changing what gets written in the metadata correctly.

Another potential cause for this problem *MIGHT* be that we elected to name PBIRS & PBIRSTempDB the metadata and temp DBs for PBIRS, instead of the standard / default names (ReportServer & ReportServerTemp I think)...

Before I found these slight differences in the metadata, I foolishly tried different things as potential fixes (none of which helped):

1) Since my report name was longer than the other working one with the same data source, I tried saving the report under a shorter name.

2) Since my report was using many more views and tables than the other working one with the same data source, I tried breaking the report in seperate ones each using fewer views and tables.

3) Since these problems surfaced right after upgrading from the August 2018 of PBIRS straight to the May 2019 version without ever trying the January 2019, I tried wiping PBIRS and the SQL Server and Database used exclusively for the PBIRS Metadata (not the actual data sources of the reports) and reinstalling them from scratch.

In hope this will help indentify and resolv this more properly, here's the diagnostics info from the About page of PBI Desktop:

Feedback Type:
Frown (Error)

Timestamp:
2019-05-31T14:18:45.1764202Z

Local Time:
2019-05-31T10:18:45.1764202-04:00

Session ID:
34e69780-06f2-4626-84d2-f90256421629

Release:
May, 2019

Product Version:
2.69.5467.1801 (19.05) (x64)

OS Version:
Microsoft Windows NT 6.1.7601 Service Pack 1 (x64 fr-FR)

CLR Version:
4.7 or later [Release Number = 461814]

Peak Virtual Memory:
3.98 GB

Private Memory:
505 MB

Peak Working Set:
699 MB

IE Version:
11.0.9600.18977

User ID:
e98dba29-110a-4419-af9c-e0d4bae49e61

Workbook Package Info:
1* - fr-CA, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\turcmart\AppData\Local\Microsoft\Power BI Desktop SSRS\FrownSnapShot1598656874.zip

Performance Trace Logs:
C:\Users\turcmart\AppData\Local\Microsoft\Power BI Desktop SSRS\PerformanceTraces.zip

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_NewWebTableInference
PBI_showIncrementalRefreshPolicy
PBI_showManageAggregations
PBI_improvedFilterExperience
PBI_qnaLiveConnect
PBI_keyDrivers

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
125%

Supported Services:
Power BI Report Server, Power BI

Formulas:

section Section1;

shared vEvolutionEncours_TC = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vEvolutionEncours_TC = WAREHOUSE{[Schema="dbo",Item="vEvolutionEncours_TC"]}[Data]
in
dbo_vEvolutionEncours_TC;

shared fnSTAT002Tableau1 = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_fnSTAT002Tableau1 = WAREHOUSE{[Schema="dbo",Item="fnSTAT002Tableau1"]}[Data],
#"Fonction appeléedbo_fnSTAT002Tableau1" = dbo_fnSTAT002Tableau1(),
#"Colonnes renommées" = Table.RenameColumns(#"Fonction appeléedbo_fnSTAT002Tableau1",{{"EncoursGlobal", "EncoursTotal"}})
in
#"Colonnes renommées";

shared fnSTAT002Tableau2 = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_fnSTAT002Tableau2 = WAREHOUSE{[Schema="dbo",Item="fnSTAT002Tableau2"]}[Data],
#"Fonction appeléedbo_fnSTAT002Tableau1" = dbo_fnSTAT002Tableau2()
in
#"Fonction appeléedbo_fnSTAT002Tableau1";

shared fnSTAT002Tableau3 = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_fnSTAT002Tableau3 = WAREHOUSE{[Schema="dbo",Item="fnSTAT002Tableau3"]}[Data],
#"Fonction appeléedbo_fnSTAT002Tableau1" = dbo_fnSTAT002Tableau3()
in
#"Fonction appeléedbo_fnSTAT002Tableau1";

shared vSTAT002TableauECEnregProduit = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002TableauECEnregProduit = WAREHOUSE{[Schema="dbo",Item="vSTAT002TableauECEnregProduit"]}[Data]
in
dbo_vSTAT002TableauECEnregProduit;

shared vSTAT002NombreClients = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002NombreClients = WAREHOUSE{[Schema="dbo",Item="vSTAT002NombreClients"]}[Data]
in
dbo_vSTAT002NombreClients;

shared vSTAT002ClientsParRegion = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002ClientsParRegion = WAREHOUSE{[Schema="dbo",Item="vSTAT002ClientsParRegion_2"]}[Data]
in
dbo_vSTAT002ClientsParRegion;

shared vSTAT002ClientsParStrate = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002ClientsParStrate = WAREHOUSE{[Schema="dbo",Item="vSTAT002ClientsParStrate"]}[Data]
in
dbo_vSTAT002ClientsParStrate;

shared vSTAT002NouvClientsParStrate = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002NouvClientsParStrate = WAREHOUSE{[Schema="dbo",Item="vSTAT002NouvClientsParStrate_2"]}[Data]
in
dbo_vSTAT002NouvClientsParStrate;

shared vSTAT002NouvClientsParRegion = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002NouvClientsParRegion = WAREHOUSE{[Schema="dbo",Item="vSTAT002NouvClientsParRegion_2"]}[Data]
in
dbo_vSTAT002NouvClientsParRegion;

shared vSTAT002ClientsParSexe = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002ClientsParSexe = WAREHOUSE{[Schema="dbo",Item="vSTAT002ClientsParSexe"]}[Data]
in
dbo_vSTAT002ClientsParSexe;

shared vSTAT002NouvClientsParSexe = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002NouvClientsParSexe = WAREHOUSE{[Schema="dbo",Item="vSTAT002NouvClientsParSexe_2"]}[Data]
in
dbo_vSTAT002NouvClientsParSexe;

shared vSTAT002ClientsParLangue = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002ClientsParLangue = WAREHOUSE{[Schema="dbo",Item="vSTAT002ClientsParLangue"]}[Data]
in
dbo_vSTAT002ClientsParLangue;

shared vSTAT002NouvClientsParLangue = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002NouvClientsParLangue = WAREHOUSE{[Schema="dbo",Item="vSTAT002NouvClientsParLangue_2"]}[Data]
in
dbo_vSTAT002NouvClientsParLangue;

shared vSTAT002NombreNouvClients = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002NombreNouvClients = WAREHOUSE{[Schema="dbo",Item="vSTAT002NombreNouvClients_2"]}[Data]
in
dbo_vSTAT002NombreNouvClients;

shared vSTAT002Clients = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002Clients = WAREHOUSE{[Schema="dbo",Item="vSTAT002Clients"]}[Data]
in
dbo_vSTAT002Clients;

shared vSTAT002NouvClients = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002NouvClients = WAREHOUSE{[Schema="dbo",Item="vSTAT002NouvClients"]}[Data]
in
dbo_vSTAT002NouvClients;

shared vSTAT002ClientsStrateEncours = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002ClientsStrateEncours = WAREHOUSE{[Schema="dbo",Item="vSTAT002ClientsStrateEncours"]}[Data],
#"Lignes triées" = Table.Sort(dbo_vSTAT002ClientsStrateEncours,{{"Ordre", Order.Ascending}}),
#"Colonnes renommées" = Table.RenameColumns(#"Lignes triées",{{"Encours", "Portefeuille"}, {"EncoursTotal", "PortefeuilleTotal"}, {"Ordre", "#"}})
in
#"Colonnes renommées";

shared vSTAT002NCparProduit = let
Source = Sql.Databases("EPPV-SQL12-2C\EPQDW_PROD"),
WAREHOUSE = Source{[Name="WAREHOUSE"]}[Data],
dbo_vSTAT002NCparProduit = WAREHOUSE{[Schema="dbo",Item="vSTAT002NCparProduit_2"]}[Data],
#"Colonnes renommées" = Table.RenameColumns(dbo_vSTAT002NCparProduit,{{"OrdreAffichage", "#"}})
in
#"Colonnes renommées";

[Anonymous]

This toppic solved my issue regarding swapping a report from direct query to Import mode. On our local report server.

I got exactly the error Couldn't connect Unsupported keyword.

Yes you can change a datasource from direct query to import in the Power-BI desktop designer.

but if you save it with the same name in the same folder. The server is messing things up and keeping old values.

Yes you can build a new report then and save it with a new name.

but I found this to be a little bit quicker

First as mentioned

UPDATE [dbo].[DataModelDataSource]
SET [DSType] = 'Import'
,[DSKind] = 'SQL'
,[AuthType] = 'Windows'
where dsid =  [do not forget the where clause, or the backup]

but then also the catalog property needs to be changed

Update [ReportServer].[dbo].[Catalog]
set Property = '<Properties>
<IsMobileOptimized>False</IsMobileOptimized>
<HasEmbeddedModels>True</HasEmbeddedModels>
<PbixShredderVersion>3</PbixShredderVersion>
<ModelRefreshAllowed>True</ModelRefreshAllowed>
<HasDirectQuery>False</HasDirectQuery>
</Properties>'
where itemid = [do not forget the where clause, or the backup]

After this you can insert credentials for a datasource, and create a schedule for datarefresh on the local report server

[ianmanton]

This was driving me scatty - thanks for saving some of my hair!

[ianmanton]

That said - every time the report is pushed again the funky metadata returns and breaks the scheduled refresh....

[ianmanton]

@v-yulgu-msft 

[mturcotte_epq]

Thank you for your workaround completement!

It really helped.

Without it, my report would work but I remained unable to schedule a data refresh for it.

With this additional correction to the Property column in the Catalog table, it helped restore the ability to create and update a scheduled refresh on the report.

I just hope that Microsoft has a proper fix in the plans for the next release of PBIRS (isn't a new version due this month?) and Power BI Desktop for Report Server. I have not seen any reply from Microsoft on this thread, so I fear it might not be acknowledged or scheduled to be corrected.

Thanks again!