Example of the referenced working scenario • ADFS 3.0
• CRM 2013 SP1 claims based enabled
• Client machine with Excel 2013 and Microsoft Power Query for Excel 2.27.4163.242 onboard with Internet connectivity
Step-by-step on how to configure the environment A - Enable Form Based Authentication on ADFS 3.0
1. Log on to the ADFS server as an administrator.
2. Open the ADFS management wizard.
3. Click Authentication Policies > Primary Authentication > Global Settings > Authentication Methods > Edit.
4. Click (check) Form Based Authentication on the Intranet tab.
5. Restart the ADFS service
B - Configure/Allow the OAuth on Dynamics CRM deployment
1. Log on to the Microsoft Dynamics CRM server as an administrator.
2. In a Windows PowerShell console window, run the following script.
Add-PSSnapin Microsoft.Crm.PowerShell
$fedurl = Get-CrmSetting -SettingType ClaimsSettings
$fedurl.FederationProviderType = 1
Set-CrmSetting $fedurl
3. Run an IISReset command
C - Register the client application (Excel with Power Query add-in onboard)
1. Log on to the ADFS server as administrator.
2. In a PowerShell window, execute the following command.
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"
D - Configure Power Query for Excel
1. On the client machine, Open Excel and open/create a workbook
2. Click on "Power Query", then in the "Get External Data" section of the ribbon, click on "From other sources" and then choose "From OData feed"
3. Specify the Dynamics CRM Organization OData URL and click "OK" (internal or external URL depending on your scenario)
4. Click On "Organizational account" as the authentication method and then click on "Sign in"
5. When prompted, click "Allow" to confirm that you trust the ADFS as your identity provider for your OData feed
Note:
During this phase, the client machine with the Excel and Power Query onboard, needs Internet connectivity to reach the PowerBI authentication endpoint hosted in Azure @("https://de-users-preview.sqlazurelabs.com/account/reply/", "https://preview.powerbi.com/views/oauthredirect.html")
The ADFS end point will be added to the Approved ADFS Authentication Service" list; to access the list click on "Power Query", then in the "Settings" section of the ribbon, click on "Options" and then click on "Security".6. Click "Connect" and you will be presented the entity list as usual to start working with Power Query.