Tuesday, March 13, 2012

Connecting to Analysis Services in a remote computer using Impersonation with C# - Microsoft SQL Server 2008 R2

My last post was about trying to connect to remote Analysis Services and it was using a tool like BIDS (Business Intelligence Development Studio) or Microsoft SQL Server 2008 R2 Report Builder 3 etc. Today I am going to write about successfully connecting to remote Analysis Services using C#.

If you have not read my previous post about connecting to remote Analysis Services, I think it's better if you can read it first.

So, now I will begin. Now I have logged in to my machine as local administrator and I have no permission to remote Analysis Services. I needed to programmatically create a connection with remote Analysis Services and I tried a lot using various ways.I tried passing both the username and password which has access to remote Analysis Services in connection string in my AdomdConnection. But it was again this same error as I previously had.

Error
Then suddenly this came to my mind, "Impersonation". Of course, If I can access the remote Analysis Services from the windows login of the account which has permission to Analysis Services, from the code if I impersonate myself as that user, I should be able to access Analysis Services without passing any usernames and passwords in the connection string. (Not to mention, the other alternative would be, switch the user and log into the machine using the account which has access to Analysis Services and running Visual Studio from that account each time you want to create the connectivity.)

So from the code, I impersonated my self as the user which has access to Analysis Services and initialized the AdomdConnection. The connection successfully got created and I was able to retrieve database information successfully. And it's my happy day.

Happy Coding.

Regards,
Jaliya