microsoft / navcontainerhelper

Official Microsoft repository for BcContainerHelper, a PowerShell module, which makes it easier to work with Business Central Containers on Docker.
MIT License
385 stars 246 forks source link

PowerBI access to container using traefik - error "the token service reported by the resource is not trusted" #2359

Closed sirhc101 closed 1 year ago

sirhc101 commented 2 years ago

Describe the issue To be honest, I'm not sure it is related to BcContainerHelper, but as far as I know it works with classic On-Premises installations, so I just want to give it a try. We're running BC containers using traefik. The containers authenticate using AAD and I've created all app registrations using Create-AadAppsForBC. This worked well and created a bunch of app registrations in Azure AD. Afterwards I created a new container, using the script below. Authentication on Web Client works well. I also was able to consume OData feeds using Postman: image

But as soon as I try to connect to OData feed using PowerBI I get the following error message: image

As you can see in the script below I was forced to add additional traefik route to make OData available, due to the issue reported with #2137. In our scenario it's ok because we've only one container where we need OData.

Scripts used to create container and cause the issue

New-BCContainer -accept_eula `
    -containerName $containerName `
    -multitenant:$multitenant `
    -updateHosts `
    -artifactUrl $artifactUrl `
    -memoryLimit 30G `
    -licenseFile $license `
    -EnableTaskScheduler:$enableTaskScheduler `
    -auth AAD `
    -authenticationEMail "my@email.com" `
    -databaseServer 'host.containerhelper.internal' `
    -databaseInstance '' `
    -databaseName "bc-database" `
    -databaseCredential $databaseCredential `
    -accept_outdated `
    -useTraefik `
    -PublicDnsName sub.domain.com `
    -shortcuts DesktopFolder `
    -alwaysPull `
    -useSSL `
    -additionalParameters @(
        "--env appIdUri=$applicationId",
        "--env federationLoginEndpoint=$federationLoginEndpoint",
        "--env federationMetadata=$federationMetadataLocation",
        "--env authenticationEMail=my@email.de",
        "-v C:\containers\bc:c:\storage",
        '-l "traefik.odata.frontend.rule=PathPrefix:/BC/ODataV4,/BC/ODatav4,/bc/BC/ODataV4;ReplacePathRegex: ^/BC/ODataV4(.*),^/BC/ODatav4(.*),^/bc/BC/odatav4(.*) /BC/ODataV4$1,/BC/ODataV4$1,/BC/ODataV4$1"',
        '-l "traefik.odata.port=7048"'
        )

Invoke-ScriptInBcContainer -containerName $containerName -scriptblock {
    # Set Application Insights
    Set-NAVServerConfiguration -ServerInstance "BC" -KeyName "ApplicationInsightsInstrumentationKey" -KeyValue "xxx" -ApplyTo All

    # Enable Visual Studio Code Access
    Set-NAVServerConfiguration -ServerInstance "BC" -KeyName "ValidAudiences" -KeyValue "$(Get-NAVServerConfiguration BC -KeyName ValidAudiences);https://api.businesscentral.dynamics.com"

    # Set Excel Add-In Registration
    Set-NAVServerConfiguration -ServerInstance "BC" -KeyName "ExcelAddInAzureActiveDirectoryClientId" -KeyValue $excelAddInClientId

    Set-NAVServerConfiguration -ServerInstance "BC" -KeyName DisableTokenSigningCertificateValidation -KeyValue $true
    Set-NAVServerConfiguration -ServerInstance "BC" -KeyName ExtendedSecurityTokenLifetime -KeyValue 24
    Set-NAVServerConfiguration -ServerInstance "BC" -KeyName DisableTokenSigningCertificateValidation -KeyValue $true 
    Set-NAVWebServerInstanceConfiguration -WebServerInstance $containerName -KeyName SessionTimeout -KeyValue 01:00:00

    # Set Max Upload Size to 512MB
    Set-WebConfigurationProperty `
        -filter /system.webserver/security/requestfiltering/requestLimits `
        -name maxAllowedContentLength `
        -value 536870912 # 512 MB in Bytes

    Restart-NavServerInstance -ServerInstance "BC"
}

Add-FontsToNavContainer -containerName $containerName -path "C:\Install\fonts"

# [..] unrelated stuff...

Restart-BcContainer -containerName $containerName

Full output of scripts

BcContainerHelper is version 3.0.3
BcContainerHelper is running as administrator
Host is Microsoft Windows Server 2019 Standard - ltsc2019
Docker Client Version is 20.10.0
Docker Server Version is 20.10.0
Removing container bc
Removing bc from container hosts file
Removing bc-* from container hosts file
Removing C:\ProgramData\BcContainerHelper\Extensions\bc
Fetching all docker images
Fetching all docker volumes
Enabling SSL as otherwise all clients will see mixed HTTP / HTTPS request, which will cause problems e.g. on the mobile and modern windows clients
Pulling image mcr.microsoft.com/businesscentral:10.0.17763.2565
10.0.17763.2565: Pulling from businesscentral
Digest: sha256:21b682c5adf6cf716dc82de8e55d52bb66129d3f2922192754a9e6c15ea31c2d
Status: Image is up to date for mcr.microsoft.com/businesscentral:10.0.17763.2565
mcr.microsoft.com/businesscentral:10.0.17763.2565
Using image mcr.microsoft.com/businesscentral:10.0.17763.2565
PublicDnsName is sub.domain.com
Creating Container bc
Style: onprem
Multitenant: No
Version: 19.3.34541.35002
Platform: 19.0.34506.34941
Generic Tag: 1.0.2.1
Container OS Version: 10.0.17763.2565 (ltsc2019)
Host OS Version: 10.0.17763.2565 (ltsc2019)
Using process isolation
Using locale de-DE
Adding special CheckHealth.ps1 to enable Traefik support
Disabling the standard eventlog dump to container log every 2 seconds (use -dumpEventLog to enable)
Using license file C:\install\fin.flf
Additional Parameters:
--env appIdUri=https://sub.domain.com/bc
--env federationLoginEndpoint=https://login.microsoftonline.com/<tenant-id>/wsfed?wa=wsignin1.0%26wtrealm=https://sub.domain.com/bc%26wreply=https://sub.domain.com/bc/SignIn
--env federationMetadata=https://login.microsoftonline.com/<tenant-id>/FederationMetadata/2007-06/FederationMetadata.xml
--env authenticationEMail=my@mail.com
-v C:\containers\bc:c:\storage
-l "traefik.odata.frontend.rule=PathPrefix:/BC/ODataV4,/BC/ODatav4,/bc/BC/ODataV4;ReplacePathRegex: ^/BC/ODataV4(.*),^/BC/ODatav4(.*),^/bc/BC/odatav4(.*) /BC/ODataV4$1,/BC/ODataV4$1,/BC/ODataV4$1"
-l "traefik.odata.port=7048"
-e webserverinstance=bc
-e publicdnsname=sub.domain.com
-l "traefik.protocol=https"
-l "traefik.web.frontend.rule=PathPrefix:/bc"
-l "traefik.web.port=443"
-l "traefik.soap.frontend.rule=PathPrefix:/bcsoap;ReplacePathRegex: ^/bcsoap(.*) /BC$1"
-l "traefik.soap.port=7047"
-l "traefik.rest.frontend.rule=PathPrefix:/bcrest;ReplacePathRegex: ^/bcrest(.*) /BC$1"
-l "traefik.rest.port=7048"
-l "traefik.dev.frontend.rule=PathPrefix:/bcdev;ReplacePathRegex: ^/bcdev(.*) /BC$1"
-l "traefik.dev.port=7049"
-l "traefik.snap.frontend.rule=PathPrefix:/bcsnap;ReplacePathRegex: ^/bcsnap(.*) /BC$1"
-l "traefik.snap.port=7083"
-l "traefik.dl.frontend.rule=PathPrefixStrip:/bcdl"
-l "traefik.dl.port=8080"
-l "traefik.dl.protocol=http"
-l "traefik.enable=true"
-l "traefik.frontend.entryPoints=https"
--env customNavSettings=EnableTaskScheduler=True,PublicODataBaseUrl=https://sub.domain.com/bcrest/odata,PublicSOAPBaseUrl=https://sub.domain.com/bcsoap/ws,PublicWebBaseUrl=https://sub.domain.com/bc
Files in C:\ProgramData\BcContainerHelper\Extensions\bc\my:
- AdditionalOutput.ps1
- CheckHealth.ps1
- license.flf
- MainLoop.ps1
- SetupVariables.ps1
- updatehosts.ps1
Creating container bc from image mcr.microsoft.com/businesscentral:10.0.17763.2565
Using Shared Encryption Key file
97a915538cedc9858c41acabbce20eecc9024806eccac7c6dcbd99ff6bb6e78b
Waiting for container bc to be ready
Using artifactUrl https://bcartifacts.azureedge.net/onprem/19.3.34541.35002/de
Using installer from C:\Run\150-new
Installing Business Central
Installing from artifacts
Starting Local SQL Server
Starting Internet Information Server
Copying Service Tier Files
c:\dl\onprem\19.3.34541.35002\platform\ServiceTier\Program Files
c:\dl\onprem\19.3.34541.35002\platform\ServiceTier\System64Folder
Copying PowerShell Scripts
c:\dl\onprem\19.3.34541.35002\platform\WindowsPowerShellScripts\Cloud\NAVAdministration
c:\dl\onprem\19.3.34541.35002\platform\WindowsPowerShellScripts\WebSearch
Copying dependencies
Copying ReportBuilder
Importing PowerShell Modules
Skipping restore of Cronus database
Modifying Business Central Service Tier Config File for Docker
Creating Business Central Service Tier
Installing SIP crypto provider: 'C:\Windows\System32\NavSip.dll'
Copying Web Client Files
c:\dl\onprem\19.3.34541.35002\platform\WebClient\Microsoft Dynamics NAV
Copying Client Files
c:\dl\onprem\19.3.34541.35002\platform\LegacyDlls\program files\Microsoft Dynamics NAV
c:\dl\onprem\19.3.34541.35002\platform\LegacyDlls\program files\Microsoft Dynamics NAV
c:\dl\onprem\19.3.34541.35002\platform\LegacyDlls\systemFolder
Copying ModernDev Files
c:\dl\onprem\19.3.34541.35002\platform
c:\dl\onprem\19.3.34541.35002\platform\ModernDev\program files\Microsoft Dynamics NAV
Copying additional files
Copying ConfigurationPackages
C:\dl\onprem\19.3.34541.35002\de\ConfigurationPackages
Copying Test Assemblies
C:\dl\onprem\19.3.34541.35002\platform\Test Assemblies
Copying Applications
C:\dl\onprem\19.3.34541.35002\de\Applications
Installation took 36 seconds
Installation complete
Initializing...
Setting host.containerhelper.internal to 172.28.32.1 in container hosts file
Starting Container
Hostname is bc
PublicDnsName is sub.domain.com
Using AccessControlService Authentication
Import Encryption Key
Stopping local SQL Server
Creating Self Signed Certificate
Self Signed Certificate Thumbprint 6B234FDCD2A1250467F28D224EA1A186B9DDBD41
DNS identity sub.domain.com
Modifying Service Tier Config File with Instance Specific Settings
Modifying Service Tier Config File with settings from environment variable
Setting EnableTaskScheduler to True
Setting PublicODataBaseUrl to https://sub.domain.com/bcrest/odata
Setting PublicSOAPBaseUrl to https://sub.domain.com/bcsoap/ws
Setting PublicWebBaseUrl to https://sub.domain.com/bc
Starting Service Tier
Registering event sources
Creating DotNetCore Web Server Instance
Using license file 'c:\run\my\license.flf'
Import License
Creating http download site
Container IP Address: 172.28.44.247
Container Hostname  : bc
Container Dns Name  : sub.domain.com
Web Client          : https://sub.domain.com/bc/
Dev. Server         : https://sub.domain.com
Dev. ServerInstance : BC
Setting bc to 172.28.44.247 in host hosts file

Files:
http://sub.domain.com:8080/ALLanguage.vsix
http://sub.domain.com:8080/certificate.cer

Container Total Physical Memory is 127.9Gb
Container Free Physical Memory is 52.9Gb

Initialization took 320 seconds
Ready for connections!
Reading CustomSettings.config from bc
Creating Desktop Shortcuts for bc
Container bc successfully created
Because of Traefik, the following URLs need to be used when accessing the container from outside your Docker host:
Web Client:        https://sub.domain.com/bc
SOAP WebServices:  https://sub.domain.com/bcsoap
OData WebServices: https://sub.domain.com/bcrest
Dev Service:       https://sub.domain.com/bcdev
Snapshot Service:  https://sub.domain.com/bcsnap
File downloads:    https://sub.domain.com/bcdl

Use:
Get-BcContainerEventLog -containerName bc to retrieve a snapshot of the event log from the container
Get-BcContainerDebugInfo -containerName bc to get debug information about the container
Enter-BcContainer -containerName bc to open a PowerShell prompt inside the container
Remove-BcContainer -containerName bc to remove the container again
docker logs bc to retrieve information about URL's again
WARNING: The new settings value will not take effect until you stop and restart the service.
WARNING: The new settings value will not take effect until you stop and restart the service.
WARNING: The new settings value will not take effect until you stop and restart the service.
WARNING: The new settings value will not take effect until you stop and restart the service.
WARNING: The new settings value will not take effect until you stop and restart the service.

PSComputerName : 97a915538cedc9858c41acabbce20eecc9024806eccac7c6dcbd99ff6bb6e78b
RunspaceId     : 751c38e0-f077-47e7-84c9-361abafd771f
ServerInstance : MicrosoftDynamicsNavServer$BC
DisplayName    : Dynamics 365 Business Central Server [BC]
State          : Running
ServiceAccount : NT AUTHORITY\SYSTEM
Version        : 19.0.34506.34941
Default        : True

Skipping font 'lucon.ttf' as it is already installed
Installing font 'c:\Windows\Fonts\arial.ttf' succeeded
Installing font 'c:\Windows\Fonts\arialbd.ttf' succeeded
[..]

bc
Waiting for container bc to be ready

Initializing...
Setting host.containerhelper.internal to 172.28.32.1 in container hosts file
Restarting Container
PublicDnsName unchanged
Hostname is bc
PublicDnsName is sub.domain.com
Using AccessControlService Authentication
Starting Internet Information Server
Starting Service Tier
Container IP Address: 172.28.32.163
Container Hostname  : bc
Container Dns Name  : sub.domain.com
Web Client          : https://sub.domain.com/bc
Dev. Server         : https://sub.domain.com
Dev. ServerInstance : BC
Setting bc to 172.28.32.163 in host hosts file

Files:
http://sub.domain.com:8080/ALLanguage.vsix
http://sub.domain.com:8080/certificate.cer

Container Total Physical Memory is 127.9Gb
Container Free Physical Memory is 54.2Gb

Initialization took 52 seconds
Ready for connections!

Additional context I've read that I can also use Dynamics 365 Business Central (on-premises) as data source in Power BI, but this seems to only support NavUserPassword or Basic Authentication, not sure. I tried with my AAD credentials, but it does not work.

freddydk commented 2 years ago

Skiing in france this week, won't have time to see why this doesn't work. I did test that using https://aka.ms/getbc and setting up O365 auth and LetsEncrypt - that does work with PowerBI - I didn't try with Traefik.

sirhc101 commented 2 years ago

Thanks for feedback. Would you may try it with PowerBI, after your return? At the moment I'm more or less clueless why PowerBI is not trusting. If you need some more details or something pls let me know

sirhc101 commented 2 years ago

I've created a VM using https://aka.ms/getbc (without traefik) and got exactl the same error message from PowerBI image

tfenster commented 2 years ago

FWIW, I see the exact same problem

freddydk commented 2 years ago

And you are using LetsEncrypt? or are you using self-signed certificates? I would expect the behavior with a self-signed certificate, as the PowerBI service doesn't trust that - but LetsEncrypt should work.

tfenster commented 2 years ago

We are using LetsEncrypt

sirhc101 commented 2 years ago

Yes, we too.

HTeunis commented 2 years ago

Same problem here, using a on-premise BC installation on a AzureVM with official bought certificate. We got it running with Postman, but Excel and PowerBI give this error,

Geert commented 2 years ago

This issue is appearing in an on premise 19.x environment in production. @freddydk is a work around possible for our customer?

freddydk commented 2 years ago

For On-premises, the powerbi connector can just use username / password/wskey to become unblocked. I have not had time to investigate on the AAD issue yet,

sirhc101 commented 2 years ago

Hello @freddydk, did you found any time to investigate on this topic? We currently "solved" this problem by creating a separate instance with NavUserPassword authentication, but because this is deprecated (Basic Auth) we would like to switch to OAuth as soon as possible.

freddydk commented 2 years ago

Containers will not stop working with UserPassword auth - and you will be able to use UserPassword even if the service tier is setup for AAD auth

freddydk commented 1 year ago

Assuming that people found a workaround on this issue, closing this.