Connecting to Analysis Service from ASP.net applic

Question

I am trying to implement ASP.net application which will need to connect Microsoft Analysis Service (SSAS) to retrive data from cube as well as dimensions.

    static void Main(string[] args)
    {
        StringBuilder connectionStringBuilder = new StringBuilder();
        connectionStringBuilder.Append("Data Source=MyDataSource;");
        connectionStringBuilder.Append("Initial Catalog=MyOlapDatabase;");
        connectionStringBuilder.Append(@"User Id=OlapServerMachineName\MyUserName;");
        connectionStringBuilder.Append("Password=MyPassword;");
        connectionStringBuilder.Append("Provider=MSOLAP.5;");
        connectionStringBuilder.Append("Persist Security Info=True;");
        connectionStringBuilder.Append("MDX Compatibility=1;");
        connectionStringBuilder.Append("Safety Options=2;");
        connectionStringBuilder.Append("MDX Missing Member Mode=Error;");
        connectionStringBuilder.Append("Update Isolation Level=2;");



        using (var connection = new AdomdConnection(connectionStringBuilder.ToString()))
        {
            connection.Open();

        }

    }

This code throws the following exception

A connection cannot be made. Ensure that the server is running.

and inner exception says

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

and inner exception of inner exception is

Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

On the other hand, I can connect to this Analysis Service from Excel with the same user name and password as shown below

I tried setting up HTTP access to Olap Server as described here. With anonymous authentication on IIS setting, I am able to connect successfully. But when I disable Anonymous option and enable Basic Authentication, again I am not able connect from my client tool but Excel is working fine with Basic Authentication.

But trying to use HTTP access was just for temporary solution. I do not want to use that option. I want to be able connect SSAS like I can in excel from my client app.

My question is, what is the difference between my implementation to connect SSAS (does not work) and excel authentication (works perfect) ?

Thank you in advance.

Answer 1

Instead of putting the user and password on the connection string try wrapping at least your .Open() function call in this impersonator:

https://github.com/OlapPivotTableExtensions/OlapPivotTableExtensions/blob/master/OlapPivotTableExtensions/Impersonater.cs

I have had more success with that than passwords on the connection string.

You shouldn't need the msmdpump HTTP layer unless you prefer it be involved.