So I have my VB.NET code that connects to an Analysis Cube via ADOMD from my local desktop. I think, great, I can take this code now, dump it into my ASP.NET site or Webpart and take off. Wrong. I kept getting vague errors which I knew were likely permissions, but I didn't know why. Here were the messages from the exception and inner exceptions:
Exception: The connection either timed out or was lost
Inner Exception 1: Unable to read data from the transport connection
Inner Exception 2: An existing connection was forcibly closed by the remote host.
The problem is this... when you connect to a website via Windows Authentication your machine passes your credentials off to the web-site (which in our case is setup to impersonate in the web.config, so it uses your permissions). Now, since Analysis Services expects a trusted connection with an Active Directory account, the web-site would pass your credentials off to SQL Analysis Services. This is where I got hung up. I've learned that NTLM (v2) has a security feature that won't pass your credentials off more than once... therefore you connect to the web-site and hand off your login credentials but the web-site won't in turn use those to login to SQL Analysis Services and you get the above error which should read "Permission Denied".
How to get around this:
Well, first I tried adding Everyone to the cube just as a test, that didn't work. The Sharepoint webpart is running under a local IUSR account which I can't add to the Analysis Services because it's local on another machine.
- I implemented Impersonation in my .NET webpart. What I did was impersonate a proxy Active Directory account that we use for running certain services. The webpart executed as that user for that section of code, and it worked well (You add that Active Directory user to the roll that is associated with your cube that gives users the ability to read it). Here's a link on how to impersonate (you may have to load it twice, it seems to come up with an error, but the second time I goto it it works.. if this link totally goes away let me know and I'll post an example on how to impersonate though you can probably find some on the web easily): http://support.microsoft.com/kb/306158
- If you don't mind using basic authentication, you can turn it on and disable Windows authentication. Basic authentication will pass your user credentials through to the SQL Server. Be warned though, basic authentication is not encrypted if not on SSL. If you're going to use it I highly suggest making putting SSL on your site to encrypt the data being transfered. Basic authentication in most all cases will prompt for your password with a popup box so if you have to have pass through authentication this solution isn't for you, though it will work.