Error while impersonating an user in SQL Server Analysis Services
Hi folks, recently I came across an interesting issue related to impersonation in SSAS and I thought of sharing with you.
Scenario and Issue:
Let’s say we are trying to impersonate a user while browsing a cube in SSAS (Fig2). You might encounter with below error(Fig1). In first occurrence, it might feel like this is a login failure issue since error says as below but it’s not.
Error: The following system error occurred: The user name or password is incorrect.
You might see below error in the event Viewer log as well
An account failed to log on.
Logon Type: 3
Account For Which Logon Failed:
Unknown user name or bad password.
Caller Process ID:
Caller Process Name:
C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\bin\msmdsrv.exe
Source Network Address:
Detailed Authentication Information:
Package Name (NTLM only):
Initially I thought it might be related to Kerberos authentication, but we could reproduce the issue local to SSAS server. Hence it is not related to Kerberos.
We have tried EffectiveUserName Property in the connection string, but even there we are seeing the same error. For more info about EffectiveUserName, refer to /en-us/sql/analysis-services/instances/connection-string-properties-analysis-services.
While doing further research we found that Issue was with SSAS service account. In our case, we were using domain account as the SSAS service account and it looked like few permissions are missing.
We came to know that the SSAS service account is not part of the “Windows Authorization Access Group” active directory group. For more info about this AD group, refer to https://technet.microsoft.com/en-us/library/dn579255(v=ws.11).aspx#BKMK_WinAuthAccess.
We have followed the below steps to Grant the SSAS service domain account in this AD group: Windows Authorization Access Group.
Went to Active Directory Users and Computers
Added the required SSAS Domain service account and Apply
We restarted the SSAS service and issue has been fixed.Now we can able to impersonate the other user using different account while browsing the cube
Note: The problem does not exist when the SSAS service account is the local system account, but only happens when using a domain user account.
Hope this helps you as well.
Author: Vikas Kumar – Support Engineer, SQL Server BI Developer team, Microsoft
Reviewer: Sarath Babu Chidipothu – Support Escalation Engineer, SQL Server BI Developer team, Microsoft