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

Question

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 ?

Answer 1

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.

Answer 2

"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.