Excel Report was not passing right user id to SQL Server Analysis Services

  • Article

Background:

Windows 2003x64, SQL 2005 SSAS with Dimension Security Enabled

In this scenario when we were opening an Excel Report deployed on SharePoint it hangs the report when we try to expand the user hierarchy

Resolution

To work around this issue, disable the local SID cache on the domain member computer. To do this, follow these steps:
1. Open Registry Editor.
To do this in Windows XP or in Windows Server 2003, click Start, click Run, type regedit, and then click OK.
To do this in Windows Vista, Click Start, type regedit in the Start Search box, and then press ENTER.
2. Locate and then right-click the following registry subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
3. Point to New, and then click DWORD Value.
4. Type LsaLookupCacheMaxSize, and then press ENTER.
5. Right-click LsaLookupCacheMaxSize, and then click Modify.
6. In the Value data box, type 0, and then click OK.
7. Exit Registry Editor.
We did above changes on SSAS server which is on Win server 2003 which resolved the issue

Cause

Root cause of problem found is User name flipping to "\usera@my.local" when launching excel report from SharePoint website as cause to this issue. This was traced out and seen in Analysis services 2005 profiler traces.

Instead user name should appear like: "usera@my.local" or my\usera

"\usera@my.local" is not acceptable to AS and it will only display no data considering we have dimension security applied in SSAS database.
The local security authority (LSA) caches the mapping between the SID and the user name in a local cache on the domain member computer. The cached user name is not synchronized with domain controllers. The LSA on the domain member computer first queries the local SID cache. If an existing mapping is already in the local SID cache, the LSA returns the cached user name information instead of querying the domain controllers. This behavior is intended to improve performance.

ADDITIONAL INFORMATION:

KB: https://support.microsoft.com/kb/946358

Ashutosh Sharma, Support Engineer-SQL Team