NTLM authentication
NTLM (opens in a new tab) is an authentication method developed by Microsoft that can be used to authenticate requests to DAX API and MDX API.
DAX API and MDX API are available in Cube Cloud on Enterprise and above (opens in a new tab) product tiers. They also require the M deployment tier.
On the diagram below, NTLM is used to authenticate requests from Power BI Service that come through the on-premises data gateway (opens in a new tab) (step 6):
Authentication flow
The NTLM authentication can be used with Power BI Desktop or with Power BI Service and the on-premises data gateway (opens in a new tab).
Power BI Desktop
Initiated by Power BI Desktop, NTLM authentication works as follows:
- Power BI Desktop is launched under a specific user account via the
runas
command. - Power BI Desktop performs an NTLM challenge-response authentication and passes the credentials of that account to the Cube Cloud deployment.
- The Cube Cloud deployment verifies the credentials.
In the following example, Power BI Desktop is launched under the cube
user:
# Run Power BI Desktop as the `cube` user
runas /user:cube "C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe"
# Run a specific report in Power BI Desktop as the `cube` user
runas /user:cube "C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe \"C:\Users\Administrator\Desktop\Dashboard.pbix\""
This flow should only be used for testing purposes. Note that, when Power BI Desktop is started as a user different from the currently logged-in Windows account, it may encounter permission issues, e.g., when saving files on network drives.
In a production environment, Power BI Desktop should be used with Kerberos authentication.
Power BI Service
Initiated by Power BI Service, NTLM authentication works as follows:
- The gateway is configured with a master user account.
- When users interact with a Power BI report in Power BI Service, their user principal name (UPN) is passed to the gateway.
- The gateway performs an NTLM challenge-response authentication and passes the the credentials of the master user account to the Cube Cloud deployment. It also passes the UPN of the interacting user.
- The Cube Cloud deployment verifies the credentials and changes the user name to the UPN of the interacting user.
This is the recommended way to authenticate Power BI Service requests.
Configuration
Using NTLM authentication requires configuring the deployment to verify the credentials.
To use NTLM authentication with Power BI Service, you also need to install the on-premises data gateway first.
Installing the gateway
You need to have the on-premises data gateway (opens in a new tab) installed on a Windows Server machine.
It should be configured to authenticate with a master user account. It can be a local user on the machine or a domain user.
Verifying the credentials
By default, CUBEJS_SQL_USER
and CUBEJS_SQL_PASSWORD
environment variables are used
to verify the passed credentials. You can also customize the authentication by using the
check_sql_auth
configuration option.
Also, the CUBEJS_SQL_SUPER_USER
environment variable or the can_switch_sql_user
configuration option can be used to ensure that the
user name can be changed to the UPN of the interacting user only if proper credentials
of the master user account were passed.