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
