ADOMD.NET Connection ignores User ID property when connecting to SSAS on-premise over native connection, not http

Tanya Ershova 6 Reputation points
2020-10-06T18:48:01.237+00:00

We are trying to execute MDX statements from .NET Framework 4.5 application. It works if windows identity that App pool runs as has access to the cube.

According to the documentation (https://learn.microsoft.com/en-us/analysis-services/instances/connection-string-properties-analysis-services?view=asallproducts-allversionsn):

"SQL Server Analysis Services uses Windows authentication only, but you can set properties on the connection string to pass in a specific user name and password."

When we change connection string to:
"Data Source={0};Catalog={1};User ID={2};Password={3}"
we can still see that connection attempt is made with Application Pool identity , not the identity specified in connection string.

Are we making mistake in how we are passing credentials to SSAS connection ?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,231 questions
0 comments No comments
{count} vote

2 answers

Sort by: Most helpful
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-10-07T06:19:53.96+00:00

    Hi,

    It seems we need to create an application pool in IIS server first and connect the ssas to IIS server using windows account credentials.
    This is also mentioned in other people's blog, although it is a little bit old : Microsoft Analysis Services 2005: Displaying a grid using ADOMD.NET and MDX

    We could change the user/password property in IIS settings. Check steps at :
    Connect to MS SQL Server Analysis Services

    Regards,
    Lukas


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Olaf Helper 38,786 Reputation points
    2020-10-07T06:55:21.227+00:00

    "Data Source={0};Catalog={1};User ID={2};Password={3}"

    Use the EffectiveUserName property instead; I use it in Excel connection to test user permissions.