1

On my company local SQL Server instance I was able to add a linked server to a remote AS instance (in Management Studio > Server Objects). I was also able to connect using Data Tools, as well with Excel. In these clients I am able to run custom DMX queries

The issues that I face are: - In SSMS linked server, the explorer shows only dimension tables, no measures. - Same in Visual Studio Data Tools (no measures are displayed in the explorer UI), except that here I also see "columns" for each table (while in SSMS tables don't show any lower level object to explore).

While in Excel OLAP pivot table I see all dimensions and measures, I therefore have access to all the cube's objects - or at least the ones allowed for the login used in the connection string.

For all of the above mentioned connections, I successfully used the same string, which is something like:

Provider=MSOLAP.6;Data Source=biserver.domain.com:1564;Persist Security Info=True;
User ID=myusername;Password=myPassword;Initial Catalog="MyCatalogue Name";
Location=biserver.domain.com:1564;MDX Compatibility=1;Safety Options=2;
MDX Missing Member Mode=Error

The remote AS is located out of my company domain. I have no additional info on how the instance is configured, nor possibility of having more permissions granted on the login, nor establish a VPN access.

Question is: if Excel lets me see the data I need (measures and dimensions with their hierarchies), why am I unable to see the same with other clients, using the same connection string?

Best would be if I could connect directly with SSMS (not using the linked server), by selecting "Analysis Services" server type on the initial prompt. Of course I tried, but given that only Windows Authentication is allowed there, even if I pass the entire string on the "Additional Connection Parameters" tab, I always receive the following error:

A connection cannot be made. Ensure that the server is running.
(Microsoft.AnalysisServices.AdomdClient).
No such host is known (System)

I also tried to create a local Windows login with same user and pw and open SSMS as that login, but of course that didn't work.

Any help is really appreciated.

Don't know if this can help: we have no local AS instances running on our servers. SQL Server Browser is running

Thanks

  • Are firewalls well configured? Is analysis services using default ports? docs.microsoft.com/en-us/sql/analysis-services/instances/… – McNets Nov 30 '18 at 15:01
  • The AS instance is running on a remote server, I just want to connect to it from my local server (where only a SQL Server instance is running) using Management Studio. Consider that on my local SQL Server instance I was able to add a linked server to that remote AS - which I guess is using port 1564; if firewall or ports were an issue, then how could I connect a linked server, or even connect with Data Tools? – matt Dec 1 '18 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.