I came across an issue the other day with some SSAS reports which are supposed to show an overview of all the projects we have on the go – the problem being, it never returns any data, just an error.
Upon enquiring as to why this might be, I was told “Oh, that’s never worked… it works on the server, but not on any clients”. Oh dear…
As it turns out, the reports would show on any of the servers in the farm, but on NO clients. Armed with a very consistent scenario such as this, troubleshooting should be fairly straight forward. The first thing that sprang to mind was authentication and the infamous “double-hop” issue - I had no real evidence of this, but it did provide a reasonable starting point…
After skimming a tech net article on connection issues with SSAS 2005 (http://technet.microsoft.com/en-au/library/cc917670.aspx#ECAA), I attempted to test connectivity from the clients into SSAS. To do this, create an empty text file and rename the .txt extension to .udl. Now when you double click on this udl file, you’ll get the data connection properties window. In the provider tab, select the type of data you’re connecting to (in this case Microsoft OLE DB Provider for Analysis Services 9.0 as we’re connecting to SSAS 2005). In the connection tab, enter the server’s name, and hit “test connection”. A very interesting thing happened; when using integrated authentication, the test failed with a “transport error”, if I specified an account on the SSAS server which I knew had access, the test would succeed. A quick look at the security log on the SSAS server confirmed that using integrated security, a failure audit would be generated and the connection would be blocked.
According to the tech net article, this is usually due to insufficient permissions… However, with my account (or anyone elses), using the server, I could access the reports just fine…
Turns out when PWA / MOSS was implemented, since the implementation wasn’t company wide and since the enterprise doesn’t actually use AD, a brand new AD domain was created for our department, and users were provisioned into this domain as needed. Log-in for the actual client machine the user was accessing MOSS from is completely independent – I’m not a big fan, but it could work, IF done properly. One tiny, but crucial thing was never done though - the domain that MOSS resides in does not trust the domain that users are logged in to. No client can ever access SSAS because their machine can never authenticate…
Creating a workaround is simple – provision a local account on the SSAS box for each user who needs access to the report, which has the same username and password they’re using to log onto their computer. Then add this account to a role in SSAS. Obviously that solution is not security best practice, and would completely unmanageable with a large number of users.
The permanent solution is to create a trust relationship between the 2 domains – this requires the domain admin to create, which is going to take weeks *sigh*, but hence the workaround for the time being. I’m of the opinion that a properly implemented SSO solution would go a long way to solving this too, but fixing SSO is a beast of an issue for another day…
Turns out when PWA / MOSS was implemented, since the implementation wasn’t company wide and since the enterprise doesn’t actually use AD, a brand new AD domain was created for our department, and users were provisioned into this domain as needed. Log-in for the actual client machine the user was accessing MOSS from is completely independent – I’m not a big fan, but it could work, IF done properly. One tiny, but crucial thing was never done though - the domain that MOSS resides in does not trust the domain that users are logged in to. No client can ever access SSAS because their machine can never authenticate…
Creating a workaround is simple – provision a local account on the SSAS box for each user who needs access to the report, which has the same username and password they’re using to log onto their computer. Then add this account to a role in SSAS. Obviously that solution is not security best practice, and would completely unmanageable with a large number of users.
The permanent solution is to create a trust relationship between the 2 domains – this requires the domain admin to create, which is going to take weeks *sigh*, but hence the workaround for the time being. I’m of the opinion that a properly implemented SSO solution would go a long way to solving this too, but fixing SSO is a beast of an issue for another day…
No comments:
Post a Comment