Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Authentication with gateway

Hello,
I'm having some problems to creating the following infrastructure:

  1. A SQL Server Analysis Services 2017 instance on-premises to deploy tabular models.

  2. Integration with the Power BI service using an on-premises data gateway standard to retrieve data.

To test this architecture, I created a virtual machine (VM) in Azure with Windows Server 2019 to simulate an on-premises server. I installed all the required components (SSAS, Gateway, Power BI Desktop, Visual Studio) on the VM. I joined the VM with Azure AD, allowing me to log in with my Azure AD credentials and have a user principal name (UPN) to integrate row-level security in the Tabular model.

The output of "dsregcmd /status" is as follows:
AzureAdJoined: YES
EnterpriseJoined: NO
DomainJoined: NO

Next, I created a group on the VM and added a local user and my Azure AD user to the group using the following command: "net localgroup GROUP /add 'azureADdomain\myupn'". I used "azureADdomain\myupn" because neither "azureADdomain\username" nor "username" worked. I added this group to the database role with read permissions.

When I use Excel on the same machine to retrieve data from the SSAS database with the local user, everything works fine, and the Row-Level Security is applied correctly. However, when I try to access it with the Azure AD user credentials, I get an error message: "An error was encountered in the transport layer."

Interestingly, if I log in to the VM with the Azure AD account and access SSAS through Power BI Desktop on the same machine using Windows credentials, I face no problems. The UPN is correct, and the RLS works as expected.

Afterward, I attempted to use Power BI Desktop to create a report and publish it to my workspace, hoping that the EffectiveUsername remapping would allow me to access the data. The gateway seems to be working correctly based on the network connectivity tests in the Diagnostic tab. Additionally, I can see it online on my account on powerbi.com.

I published the report, leaving the default settings for the "Map user name" section in the connection configuration (only selecting the EffectiveUserName radio button without any rules).

Now, if I try to access the report, I encounter the error message: "There was a data source access error." Looking at the gateway log, I see the following:

“Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: The following system error occurred: The name provided is not a properly formed account name”.

In the additional logs, I found: “Either LookupPropertyName {{myupn}} or TargetPropertyName is null or empty: Fall back to the original value. Replace the effective username in the ADOMD connection string from {{myupn}} to {{myupn}}. About to open ADOMD connection. Current Windows Identity is '<euii>{{myupn}}</euii>'“.

I tried changing the connection configuration by adding a rule in the "Map user name" section with the following details:
Replace: *
With: {{localdomain\localusername}}

However, I still receive the same error in the report, but the gateway log shows a different error message:

“Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: The following system error occurred: We can't sign you in with this credential because your domain isn't available. Make sure your device is connected to your organization's network and try again. If you previously signed in on this device with another credential, you can sign in with that credential”.

I want to clarify that, at the moment, I am unable to join the VM to my domain.

Is there anything I can do to resolve this issue?

1 Reply