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.

Subject:

Security ID:

Domain\UserName

Account Name:

[SA account]

Account Domain:

[DOMAIN]

Logon ID:

0x192D1

 

Logon Type: 3

 

Account For Which Logon Failed:

Security ID:

NULL SID

Account Name:

Account Domain:

 

Failure Information:

Failure Reason:

Unknown user name or bad password.

Status:

0xC000006D

Sub Status:

0xC0000064

 

Process Information:

Caller Process ID:

0x7dc

Caller Process Name:

C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\bin\msmdsrv.exe

 

Network Information:

Workstation Name:

[Server]

Source Network Address:

-

Source Port:

-

 

Detailed Authentication Information:

Logon Process:

OLAPSvc

Authentication Package:

Kerberos

Transited Services:

-

Package Name (NTLM only):

-

Key Length:

0

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.

Note: EffectiveUserName is not case sensitive

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.

Step 1:

Went to Active Directory Users and Computers

 

Step 2:

Double clicked on Windows Authorization Access Group =>Members

Step 3:

Added the required SSAS Domain service account and Apply

Step 4:

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