Connecting Power BI to Dynamics 2016 on-premise with IFD

[gjayne84]

Hi everyone

We have been using Power BI with on-premise Dynamics CRM 2016 using OData connection and it worked fine.  However, we have now reconfigured our Dyanmics server so it now uses claims-based authentication and an Internet Facing Deployment (IFD) is setup.  Our users now login to Dynamics via ADFS on Server 2016 and can access the site both inernally and externally.

Since this change we cannot get Power BI Desktop to connect to our CRM server.  I have done the following:

Enabled oauth on our Dynamics server

Registered the Power BI Desktop OAuth 2.0 client with ADFS

(Steps from https://technet.microsoft.com/en-us/library/dn708055.aspx)

When we try to add the odata connection to Dynamics CRM using Anonymous/Windows authentication I get an error saying "We couldn't authenticate with the credentials provided.  Please try again."

If I try using Organisational Account I get "The WWW-Authenticate header doesn't contain a valid authorization URI.  Header value: 'Negotiate,NTLM'.

Can anyone tell me any additional steps I need to take to get this to work as I'm having trouble finding any helpful solutions online for this?

Thanks for your help in advance.

[v-qiuyu-msft]

Hi @gjayne84,

Please use Fiddler and repeat the steps to get data from Dynamics CRM on-premise, share .saz file with us.

Best Regards,
Qiuyun Yu

[gjayne84 · Accepted solution]

Hi Qiuyun

Thanks for the quick reply but I've now managed to get this working.  To get it working I had to run the following PS commands on our Dynamics server:

$ClaimsSettings = Get-CrmSetting -SettingType OAuthClaimsSettings
$ClaimsSettings.Enabled = $true
Set-CrmSetting -Setting $ClaimsSettings

This got me a bit further but I was then getting a permissions error.  This was resolved by running the following on our ADFS server:

Grant-AdfsApplicationPermission -ClientRoleIdentifier "POWERBI_CLIENTID" -ServerRoleIdentifier ADFS_RelyingPartyTrust_NAME

This gave Power BI Desktop access to ADFS and I was able to connect to the Odata source using Organization Credentials.

[Syndicate_Admin]

hi dear 

thanks for your answer 

i can not solve for myself 

i can not run the following command in ps 

$ClaimsSettings = Get-CrmSetting -SettingType OAuthClaimsSettings
$ClaimsSettings.Enabled = $true
Set-CrmSetting -Setting $ClaimsSettings

and please explain more the following 

Grant-AdfsApplicationPermission -ClientRoleIdentifier "POWERBI_CLIENTID" -ServerRoleIdentifier ADFS_RelyingPartyTrust_NAME

what is powerbi clientid ?

i defined application group and secret and client id in it . 

Unfortunately i'm confused now 

[thuanhungtq]

Hi

Could you please advice in more detail about "Grant-AdfsApplicationPermission -ClientRoleIdentifier "POWERBI_CLIENTID" -ServerRoleIdentifier ADFS_RelyingPartyTrust_NAME" ?

i tried 

Grant-AdfsApplicationPermission -ClientRoleIdentifier "POWERBI_3ad65de6-ccb9-4132-8ea0-f85f03ed6a39" -ServerRoleIdentifier "CRM IFD Relying Party"

got issue

Grant-AdfsApplicationPermission : The term 'Grant-AdfsApplicationPermission' is not recognized as the name of a
cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify
that the path is correct and try again.
At line:1 char:1
+ Grant-AdfsApplicationPermission -ClientRoleIdentifier "POWERBI_3ad65de6-ccb9-413 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Grant-AdfsApplicationPermission:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

Hung

[gjayne84]

Hi Hung

Are you running this command on your ADFS server?

[Mike_Alianz]

We're also getting "The WWW-Authenticate header doesn't contain a valid authorization URI.  Header value: 'Negotiate,NTLM'."

Apparently "Grant-AdfsApplicationPermission" only works on Windows 2016.  Is there another option for WIndows 2012 R2?

We can connect Power BI desktop to our DEV server, but the above error occurs on our QA and PROD servers (different CRM servers, same ADFS).

Any alternative to "Grant-AdfsApplicationPermission" on WIndows 2012 R2 to grant Power BI desktop access to the other trusts?

[gjayne84]

Hi Mike

As far as I know there is no equivalent to Grant-AdfsApplicationPermission on Server 2012.  Are the setups on your QA and PROD CRM servers different in any way regarding authentication?  Also, have you tried using Anonymous access method when connecting Power BI to Dynamics?

[karthikmsk]

Guys! any solution for this ?

[thuanhungtq]

Hi 

Could you advise in more detail about "Grant-AdfsApplicationPermission -ClientRoleIdentifier "POWERBI_CLIENTID" -ServerRoleIdentifier ADFS_RelyingPartyTrust_NAME "

i tried below but did not work.

Grant-AdfsApplicationPermission -ClientRoleIdentifier "POWERBI_3ad65de6-ccb9-4132-8ea0-f85f03ed6a39" -ServerRoleIdentifier "CRM IFD Relying Party"

Grant-AdfsApplicationPermission : The term 'Grant-AdfsApplicationPermission' is not recognized as the name of a
cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify
that the path is correct and try again.
At line:1 char:1
+ Grant-AdfsApplicationPermission -ClientRoleIdentifier "POWERBI_3ad65de6-ccb9-413 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Grant-AdfsApplicationPermission:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

Hung