Connecting to Microsoft Azure Analysis Services using TIBCO Spotfire connector for Microsoft SQL Server Analysis Services fails with error "Input String was not in a correct format"

Connecting to Microsoft Azure Analysis Services using TIBCO Spotfire connector for Microsoft SQL Server Analysis Services fails with error "Input String was not in a correct format"

When connecting to Microsoft Azure Analysis Services connection URL of format "asazure://..." using the TIBCO Spotfire connector for Microsoft SQL Server Analysis Services and ADOMD.NET Driver for SQL Server 2016, 2014 or 2012 installed on the machine, it may fail with the following error:

2020-01-22T10:23:35,315-06:00 2020-01-22 16:23:35,315 00udwi0wiLBWVtPjH356 [45] DEBUG Spotfire.Dxp.Data.Adapters.Ssas.SsasAdapterConnection [(null)] - Get databases
Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection string is not valid. ---> System.FormatException: Input string was not in a correct format.
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetInstancePort(ConnectionInfo connectionInfo)
   --- End of inner exception stack trace ---
   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
   at Spotfire.Dxp.Data.Adapters.Ssas.SsasAdapterConnection.GetDatabases(AdomdOdboConnectionSettings connectionSettings, AdapterCredentialsValue credentials, String[]& databases)
2020-01-22T10:23:35,390-06:00 2020-01-22 16:23:35,390 00udwi0wiLBWVtPjH356 [Main Thread] ERROR Spotfire.Dxp.Forms.Data.Access.AsynchronousTaskManager [(null)] - Background task failed.
The data source does not exist or it does not respond.
External error:
"The connection string is not valid."
which is caused by "Input string was not in a correct format." Some of the ADOMD.NET drivers that are available on the Microsoft's Download Center (eg: https://www.microsoft.com/en-us/download/details.aspx?id=52676) are not configured to parse the server URL of format "asazure://....." but NuGet, which is package manager for Microsoft development platform, has the latest drivers listed that can parse Microsoft Azure Analysis Services URLs correctly. To resolve, you can download and install the latest package from NuGet:

Method 1:

1. Download the latest Microsoft ADOMD.NET from https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.retail.amd64/18.2.3 by clicking on 'Download package'.

2. Now install the package using the command available under '.NET CLI'.

3. Reopen TIBCO Spotfire Installed Client



If dotnet core (which is needed to run the above command) is not installed on the machine, follow method 2 which is replacing the dll as shown below.


Method 2:

1. Download and install SQL_AS_ADOMD.msi from https://www.microsoft.com/en-us/download/details.aspx?id=52676

2. Download the latest Microsoft ADOMD.NET from https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.retail.amd64/18.2.3 by clicking on 'Download package'.

3. Change the extension of the NuGet package to 'zip' and extract that zip file.

4. Replace 'Microsoft.AnalysisServices.AdomdClient.dll' available under "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.AnalysisServices.AdomdClient\v4.xxxx" with the 'Microsoft.AnalysisServices.AdomdClient.dll' present under "NuGet Folder\lib\netxx"

5. Reopen TIBCO Spotfire Installed Client

Note: 18.2.3 is latest version available currently available on NuGet. Please install the newer version if available while downloading Doc: Details on Microsoft SQL Server Analysis Services Connector

• https://docs.tibco.com/pub/sfire-analyst/10.3.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/connectors/sqlas/sqlas_details_on_microsoft_sql_server_analysis_services_connection.htm

• https://www.nuget.org/packages/Microsoft.AnalysisServices.AdomdClient.retail.amd64/18.2.3

• https://www.microsoft.com/en-us/download/details.aspx?id=52676