Hello Dundas BI experts,
We need to use Azure Synapse as a data source in Dundas BI. When we connect to the build-in SQL pool, we get the error “PROCEDURE ‘sp_columns_managed’ is not supported.”
Is there any special requirements for Synapse configuration to work with Dundas BI?
Does Dundas BI treats Azure Synapse as a regular SQL Server?
Regards,
Dmitri
Good Day Dmitri,
Yes, Dundas BI treats Azure Synapse as a Microsoft SQL Server. Try using the Microsoft SQL Server data connector and let us know it goes.
I have included a link to a dundas.com/support site article which mentions this.
Look at the note below section 1.
Connecting to Azure
Cheers.
Hello Lota,
We added new connection and connection test runs successfully. In the Explorer, we see a DB table, but when we try to expand the table, or add this table to the dashboard, we see now this error:
PROCEDURE ‘sp_describe_parameter_encryption’ is not supported.
The technical details show next
Web response code: 503 error (System.Data.SqlClient.SqlException)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.TryFetchInputParameterEncryptionInfo(Int32 timeout, Boolean async, Boolean asyncWrite, Boolean& inputParameterEncryptionNeeded, Task& task, ReadOnlyDictionary2& describeParameterEncryptionRpcOriginalRpcMap)
at System.Data.SqlClient.SqlCommand.PrepareForTransparentEncryption(CommandBehavior cmdBehavior, Boolean returnStream, Boolean async, Int32 timeout, TaskCompletionSource1 completion, Task& returnTask, Boolean asyncWrite, Boolean& usedCache, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at ᗰᘛᓴᖧᙾᓞᕫᙔᓍᕂ.d__5.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
at Dundas.BI.Data.DataProviders.Relational.SqlServerProvider.ᙉ᐀ᔲᐻᘞᐣᑫᖮᖢᖏ(SqlConnection connection, String schemaName, String structureName, RelationalStructure table)
at Dundas.BI.Data.DataProviders.Relational.SqlServerProvider.DiscoverStructure(IDisposable connection, SupportedDataStructures structureType, String structureName)
at ᙴᑨᔐᕳᓩᒪᘭᖗᘽᐦ.ᙣᕻᐂᕫᗵᔶᘹᖀᕿᔻ(Guid id, DiscoveryType discoveryType, ᙴᑨᔐᕳᓩᒪᘭᖗᘼᙳ options)
BTW, we run Dundas BI Version: 7.0.2.1006
Hello Dmitri,
I see, in both errors you had, the issues were with the stored procedures( ‘sp_describe_parameter_encryption’ and ‘sp_columns_managed’)not being supported.
Let’s get some additional information to troubleshoot this.
Both stored procedures are system ones.
sp_describe_parameter_encryption (Transact-SQL) - SQL Server | Microsoft Docs
I can’t find formal description for sp_columns_managed, but it is listed system
All this happens for data source connected to “Built-in” serverless SQL Pool in the Synapse namespace.
I see, and this happens whenever you drag in any table from the database?
Yes, when dragging the table. Also, if I go to “Explore Data” and select Synapse data source, the error message is next:
Unable to build the ‘Columns’ collection because execution of the SQL query failed. See the inner exception for details.
The technical details are next
Web response code: 503 error (System.InvalidOperationException)
at System.Data.ProviderBase.DbMetaDataFactory.ExecuteCommand(DataRow requestedCollectionRow, String[] restrictions, DbConnection connection)
at System.Data.ProviderBase.DbMetaDataFactory.GetSchema(DbConnection connection, String collectionName, String[] restrictions)
at System.Data.SqlClient.SqlConnection.GetSchema(String collectionName, String[] restrictionValues)
at Dundas.BI.Data.DataProviders.Relational.SqlServerProvider.ᙉ᐀ᔲᐻᘞᐣᑫᖮᖢᖏ(SqlConnection connection, String schemaName, String structureName, RelationalStructure table)
at Dundas.BI.Data.DataProviders.Relational.SqlServerProvider.DiscoverStructure(IDisposable connection, SupportedDataStructures structureType, String structureName)
at ᙴᑨᔐᕳᓩᒪᘭᖗᘽᐦ.ᙣᕻᐂᕫᗵᔶᘹᖀᕿᔻ(Guid id, DiscoveryType discoveryType, ᙴᑨᔐᕳᓩᒪᘭᖗᘼᙳ options)
Hello Dmitri,
Currently looking into this. Will get back to you as soon as I get a solution.
Please do you mind sending in an email to support@dundas.com so this issue can be tracked more efficiently? Please reference Issue Number 89423 in the email, and a subject line of “Azure Synapse Data Source” will suffice.
Done. I’ve sent an email.
Thank you very much. Will get back to you on this.
Good Day Dmitri,
Quick update. Work is still ongoing on this. I’ll keep you updated as things go along.
Good Day Dmitri.
Regarding the issue you are facing, it turns out it has been fixed in Version 8. Are you open to upgrading your instance and giving it another try?
Hello Lota,
We are going to update our Dundas BI instance and try again.
Side question. Is Version 9 coming soon?
FYI.
We tried Dundas BI V8 and we see exactly the same error. Dundas BI is able to connect to Synapse’s serverless SQL pool and show DB and table in the DB. Any other operations (e.g. Explore Data) fail.
Serverless SQL pool - Azure Synapse Analytics | Microsoft Docs
Hello Dmitri,
Yes v9 will be released soon( some weeks from now).
Regarding the error, I am looking further into this and will get back to you.
Please can you share your current version, complete the minor release number.
