Problems reading from Synapse SQL On-Demand #582
Comments
|
Since you mentioned that this is an on-demand SQL pool, I assume that the tables shown in the screenshot above are actually external tables, that point to a file in some storage account? If that's indeed the case, could you check whether the SQL account you used (SERVICEAUSERWITHACCESS) also has access to these files? As documented in this article, permission is needed at two levels: Storage AND SQL service level. The fact that Tabular Editor's Import Table Wizard can enumerate the list of tables on the SQL pool, indicates that you have proper access at the service level - but the crash that occurs when attempting to import the table (in which Tabular Editor tries to execute a schema-only SELECT * FROM ... against the table), indicates that the account does not have permission to access the storage level. |
|
I wonder if you are right. It might be. |
|
I wrote a short description of how to use MFA with the Import Table Wizard - it requires you to manually set up the data source using the MSOLEDBSQL provider. This works for both Azure SQL DB and Synapse SQL pools. There's a note in the text about setting the row reduce clause to "TOP (without NOLOCK)". This is available in version 2.12.1 of Tabular Editor which will be released shortly. But again, remember that even with MFA and the aforementioned clause, a user may view the list of tables and views in the SQL pool, but they won't be able to preview the data or import the table schema unless they also have access to the underlying storage account. |
|
Closing this (see new release 2.12.1), but feel free to open a new issue if you are still facing difficulties loading data from Synapse SQL pools. |
I've added a legacy data source for my SQL On-Demand end-point in Azure Synapse.
The connection string goes something like this:
When I right click the data source, and start the Import Tables-dialog, the tables in the database pop up for selection. But if I try to load a table, I get an exception (see dump below).
Seeing as I am able to see the tables, I suppose my connection works. So what is then the reason why I can't get at my data?
If I use a Power Query data source, the same table does allow data load.
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
at TabularEditor.UI.Dialogs.ImportTablesWizard.DoImport(ImportMode importMode, Model model, TypedDataSource source, IEnumerable
1 schemaNodes, RowLimitClause rowLimitClause, IdentifierQuoting identifierQuoting) at TabularEditor.UI.Dialogs.ImportTablesWizard.ShowWizard(Model model, ProviderDataSource source) at TabularEditor.UI.Actions.ModelActionManager.<>c.<CreateStandardActions>b__13_7(UITreeSelection s, Model m) at TabularEditor.UI.Actions.Action.InternalExecute(Object arg, IEnumerable1 alternateSelection)at TabularEditor.UI.Actions.Action.Execute(Object arg)
at TabularEditor.UI.UIController.ContextMenuItem_Click(Object sender, EventArgs e)
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
************** Loaded Assemblies **************
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3640.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
TabularEditor
Assembly Version: 2.11.7499.21291
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3580.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
System
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3640.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
TOMWrapper
Assembly Version: 2.11.7499.21289
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3640.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
Microsoft.AnalysisServices.Core
Assembly Version: 19.4.0.2
Win32 Version: 15.1.44.24
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/Microsoft.AnalysisServices.Core.DLL
System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
System.Data
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3630.0 built by: NET472REL1LAST_B
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll
Microsoft.AnalysisServices.Tabular
Assembly Version: 19.4.0.2
Win32 Version: 15.1.44.24
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/Microsoft.AnalysisServices.Tabular.DLL
Microsoft.AnalysisServices.Tabular.Json
Assembly Version: 19.4.0.2
Win32 Version: 15.1.44.24
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/Microsoft.AnalysisServices.Tabular.Json.DLL
System.Xml.Linq
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Xml.Linq/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.Linq.dll
Newtonsoft.Json
Assembly Version: 10.0.0.0
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
System.Design
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Design/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Design.dll
FastColoredTextBox
Assembly Version: 2.16.24.0
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
Crad.Windows.Forms.Actions
Assembly Version: 1.3.0.0
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
Antlr4.Runtime
Assembly Version: 4.5.0.0
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
System.Numerics
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll
System.Runtime.Serialization
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3620.0 built by: NET472REL1LAST_B
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Runtime.Serialization/v4.0_4.0.0.0__b77a5c561934e089/System.Runtime.Serialization.dll
va3cvzau
Assembly Version: 0.0.0.0
Win32 Version: 4.7.3640.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3630.0 built by: NET472REL1LAST_B
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
Microsoft.WindowsAPICodePack
Assembly Version: 1.1.2.0
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
System.Web
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3650.0 built by: NET472REL1LAST_B
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Web/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Web.dll
SMDiagnostics
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3620.0 built by: NET472REL1LAST_B
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/SMDiagnostics/v4.0_4.0.0.0__b77a5c561934e089/SMDiagnostics.dll
System.ServiceModel.Internals
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3620.0 built by: NET472REL1LAST_B
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.ServiceModel.Internals/v4.0_4.0.0.0__31bf3856ad364e35/System.ServiceModel.Internals.dll
Microsoft.IdentityModel.Clients.ActiveDirectory
Assembly Version: 2.29.0.1078
Win32 Version: 15.1.44.24
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/Microsoft.AnalysisServices.Core.dll
Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms
Assembly Version: 2.29.0.1078
Win32 Version: 15.1.44.24
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/Microsoft.AnalysisServices.Core.dll
Microsoft.mshtml
Assembly Version: 7.0.3300.0
Win32 Version: 7.0.3300.0
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.mshtml/7.0.3300.0__b03f5f7f11d50a3a/Microsoft.mshtml.dll
System.Runtime.Caching
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Runtime.Caching/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Runtime.Caching.dll
System.Data.OracleClient
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Data.OracleClient/v4.0_4.0.0.0__b77a5c561934e089/System.Data.OracleClient.dll
System.Transactions
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3260.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll
System.EnterpriseServices
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.EnterpriseServices/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
Accessibility
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/Accessibility/v4.0_4.0.0.0__b03f5f7f11d50a3a/Accessibility.dll
Microsoft.Data.ConnectionUI.Dialog
Assembly Version: 0.0.0.0
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
Microsoft.Data.ConnectionUI
Assembly Version: 0.0.0.0
Win32 Version: 2.11.7499.21291
CodeBase: file:///C:/Program%20Files%20(x86)/Tabular%20Editor/TabularEditor.exe
System.Data.DataSetExtensions
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Data.DataSetExtensions/v4.0_4.0.0.0__b77a5c561934e089/System.Data.DataSetExtensions.dll
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
For example:
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
The text was updated successfully, but these errors were encountered: