Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DaveRuijter
Advocate II
Advocate II

Data Gateway or SSAS unable to read AD user information: "The user name or password is incorrect"

Hi everyone,

 

Looking for some advice and best-practice!

I need to know what the least required Active Directory related permissions are for SSAS to 'impersonate' a given user via the EffectiveUsername property, see the question in bold below.

 

Situation:

- Power BI Gateway running.

- SSAS tabular instance running. Service Account = SA1.

- A data source for a SSAS tabular database configured in the Gateway, with a connection using SA2.

- SA2 is Administrator of the SSAS tabular instance.

 

Problem:

- if UserA opens a report in the Power BI Service that's based on the configured data source, it works fine. UserA is sent to the database via the EffectiveUsername prop, the user has read access via a Role and data gets returned.

- if UserB opens the same report, the Gateway connection fails and  "The user name or password is incorrect" message appears in the Gateway log and profiler trace.

 

After a long adventure of troubleshooting and digging we found the difference:

- UserA has "Allow Read" permissions on the "Authenticated Users" group, if you look at the security tab of his Active Directory account.

- UserB does not have that permission active.

If we check the box for UserB, the connection works!

So I can only conclude that the impersonation or check that SSAS (or the Gateway?) does - on the value provided in the EffectiveUsername prop - needs to be able to read (some) properties on the AD user.

 

Question:

Since the IT guys are prudent on checking the "Allow Read" permission on all AD users:

What are the least required permissons required for this scenario to work, and/or what is the best practice regarding this "Allow Read" permission?

4 REPLIES 4
v-caliao-msft
Employee
Employee

@DaveRuijter

 

I think the following article should be helpful:
On-premises data gateway in-depth

 

The proper way to have Analyze Service to work well is to make sure the user’s UPN is well configured(Well mapped). Check the following information quoted:

Each time a user interacts with Analysis Services, the effective username is passed to the gateway and then onto your on-premises Analysis Services server. The user principal name (UPN), typically the email address you sign into the cloud with, is what we will pass to Analysis Services as the effective user. The UPN is passed in the connection property EffectiveUserName. This email address should match a defined UPN within the local Active Directory domain. The UPN is a property of an Active Directory account. That Windows account then needs to be present in an Analysis Services role to have access to the server. The login will not be successful if no match is found in Active Directory.
Analysis Services can also provide filtering based on this account. The filtering can occur with either role based security, or row-level security.
For Analysis Services to determine if a user connecting to it belongs to a role with permissions to read data, the server needs to convert the effective username passed from AAD to the gateway, and onto the Analysis Services server. The Analysis Services server passes the effective username to a Windows Active Directory domain controller (DC). The Active Directory DC then validates the effective username is a valid UPN, on a local account, and returns that user’s Windows username back to the Analysis Services server.
EffectiveUserName cannot be used on a non-domain joined Analysis Services server. The Analysis Services server must be joined to a domain to avoid any login errors.“


By the way, here are few questions:

  • Would you please share more details about the "Authenticated Users" group, is this group related with the SSAS service role?
  • Have you configured the data source user tab under Power BI service->Manage gateways, to allow whom to use this data source?

Reference: Manage your data source - Analysis Services.

 

Regards,

Charlie Liao

Thanks Charlie.

 

I guess that text says it's the SSAS Service Account that needs the permission to read the local accounts properties.

But, it is not clear about what exact permissions are required unfortunately..

 

  • Would you please share more details about the "Authenticated Users" group, is this group related with the SSAS service role?

I think that group is a default group, i believe it's a little less 'wide' as "Everyone" but still too 'wide' to give "Read" permission to.

Remember, we have to set this for all users working with data via the Gateway.

 

Here is a screenshot, hope this helps:

 

Local Account props.png

Hi @DaveRuijter,

 

OK. I understand the Authenticated users now.

 

So back to the question, if we would like to allow a user to gain access to SSAS server, we at least should meet the following requirements:
1. Configure a SSAS server role which have the proper permission to gain access to the server, then add the required user into that role under SSAS server;
2. Using user’s UPN name to login;

 

Regarding how users are verified, I think the quoted  text I shared should be clear enough, for SSAS how to verify a user have the proper permission, first SSAS need to pass the user’s UPN to the domain DC, after the DC verified the UPN, then it will return the user’s Windows username back to the Analysis Services server, after that SSAS would check the user with the permissions configured within the server role.

 

And based on what you have shared, would you please share some information about how the server defines the role permission here?

 

Regards,

Charlie Liao


@Charlie_Liao wrote:

So back to the question, if we would like to allow a user to gain access to SSAS server, we at least should meet the following requirements:
1. Configure a SSAS server role which have the proper permission to gain access to the server, then add the required user into that role under SSAS server;
2. Using user’s UPN name to login;


Hi Charlie,

 

I can anwser these 2 questions of course (answer="Yes, we have done that." for both), but I don't see why they are relevelt to my original question, because if we check the box mentioned before for UserB, the connection works just fine :).


@Charlie_Liao wrote:

OK. I understand the Authenticated users now.


 

Do you know if it is OK to give that group Read permissions on all the AD users that will be using the Power BI Service (since they will then be authenticated on AD by SSAS using the EffectiveUsername).

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors