Unable to connect to SQL Server 2014 in Direct Query Mode

[mcudogs]

Hi,

I have tried to connect to an on premises MS SQL server 2014 in Direct Query mode and get the following error.

When I connect in Import mode it works fine. We need live data for this report. Is there a fix for this?

Mcudogs

Feedback Type:
Frown (Error)

Timestamp:
2017-08-17T00:21:41.5837657Z

Local Time:
2017-08-17T10:21:41.5837657+10:00

Product Version:
2.49.4831.521 (PBIDesktop) (x86)

Release:
August, 2017

IE Version:
11.0.9600.18738

OS Version:
Microsoft Windows NT 6.1.7601 Service Pack 1 (x64 en-US)

CLR Version:
4.6.1 or later [Release Number = 394271]

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

Peak Working Set:
498 MB

Private Memory:
443 MB

Peak Virtual Memory:
1.22 GB

Error Message:
Couldn't load the model schema.

User ID:
be4e5e95-df02-4aa3-aa20-8926546fece2

Session ID:
008d371b-7782-412f-b12f-92edc940e0d3

Telemetry Enabled:
True

Model Default Mode:
DirectQuery

Snapshot Trace Logs:
C:\Users\namedeleted\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1674684441.zip

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

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_daxTemplatesEnabled
CustomConnectors

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted

Cloud:
GlobalCloud

Couldn't load the schema for the database model:
Couldn't load the model schema associated with this report. Make sure you have a connection to the server, and try again.

Activity ID:
null

Time:
Thu Aug 17 2017 10:21:29 GMT+1000 (E. Australia Standard Time)

DPI Scale:
100%

Supported Services:
Power BI

Formulas:

section Section1;

shared #"pxrptuser PROJECT_FULL" = let
    Source = Sql.Database("ICTDEVSQL105:56420", "QRPMDB", [CreateNavigationProperties=false]),
    pxrptuser_PROJECT_FULL = Source{[Schema="pxrptuser",Item="PROJECT_FULL"]}[Data]
in
    pxrptuser_PROJECT_FULL;

[v-qiuyu-msft]

Hi @mcudogs,

It seems that you get data from PROJECT_FULL table in QRPMDB database in DirectQuery. Please try to get data from the same table in Import mode to let me know if the same issue happen.

If the issue only happens in DirectMode for the same table, please share desktop trace log files with us.

Best Regards,
QiuyunYu

[mcudogs]

Hi,

It works in import mode but not in Direct Query.

How do you upload the files?

Mcudogs

[mcudogs]

Here is a link to the files on dropbox

https://www.dropbox.com/sh/lxfi7xjro8eaq8u/AAAs383p42cfSgOPYCy6h41ma?dl=0

[v-qiuyu-msft]

Hi @mcudogs,

From the log, I found the error message:

PublicKeyToken=b77a5c561934e089\r\nMessage: A connection was successfully established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 - The received certificate has expired.)

When you get data in DirectQuery mode, do you enable the Encrypt connections? Please go to Data Source settings -> Edit Permission, uncheck this option.

Best Regards,
QiuyunYu

[mcudogs]

Hi,

When I first started with Power BI I could connect to an sql server data source and it would advise me that the server does not accept secure connections and then ask me to connect in a non secure mode.

Now when I try to connect to the server it no longer tells me that the server does not accept secure connections and the connection just fails and will not let me save it. If I then go to data source settings I can see the source and then edit permissions and turn off encrypt connections. However, I then still cannot use the data source.

Regards

Mcudogs

[mcudogs]

If I use the import option with this data source it works ok no issues at all so it is not caused by the secure connection setting.

Regards

Mcudogs

[v-qiuyu-msft]

Hi @mcudogs,

Please create a support ticket to get dedicated support:

Best Regards,
QiuyunYu