Direct query mode ON in Tabular Model Issue – Visual Studio

Direct query mode ON Error Message “Unable to save the file Model.bim. Reason: The file cannot be saved because it is marked as read only.”

Suppose you are creating tabular model using SSDT Tools in Visual Tools and you want to save or edit the model whenever the new data is updates. But to do so we have one option in the visual studio stating Direct Query Mode = ON/OFF to make this feature implemented in visual studio. Currently it is just supported for SQL Server auth and Data Base auth, if you use MFA Auth then the tabular solution explorer crash and will not be editable or make any changes.

My Environment Details:

  • Tabular Compatibility Level Tried: 1200/1400/1500/1600.
  • When we create new project solution, we get option to select Workspace “Integrated Workspace/Workspace server”. Tried both method but same result.
  • Using the latest version of Visual Studio 2022.
  • I am using Azure SQL Server for MFA and also tried with Azure Synapse Serverless SQL endpoint MFA.
  • I have SQL Authentication enabled for both above mentioned SQL Servers.

Direct query mode ON Issue:

I am able to edit/save the model with Direct query mode set as OFF with no problem but getting below error message when we change Direct query mode is ON

When I set authentication method as Database I don’t have any issue with Direct Query mode value setting to ON/OFF.

Problem happens only if authentication method is set to MFA/Microsoft(2) in the Data source level as you can see in the below Image. Refer Below Image.

Buy when I set the authentication method for the same data source to “Data Base (1)” it works fine without issue. Refer Below Image.

Direct query mode ON

Detailed:

In the below image/screenshot we can see in the model properties that DirectQueryMode is “OFF” in model.bim properties. So, I am able to save or edit the project/Solution/model without any issue.

Direct query mode ON
Note: The issue only occurs when Data Source authentication is set to  Microsoft or MFA as discussed in the above points. But when we set the authentication to SQL or Data Base works fine without error.

In below image when I turned “ON” the DirectQurryMode and try to save or try to do any kind of modification in the Project/solution I am getting this below error.

Direct query mode ON
Error Message: Unable to save the file Model.bim. Reason: The file cannot be saved because it is marked as read only

What to do:

It is expected behavior for all the users. So, for time being we can use SQL Server authentication.

It is currently bug in Visual Studio.

Might be it is expected behavior in visual studio.

There might be some other workaround for this, but I am not aware how to enable.

Once this issue or workaround is fixed, we will post below.

For more details you can see this article referred by Microsoft: https://learn.microsoft.com/en-us/analysis-services/tabular-models/directquery-mode-ssas-tabular?view=asallproducts-allversions

SQL Bi Links:

  1. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
  2. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
  3. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  4. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
  5. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
  6. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
  7. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
  8. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
  9. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  10. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
  11. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
  12. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
  13. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
  14. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
  15. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)
Vickey Rajpoot
Vickey Rajpoot

Hello there! I'm dedicated Microsoft Data & AI Engineer at LTIMindtree, where I thrive technology into actionable insights. Dive into my world by visiting my YouTube channel & Webiste, "Kingfisher Tech Tips".

Articles: 71

Leave a Reply

Your email address will not be published. Required fields are marked *