SQL Server connection with DirectQuery

[josemariaverde]

Hi,

I am trying to create a dashboard that connects to a SQL Server database with DirectQuery mode. I have no problem connecting to such database in DBeaver as well as with the import mode in Power BI.

The problem comes when I try to do it with the DirectQuery mode. After logging with my database credentials in Power BI, seeing a list of all the tables and picking one of them, it starts loading that table. After some time, it returns a message saying it was not possible to connect to the data model or to the DirectQuery data source. When clicking on details it returns the following:

Feedback Type:
Frown (Error)

Error Message:
No se pudo conectar al modelo interno o al origen de datos de DirectQuery. Compruebe que los nombres de servidor y base de datos son correctos y que tiene permiso para acceder a ellos.

Stack Trace:
Host:
   en Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent)
   en Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass10_1.<GetModelMetadata>b__1(ExploreBaseEvent timedEvent)
   en Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.<>c__DisplayClass4_0`1.<RunInActivity>b__0(ExploreBaseEvent activity)
   en Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.RunInActivity(String activityName, Action`1 action)
   en Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.RunInActivity[T](String activityName, Func`2 action)
   en Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.GetModelMetadata(ModelMetadataRequest request)
   en Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.GetConceptualSchema(ModelMetadataRequest request, ConceptualSchemaBuilderOptions builderOptions, ParseConceptualSchema parse)
   en Microsoft.PowerBI.ReportingServicesHost.ReportingSession.GetConceptualSchema(String modelMetadataVersion, Nullable`1 translationsBehavior, ParseConceptualSchema parse)
   en Microsoft.PowerBI.ExploreHost.Utils.ExploreHostUtils.<>c__DisplayClass34_0.<GetConceptualSchema>b__0()
   en Microsoft.PowerBI.ExploreHost.Utils.ExploreHostUtils.HandleModelRetrievalExceptions[T](Func`1 wrappedCall, ServiceErrorStatusCode statusCode)
   en Microsoft.PowerBI.ExploreHost.Utils.ExploreHostUtils.GetConceptualSchemaAndHandleExceptions(Func`1 getConceptualSchema, String maxModelMetadataVersion, ModelDaxCapabilities& daxCapabilities)
   en Microsoft.PowerBI.ExploreHost.Utils.ExploreHostUtils.GetConceptualSchema(IPowerViewHandler powerViewHandler, String databaseID, String maxModelMetadataVersion, Nullable`1 translationsBehavior, ModelDaxCapabilities& daxCapabilities)
   en Microsoft.PowerBI.ExploreHost.Utils.ExploreHostUtils.<>c__DisplayClass27_0.<TryGetConceptualSchema>b__0()
   en Microsoft.PowerBI.ExploreHost.Utils.ExploreHostUtils.TryGetStructureImpl[T](Int64 modelID, ServiceErrorExtractor extractor, Func`1 getStructure, Func`2 validateStructure, T& structure, ServiceError& serviceError)

Javascript&colon;ShowErrorDialog
 at ErrorHostService.showErrorDialogCore(args)

Invocation Stack Trace:
   en Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   en Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
   en Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
   en Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
   en Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
   en Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
   en Microsoft.PowerBI.Client.PowerBIUnexpectedExceptionHandler.HandleException(Exception e)
   en Microsoft.PowerBI.Client.Windows.Utilities.PowerBIFormUnexpectedExceptionHandler.HandleException(Exception e)
   en Microsoft.PowerBI.Client.Windows.ErrorHostService.<>c__DisplayClass6_0.<ShowErrorDialogCore>b__0()
   en System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   en System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   en System.Delegate.DynamicInvokeImpl(Object[] args)
   en System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
   en System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
   en System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
   en System.Windows.Forms.Control.InvokeMarshaledCallbacks()
   en System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
   en System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
   en System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state)
   en Microsoft.PowerBI.Client.Windows.Services.UIBlockingService.AllowModalDialogs(Action action)
   en Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.<>c__DisplayClass14_0.<ExecuteOnUIThreadAndHandlePromise>b__0()
   en Microsoft.PowerBI.Client.Windows.HostServiceDispatcher.ExecuteOnUIThreadAndHandlePromise[T](Func`1 func, IPromiseStore promiseStore, Int64 promiseHandle)
   en System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   en System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   en System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   en Microsoft.PowerBI.Client.Windows.WebView2.WebView2Interop.InvokeCs(InteropCall call)
   en Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   en System.EventHandler`1.Invoke(Object sender, TEventArgs e)
   en System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   en System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   en System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   en System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   en System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   en System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
   en Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
   en Microsoft.PowerBI.Client.Program.<>c__DisplayClass6_0.<RunApplication>b__0()
   en Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
   en Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   en Microsoft.PowerBI.Client.Program.RunApplication(String[] args)
   en Microsoft.PowerBI.Client.Program.Main(String[] args)


PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.122.746.0","ModuleName":null,"Component":"Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass10_1","Error":null,"MethodDef":"<GetModelMetadata>b__1","ErrorOffset":"","ErrorCode":""}

Snapshot Trace Logs:
C:\Users\user\Microsoft\Power BI Desktop Store App\FrownSnapShot01436f31-993f-4f05-aa2e-ef7bd5beb9dc.zip

Model Default Mode:
DirectQuery

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\user\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_sparklines
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_setLabelOnExportPdf
PBI_dynamicFormatString
PBI_oneDriveSave
PBI_oneDriveShare
PBI_newCard

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_angularRls
PBI_onObject
PBI_backstageUI
PBI_gitIntegration
PBI_modelExplorer

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

PowerBIUserFeedbackServices_IsReported:
True

Formulas:


section Section1;

shared #"schema_name table_name" = let
    Origen = Sql.Database("IP:PORT", "master"),
    table_name = Origen{[Schema="schema_name",Item="table_name"]}[Data]
in
    table_name;

Can someone please give me some help? Thanks a lot in advance!

[gerardolalo4]

I´m having the same issue right now. Did you solve it?

[Data-estDog]

Can you connect to the SQL server database and query the view/table in question with SSMS?

What version of SQL Server is it?  "SELECT @@Version 

[josemariaverde]

Yes, I can connect to that table in SSMS.

The version is:

Microsoft SQL Server 2022 (RTM-CU9) (KB5030731) - 16.0.4085.2 (X64) Sep 27 2023 12:05:43 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>