Description:
The problem ordinarily appears after deploying an application that uses the Pivot Grid or Dashboard control to the production server or by running the application on a test machine. On the development machine, the credentials of the current user are used to establish the connection. After deploying the application, it may be necessary to establish the connection from the name of a different user. To accomplish this task, the user name and password are specified in the connection string. However, these credentials are ignored and the connection is established from the name of the current application user.
Answer:
The problem appears if the ADOMD data provider is used to retrieve data from the OLAP server. In the pivot grid control, it is possible to select the provider using the OLAPDataProvider property. In the Dashboard Suite, the ADOMD data provider is used by default. The problem appears because the ADOMD data provider uses Windows authentication and does not support the SQL server authentication. Thus the user name and password connection parameters are simply ignored. Note, we use the connection string to establish a connection as-is and does not affect this functionality in any way.
To avoid this issue, Microsoft suggests using the MSMDPUMP intermediate layer: Connecting to MSAS2005 using ADOMD.NET and OLEDB: we get different behavior. Why?. Using this approach, the connection to the SSAS server will be established by the intermediate layer from the name of the user whose credentials are specified in the connection string. To learn how to properly configure MSMDPUMP.dll, refer to the Configure HTTP Access to Analysis Services on Internet Information Services (IIS) 7.0 article.
I have attached a few test applications that allow testing an ADOMD connection without using the dashboard controls. Simply run the project to request data from the OLAP server. You will need to update the connection string and query to request data from your server.
If you see that some problem occurs in the test application, it makes sense to check this behavior using different versions of the Microsoft.AnalysisServices.AdomdClient.dll assembly. If you see that it is necessary to use a specific assembly version to avoid this problem, you can load this version manually in code and assign it to the static DevExpress.DataAccess.ConnectionParameters.OLAP.AdomdClientAssembly property. This assembly will be used by our Dashboard and PivotGrid controls to load data from SSAS.