The release of Active Directory Federation Services (ADFS) 3.0 on Windows 2012 R2 made it possible to create OAuth connections to Dynamics CRM/365 running under IFD (Internet Facing Deployment). This was great news for organizations that wanted to remain on-premises or partner hosted, but were looking to leverage Power BI.
At Cobalt, we host Dynamics CRM/365 organizations for more than 150 customers, who have chosen a new AMS, and many of them are not ready to make the jump to the cloud so this was intriguing. Of course, we had invested a lot of time in our existing ADFS 2.0 setup and it wasn’t until we upgraded our infrastructure last year to Windows 2016 that we were able to give this a shot. I quickly searched for “Power BI and Dynamics 365 on-premises” and found the Microsoft TechNet article for how to set this up. I figured it would take a few hours and we’d be good to go. After 15 years of doing this, I should’ve known better.
First, I ran the PowerShell scripts listed in the article:
Add-PSSnapin Microsoft.Crm.PowerShell
$fedurl = Get-CrmSetting -SettingType ClaimsSettings
$fedurl.FederationProviderType = 1
Set-CrmSetting $fedurl
This worked until I got to the last command (Set-CrmSetting $fedurl), so I started digging. The first issue I found is that you need to make sure that the Web Address in Deployment Manager is set to the fully qualified domain name (e.g. crmifd.domain.com). Mine was set to the server name (see below).
You will get this error if it’s not set to the FQDN.
Get-CrmSetting : The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
At line:1 char:15
+ Get-CrmSetting <<<< TraceSettings
+ CategoryInfo : NotSpecified: (:) [Get-CrmSetting], WebException
+ FullyQualifiedErrorId : System.Net.WebException,Microsoft.Crm.PowerShell.GetCrmSettingCmdlet
After reading Rickard Norström’s blog post on this exact issue, I was feeling confident that if I made this change, I’d be good to go. I made the change, reset IIS, and reran the claims and IFD setup wizards. Still no dice. I even restarted the ADFS service on my ADFS server and refreshed the relying party trust. Nothing was working. Just more errors like the one below.
Set-CrmSetting : Source : mscorlib
Method : HandleReturnMessage
Date : 6:14:00 PM
Time : 4/9/2018
Error : The server was unable to process the request due to an internal error. For more information about the error,
either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the
configuration behavior) on the server in order to send the exception information back to the client, or turn on
tracing as per the Microsoft .NET Framework SDK documentation and inspect the server trace logs.
Stack Trace :
======================================================================================================================
At line:1 char:1
+ Set-CrmSetting $fedurl
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (Microsoft.Crm.P...rmSettingCmdlet:SetCrmSettingCmdlet) [Set-CrmSetting], F
aultException
+ FullyQualifiedErrorId : CRM Deployment Cmdlet Error,Microsoft.Crm.PowerShell.SetCrmSettingCmdlet
PS C:\Users\Administrator.COBALTTEST> $ClaimsSettings = Get-CrmSetting -SettingType OAuthClaimsSettings
It seemed like nothing I did worked to get the Set-CrmSetting $fedurl command to run. I was stumped. Then I came across a post on the Microsoft Power BI Community that offered different PowerShell commands.
$ClaimsSettings = Get-CrmSetting -SettingType OAuthClaimsSettings
$ClaimsSettings.Enabled = $true
Set-CrmSetting -Setting $ClaimsSettings
This made more sense to me since it was explicitly setting OAuth claims which is what I set out to do in the first place, so I gave it a try. Boom! No errors. Once again, I was feeling pretty confident, so I went back to the original TechNet article and looked at the next step (still only step 2, but better than step 1). Step 2 was an iisreset, so that went smoothly. Step 3 says:
“Register the Power BI Desktop OAuth 2.0 client with ADFS. To do this, open a Windows PowerShell window and run the following PowerShell command on the PC where you are running Power BI Desktop that will be used to publish your reports to the Power BI service.”
Add-AdfsClient -ClientId "a672d62c-fc7b-4e81-a576-e60dc46e951d" -Name "Microsoft Power BI" -RedirectUri @("https://de-users-preview.sqlazurelabs.com/account/reply/", "https://preview.powerbi.com/views/oauthredirect.html") -Description "ADFS OAuth 2.0 client for Microsoft Power BI"
This didn’t look right to me (ADFS is not running on my laptop), but I tried it anyway. As expected, PowerShell had no idea what I was talking about, so it failed. Being 99% confident that this needed to be run on my ADFS server, not my PC, I did a little research on what the command was actually doing. My suspicions were correct. This command adds a reference to Power BI’s OAuth client so that the ADFS server knows where to redirect requests when they come in. With this knowledge in hand, I ran the command on the ADFS server and it worked. To see what this did, you can run the following PowerShell command:
Get-AdfsClient -ClientId "a672d62c-fc7b-4e81-a576-e60dc46e951d"
This will give you the following result:
RedirectUri : {https://de-users-preview.sqlazurelabs.com/account/reply/, https://preview.powerbi.com/views/oauthredirect.html} Name : Microsoft Power BI Description : ADFS OAuth 2.0 client for Microsoft Power BI ClientId : a672d62c-fc7b-4e81-a576-e60dc46e951d BuiltIn : False Enabled : True ClientType : Public ADUserPrincipalName : ClientSecret : LogoutUri : JWTSigningCertificateRevocationCheck : None JWTSigningKeys : {} JWKSUri :
OK, so now I’m all set, right? I went back to Power BI and went through the steps to connect to the Dynamics 365.
Just when I thought everything was good to go, I got another error connecting.
The WWW-Authenticate header doesn’t contain a valid authorization URI. Header value: ‘Negotiate,NTLM’.
This looked familiar. As part of the Microsoft Power BI Community post I mentioned previously, there was a reference to a permissions error and an ADFS command to resolve the issue.
Grant-AdfsApplicationPermission -ClientRoleIdentifier "Power_BI_Client_ID" -ServerRoleIdentifier ADFS_RelyingPartyTrust_Name
After doing some research, this seemed promising so I went back to the ADFS server. Of course, I needed to figure out the Power_BI_Client_ID and ADFS_RelyingPartyTrust_Name should be set to. The Power_BI_Client_ID is the same ID that we used in a previous step (a672d62c-fc7b-4e81-a576-e60dc46e951d). Note that this is the same for all implementations since it is the ID tied to Power BI, not specific to your server. The ADFS_RelyingPartyTrust_Name is pretty straightforward. This is the URL of your Dynamics 365/CRM server. One caveat is that you need to include the forward slash (/) at the end of the URL or it will fail to recognize the server. Here’s an example:
Grant-AdfsApplicationPermission -ClientRoleIdentifier "a672d62c-fc7b-4e81-a576-e60dc46e951d" -ServerRoleIdentifier https://ifd.yourdomain.com/
After running this, I went back to Power BI and everything worked!
At the end of the day, I believe I can simplify the setup required to get your IFD Dynamics CRM/365 connected to Power BI:
1. Run the Following PowerShell Commands on the Dynamics CRM/365 Server:
Add-PSSnapin Microsoft.Crm.PowerShell $ClaimsSettings = Get-CrmSetting -SettingType OAuthClaimsSettings $ClaimsSettings.Enabled = $true Set-CrmSetting -Setting $ClaimsSettings
2. Restart IIS (Command Prompt > iisreset)
3. Run Following PowerShell Commands on the ADFS Server:
Add-AdfsClient -ClientId "a672d62c-fc7b-4e81-a576-e60dc46e951d" -Name "Microsoft Power BI" -RedirectUri @("https://de-users-preview.sqlazurelabs.com/account/reply/", "https://preview.powerbi.com/views/oauthredirect.html") -Description "ADFS OAuth 2.0 client for Microsoft Power BI"
Grant-AdfsApplicationPermission -ClientRoleIdentifier "a672d62c-fc7b-4e81-a576-e60dc46e951d" -ServerRoleIdentifier https://ifd.yourdomain.com/
That’s it. I started this believing that it should be pretty easy to set up. After a decent amount of trial and error, I hope that these steps make it easier for everyone else. If you have any questions, let’s save some time soon to connect for a quick call. Our team loves to help organizations see how Power BI and the rest of The Power Platform amplify the value of Microsoft Dynamics 365 and your CRM investment.