Issues with "Edit in Excel" for container behind traefik #2595

tfenster · 2022-07-19T19:57:47Z

Describe the issue
When I create a VM through aka.ms/getbc and create a container, I can successfully log in through AAD auth, but the "Edit in Excel" integration fails in two places. One I can fix, or at least I think so, the other I can't fix

Scripts used to create container and cause the issue

Step 1: Create VM and container through aka.ms/getbc

I mostly kept the defaults, but changed those settings:

region = west europe
accept eula = yes
remote desktop = *
OS = Windows Server 2019 with Containers
Vm Size = Standard_D8_v5
set admin password
set contact email for let's encrypt
Add Traefik = yes
Vm Name = repro-aad-vm

The install log looks good to me: install log.txt

Step 2: Remove the first container

Open ISE
Run remove-bccontainer bcserver
Again, the result looks good

Removing Session bcserver
Removing container bcserver
Removing entries from hosts
Removing bcserver from container hosts file
Removing bcserver-* from container hosts file
Removing Desktop shortcuts
Removing C:\ProgramData\BcContainerHelper\Extensions\bcserver

Step 3: Create AAD app regs and container

For this part, I copied the relevant parts from github.com/Microsoft/nav-arm-templates and came up with this script:

$publicDnsName = "repro-aad-vm.westeurope.cloudapp.azure.com"
$Office365UserName = "[email protected]"
$secureOffice365Password = ConvertTo-SecureString -AsPlainText -Force "<redacted>"

$publicWebBaseUrl = "https://$publicDnsName/bcaad/"
$Office365Credential = New-Object System.Management.Automation.PSCredential($Office365UserName, $secureOffice365Password)
$aadTenant = $Office365UserName.split('@')[1]
$appIdUri = "https://$($publicDnsName.Split('.')[0]).$($publicDnsName.Split('.')[1]).$aadTenant"

@"
`$appIdUri = '$appIdUri'
. 'c:\run\SetupConfiguration.ps1'
"@ | Set-Content "c:\myfolder\SetupConfiguration.ps1"

$AdProperties = Create-AadAppsForNav `
        -appIdUri $appIdUri `
        -publicWebBaseUrl $publicWebBaseUrl `
        -IncludeExcelAadApp `
        -IncludePowerBiAadApp `
        -IncludeEMailAadApp `
        -IncludeApiAccess `
        -AadAdminCredential $Office365Credential

$SsoAdAppId = $AdProperties.SsoAdAppId
$SsoAdAppKeyValue = $AdProperties.SsoAdAppKeyValue
$ExcelAdAppId = $AdProperties.ExcelAdAppId
$ExcelAdAppKeyValue = $AdProperties.ExcelAdAppKeyValue
$PowerBiAdAppId = $AdProperties.PowerBiAdAppId
$PowerBiAdAppKeyValue = $AdProperties.PowerBiAdAppKeyValue
$ApiAdAppId = $AdProperties.ApiAdAppId
$ApiAdAppKeyValue = $AdProperties.ApiAdAppKeyValue
$EMailAdAppId = $AdProperties.EMailAdAppId
$EMailAdAppKeyValue = $AdProperties.EMailAdAppKeyValue

@"
Write-Host 'Changing Server config to NavUserPassword to enable basic web services'
Set-NAVServerConfiguration -ServerInstance `$serverInstance -KeyName 'ExcelAddInAzureActiveDirectoryClientId' -KeyValue '$ExcelAdAppId' -WarningAction Ignore
"@ | Add-Content "c:\myfolder\SetupConfiguration.ps1"

$params = @{
        "AadTenant" = $aadTenant
        "AadAppId" =  $SsoAdAppId
        "AadAppIdUri" = $appIdUri
}

$additionalParameters = @("--env RemovePasswordKeyFile=N",
                          "--storage-opt size=100GB")

$myScripts = @()
Get-ChildItem -Path "c:\myfolder" | % { $myscripts += $_.FullName }

New-BcContainer -accept_eula -accept_outdated -containerName bcaad -imageName mybc `
    -artifactUrl (Get-BCArtifactUrl -type OnPrem -country DE -version 20) `
    -authenticationEMail $Office365UserName -auth AAD -credential $Office365Credential `
    -useTraefik -additionalParameters $additionalParameters -myScripts $myscripts @params `
    -dumpEventLog

The startup log also seems fine. The only weird thing is that the health check initially fails, but the automatic restart seems to fix it. At least the container afterwards is healthy. This is the full log output: aad container startup log.txt

Step 4: Login and test "Edit in Excel"

With that in place, login works fine with aad after consent, but if I e.g. go to the open sales orders and then click "edit in Excel", I first need to consent, which also works fine, but then get this

9002326 - [2022-07-19 19:03:29Z]: AADSTS9002326: Cross-origin token redemption is permitted only for the 'Single-Page Application' client-type.
Trace ID: 2c407b2c-153f-488e-b980-e1ed73cc1700
Correlation ID: 0ad4f689-6a92-4890-a06b-1a485470ac5d
Timestamp: 2022-07-19 19:03:29Z - Correlation ID: 0ad4f689-6a92-4890-a06b-1a485470ac5d - Trace ID: 2c407b2c-153f-488e-b980-e1ed73cc1700

This one I can fix if I go into the manifest for the Excel app reg and change the replyUrlsWithType from

        "replyUrlsWithType": [
		{
			"url": "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/*",
			"type": "Web"
		},
		{
			"url": "https://repro-aad-vm.westeurope.cloudapp.azure.com/bcaad/OAuthLanding.htm",
			"type": "Web"
		}
	]

to

        "replyUrlsWithType": [
		{
			"url": "https://az689774.vo.msecnd.net/dynamicsofficeapp/v1.3.0.0/*",
			"type": "Spa"
		},
		{
			"url": "https://repro-aad-vm.westeurope.cloudapp.azure.com/bcaad/OAuthLanding.htm",
			"type": "Web"
		}
	]

and try again, then I get one step further, but still I get an error, this time :

Error Loading Metadata

Details:
Please check server connection, verify the current user has proper access, and try again. Error message: ''

If I connect the Excel addin debugger, I see that in the end a request to https://repro-aad-vm.westeurope.cloudapp.azure.com/BC/ODatav4/$metadata fails, which makes sense as the correct URL would be https://repro-aad-vm.westeurope.cloudapp.azure.com/bcaadrest/ODatav4/$metadata. Now it really becomes guessing for me, but if I look further, I can see a request to https://repro-aad-vm.westeurope.cloudapp.azure.com/bcaad/OfficeApps/Public/ServerConfiguration?_=1658257711509, which returns

{
    "AlternateODataHost": "https://repro-aad-vm.westeurope.cloudapp.azure.com",
    "AppInsightsKey": "",
    "AppletRegistrations": [
        {
            "AppletID": "Microsoft.Dynamics.Platform.Integration.Office.DataConnectorApplet",
            "Description": "Excel Add-In!",
            "Name": "Excel Add-In",
            "RelativeUrl": "/OfficeApps/Applets/ExcelDataConnector/excel.html",
            "RequiredAppVersion": "1.3.0.0",
            "SupportedApplications": 1
        }
    ],
    "AuthType": "OAuth",
    "AuthClientId": "01dd19ea-4057-4465-bda2-76a1f86bcd96",
    "AuthRealm": "https://login.microsoftonline.com/",
    "AuthTenantId": "arssolvendi.onmicrosoft.com",
    "AuthResourceId": "https://repro-aad-vm.westeurope.arssolvendi.onmicrosoft.com",
    "CoreNamespace": "NAV",
    "DataEndpointUrl": "/BC/ODatav4",
    "Features": "DisableKeyEncoding",
    "DataEntityNamespace": "NAV",
    "Headers": {
        "Company": {
            "DisplayName": "Company",
            "Value": "",
            "UseAsQuery": true,
            "ValidValues": [
                "ef964a01-1af8-ec11-82ee-000d3a2448b1"
            ]
        },
        "Prefer": {
            "DisplayName": "Prefer",
            "Value": "odata.continue-on-error",
            "UseAsQuery": false
        }
    },
    "LabelEndpointUrl": "localhost",
    "NullValues": {
        "DateTime": {
            "TimelessDate": "0001-01-01"
        }
    },
    "TypeEnum": {
        "Name": "NavType",
        "MemberMapping": {
            "Date": "Date",
            "Enum": "Enum",
            "Guid": "Guid",
            "Int": "Int",
            "Long": "Int64",
            "Real": "Real",
            "String": "String",
            "Time": "Time",
            "DateTime": "DateTime"
        }
    }
}

The DataEndpointUrl in line 20 is wrong, I think, although the publicodatabaseurl is correctly set in the NAV server config

Welcome to the Business Central Container PowerShell prompt
Microsoft Windows Version 10.0.17763.3046
Windows PowerShell Version 5.1.17763.2931

[BCAAD] PS C:\run> C:\Run\prompt.ps1
Welcome to the Business Central Container PowerShell prompt

[BCAAD] PS C:\run> Get-NAVServerConfiguration BC -KeyName publicodatabaseurl
https://repro-aad-vm.westeurope.cloudapp.azure.com/bcaadrest/odata

Therefore it looks to me like the publicodatabaseurl is not considered properly in the /OfficeApps/Public/ServerConfiguration endpoint. The container log (I set -dumpEventLog on the container) shows a couple of messages, but I can't get any ideas what to change from them: container log.txt

Now the guessing becomes really wild, but I could imagine that this is related to the (very old) problem raised here microsoft/nav-docker#460 and I think also shown in 2019 at NAV TechDays that the BC server in general seems to mostly ignore that setting. Could that be right or am I missing something in my setup?

This whole environment is not connected at all to our corporate AAD, network or any customer data, just running in my playground tenant / subscription / AAD, so I would be happy to give anyone access to the VM or create an AAD user for testing.

Additional context

does it happen all the time? --> Yes, I can reliably repro.
did it use to work? --> Not 100% sure, but yes, I think I have seen this work before

The text was updated successfully, but these errors were encountered:

freddydk · 2022-07-21T15:50:48Z

I can repro this and I think the problem here is the same problem, which causes the URL's in SOAP and OData definition to not be correct.
This is really a bug in BC - which I have tried to get a fix for a few times before, but I haven't been able to.

Thinking whether a workaround could be to share a servicesettings.ps1 file to the my folder containing:

$NavServiceName = 'MicrosoftDynamicsNavServer$BCAADREST'
$WebServerInstance = "BCAAD"
$ServerInstance = "BCAADREST"

where bcaad is the containername.

Having the service tier to be bcaadrest would cause the URL's to match (I think) and OData + API's would work.
SOAP would still be wrong - but who uses SOAP:-)

Also - even if I then get a correct fix for the issue - this workaround would still work.

Issue #2595

freddydk · 2022-07-21T19:37:23Z

I have fixed a few things in the ARM templates and containerhelper if you are running Traefik and AAD (in the pre-release of both)
For one - the ServerInstance and the NavServiceName will be as described above (if Traefik AND AAD)
Now, Web Client and other things will work out of the box (except for SOAP)

You get the dev version of the ARM templates here: https://aka.ms/getbcext2 (add a 2)

Excel app still needs to be changed to SPI app (another fix) - but once that is done - that also works more smoothly.
I still get the same error though - need to investigate what this is, but I don't have an Excel debugger installed.
Event log says antiforgery error, so maybe you can see if you still get the same in Excel.

tfenster · 2022-07-22T20:25:06Z

I can't figure out how you did that, @freddydk. I adopted this change 6757308 locally, then I changed my script like this, so that the ServiceSettings.ps1 is generated correctly

$publicDnsName = "repro-aad-vm.westeurope.cloudapp.azure.com"
$Office365UserName = "[email protected]"
$secureOffice365Password = ConvertTo-SecureString -AsPlainText -Force '<redacted>'
$basename = "BCAAD"

$publicWebBaseUrl = "https://$publicDnsName/$($basename.ToUpperInvariant())/"
$Office365Credential = New-Object System.Management.Automation.PSCredential($Office365UserName, $secureOffice365Password)
$aadTenant = $Office365UserName.split('@')[1]
$appIdUri = "https://$($publicDnsName.Split('.')[0]).$($publicDnsName.Split('.')[1]).$aadTenant"


@"
`$appIdUri = '$appIdUri'
. 'c:\run\SetupConfiguration.ps1'
Write-Host "1: <`$NavServiceName> <`$WebServerInstance> <`$ServerInstance>"
"@ | Set-Content "c:\myfolder\SetupConfiguration.ps1"

@"
`$NavServiceName = 'MicrosoftDynamicsNavServer`$$("$basename".ToUpperInvariant())REST'
`$WebServerInstance = "$($basename.ToUpperInvariant())"
`$ServerInstance = "$($basename.ToUpperInvariant())REST"
Write-Host "2: <`$NavServiceName> <`$WebServerInstance> <`$ServerInstance>"
"@ | Set-Content "c:\myfolder\ServiceSettings.ps1"

$AdProperties = Create-AadAppsForNav `
        -appIdUri $appIdUri `
        -publicWebBaseUrl $publicWebBaseUrl `
        -IncludeExcelAadApp `
        -IncludePowerBiAadApp `
        -IncludeEMailAadApp `
        -IncludeApiAccess `
        -AadAdminCredential $Office365Credential

$SsoAdAppId = $AdProperties.SsoAdAppId
$SsoAdAppKeyValue = $AdProperties.SsoAdAppKeyValue
$ExcelAdAppId = $AdProperties.ExcelAdAppId
$ExcelAdAppKeyValue = $AdProperties.ExcelAdAppKeyValue
$PowerBiAdAppId = $AdProperties.PowerBiAdAppId
$PowerBiAdAppKeyValue = $AdProperties.PowerBiAdAppKeyValue
$ApiAdAppId = $AdProperties.ApiAdAppId
$ApiAdAppKeyValue = $AdProperties.ApiAdAppKeyValue
$EMailAdAppId = $AdProperties.EMailAdAppId
$EMailAdAppKeyValue = $AdProperties.EMailAdAppKeyValue

@"
Write-Host 'Changing Server config to NavUserPassword to enable basic web services'
Set-NAVServerConfiguration -ServerInstance `$serverInstance -KeyName 'ExcelAddInAzureActiveDirectoryClientId' -KeyValue '$ExcelAdAppId' -WarningAction Ignore
"@ | Add-Content "c:\myfolder\SetupConfiguration.ps1"

$params = @{
        "AadTenant" = $aadTenant
        "AadAppId" =  $SsoAdAppId
        "AadAppIdUri" = $appIdUri
}

$additionalParameters = @("--env RemovePasswordKeyFile=N",
                          "--env ExitOnError=N",
                          "--storage-opt size=100GB")

$myScripts = @()
Get-ChildItem -Path "c:\myfolder" | % { $myscripts += $_.FullName }

New-BcContainer -accept_eula -accept_outdated -containerName $basename -imageName mybc `
    -artifactUrl (Get-BCArtifactUrl -type OnPrem -country DE -version 20) `
    -authenticationEMail $Office365UserName -auth AAD -credential $Office365Credential `
    -useTraefik -additionalParameters $additionalParameters -myScripts $myscripts @params `
    -dumpEventLog

If I check the ServiceSettings.ps1, it looks good to me:

$NavServiceName = 'MicrosoftDynamicsNavServer$BCAADREST'
$WebServerInstance = "BCAAD"
$ServerInstance = "BCAADREST"
Write-Host "2: <$NavServiceName> <$WebServerInstance> <$ServerInstance>"

But I get this error when starting:

Creating AAD App for WebClient
Creating AAD App for API Access
Creating AAD App for Excel Add-in
Creating AAD App for PowerBI Service
Creating AAD App for EMail Service
BcContainerHelper is version 3.0.11
BcContainerHelper is running as administrator
Hyper-V is Enabled
UsePsSession is True
Host is Microsoft Windows Server 2019 Datacenter - ltsc2019
Docker Client Version is 20.10.9
Docker Server Version is 20.10.9
Removing Session BCAAD
Removing container BCAAD
Removing Desktop shortcuts
Removing C:\ProgramData\BcContainerHelper\Extensions\BCAAD
Fetching all docker images
Fetching all docker volumes
ArtifactUrl and ImageName specified
Image mybc:onprem-20.3.42673.43026-de already exists
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
Using image mybc:onprem-20.3.42673.43026-de
PublicDnsName is repro-aad-vm.westeurope.cloudapp.azure.com
Creating Container BCAAD
Style: onprem
Multitenant: No
Version: 20.3.42673.43026
Platform: 20.0.42653.43007
Generic Tag: 1.0.2.10
Container OS Version: 10.0.17763.3046 (ltsc2019)
Host OS Version: 10.0.17763.3165 (ltsc2019)
Using hyperv isolation
Using locale de-DE
Adding special CheckHealth.ps1 to enable Traefik support
2: <MicrosoftDynamicsNavServer$BCAADREST> <BCAAD> <BCAADREST>
Additional Parameters:
--env RemovePasswordKeyFile=N
--env ExitOnError=N
--storage-opt size=100GB
-e webserverinstance=BCAAD
-e publicdnsname=repro-aad-vm.westeurope.cloudapp.azure.com
-l "traefik.protocol=https"
-l "traefik.web.frontend.rule=PathPrefix:/BCAAD"
-l "traefik.web.port=443"
-l "traefik.soap.frontend.rule=PathPrefix:/BCAADsoap;ReplacePathRegex: ^/BCAADsoap(.*) /BCAADREST$1"
-l "traefik.soap.port=7047"
-l "traefik.rest.frontend.rule=PathPrefix:/BCAADrest;ReplacePathRegex: ^/BCAADrest(.*) /BCAADREST$1"
-l "traefik.rest.port=7048"
-l "traefik.dev.frontend.rule=PathPrefix:/BCAADdev;ReplacePathRegex: ^/BCAADdev(.*) /BCAADREST$1"
-l "traefik.dev.port=7049"
-l "traefik.snap.frontend.rule=PathPrefix:/BCAADsnap;ReplacePathRegex: ^/BCAADsnap(.*) /BCAADREST$1"
-l "traefik.snap.port=7083"
-l "traefik.dl.frontend.rule=PathPrefixStrip:/BCAADdl"
-l "traefik.dl.port=8080"
-l "traefik.dl.protocol=http"
-l "traefik.enable=true"
-l "traefik.frontend.entryPoints=https"
--env customNavSettings=ValidAudiences=495235d3-02bb-491a-b367-ba8a1ed3607e;https://api.businesscentral.dynamics.com,DisableTokenSigningCertificateValidation=True,ExtendedSecurityTokenLifetime=24,ClientServicesCredentialType=NavUserPassword,PublicODataBaseUrl=https://repro-aa
d-vm.westeurope.cloudapp.azure.com/BCAADrest/odata,PublicSOAPBaseUrl=https://repro-aad-vm.westeurope.cloudapp.azure.com/BCAADsoap/ws,PublicWebBaseUrl=https://repro-aad-vm.westeurope.cloudapp.azure.com/BCAAD
--env customWebSettings=AadApplicationId=495235d3-02bb-491a-b367-ba8a1ed3607e,AadAuthorityUri=https://login.microsoftonline.com/arssolvendi.onmicrosoft.com
Files in C:\ProgramData\BcContainerHelper\Extensions\BCAAD\my:
- AdditionalOutput.ps1
- CheckHealth.ps1
- ServiceSettings.ps1
- SetupConfiguration.ps1
- SetupVariables.ps1
- SetupWebClient.ps1
- SetupWindowsUsers.ps1
- updatecontainerhosts.ps1
Creating container BCAAD from image mybc:onprem-20.3.42673.43026-de
3d0a42abd56690a07ca541c66fa9c1635268f8e950b328f10ee4ba7dbdcb6cb0
Waiting for container BCAAD to be ready
Initializing...
2: <MicrosoftDynamicsNavServer$BCAADREST> <BCAAD> <BCAADREST>
Setting host.containerhelper.internal to 172.30.128.1 in container hosts file
Starting Container
Hostname is BCAAD
PublicDnsName is repro-aad-vm.westeurope.cloudapp.azure.com
Using AccessControlService Authentication
Starting Local SQL Server
Starting Internet Information Server
Creating Self Signed Certificate
Self Signed Certificate Thumbprint 4618717FDE69543B2838182D154D078E5BFFA8C6
DNS identity repro-aad-vm.westeurope.cloudapp.azure.com
Modifying Service Tier Config File with Instance Specific Settings
Modifying Service Tier Config File with settings from environment variable
Setting ValidAudiences to 495235d3-02bb-491a-b367-ba8a1ed3607e;https://api.businesscentral.dynamics.com
Setting DisableTokenSigningCertificateValidation to True
Setting ExtendedSecurityTokenLifetime to 24
Setting ClientServicesCredentialType to NavUserPassword
Setting PublicODataBaseUrl to https://repro-aad-vm.westeurope.cloudapp.azure.com/BCAADrest/odata
Setting PublicSOAPBaseUrl to https://repro-aad-vm.westeurope.cloudapp.azure.com/BCAADsoap/ws
Setting PublicWebBaseUrl to https://repro-aad-vm.westeurope.cloudapp.azure.com/BCAAD
1: <MicrosoftDynamicsNavServer$BCAADREST> <BCAAD> <BC>
Changing Server config to NavUserPassword to enable basic web services
Service Tier doesn't exist / is not installed
at <ScriptBlock>, C:\Run\navstart.ps1: line 169
at <ScriptBlock>, C:\Run\start.ps1: line 377
at <ScriptBlock>, <No file>: line 1
Starting EventLog Monitor
Monitoring EventSources from EventLog[Application]:
- MicrosoftDynamicsNavServer$BC
- MSSQL$SQLEXPRESS
Error
Initializing...
2: <MicrosoftDynamicsNavServer$BCAADREST> <BCAAD> <BCAADREST>
Setting host.containerhelper.internal to 172.30.128.1 in container hosts file
Starting Container
Hostname is BCAAD
PublicDnsName is repro-aad-vm.westeurope.cloudapp.azure.com
Using AccessControlService Authentication
Starting Local SQL Server
Starting Internet Information Server
Creating Self Signed Certificate
Self Signed Certificate Thumbprint 4618717FDE69543B2838182D154D078E5BFFA8C6
DNS identity repro-aad-vm.westeurope.cloudapp.azure.com
Modifying Service Tier Config File with Instance Specific Settings
Modifying Service Tier Config File with settings from environment variable
Setting ValidAudiences to 495235d3-02bb-491a-b367-ba8a1ed3607e;https://api.businesscentral.dynamics.com
Setting DisableTokenSigningCertificateValidation to True
Setting ExtendedSecurityTokenLifetime to 24
Setting ClientServicesCredentialType to NavUserPassword
Setting PublicODataBaseUrl to https://repro-aad-vm.westeurope.cloudapp.azure.com/BCAADrest/odata
Setting PublicSOAPBaseUrl to https://repro-aad-vm.westeurope.cloudapp.azure.com/BCAADsoap/ws
Setting PublicWebBaseUrl to https://repro-aad-vm.westeurope.cloudapp.azure.com/BCAAD
1: <MicrosoftDynamicsNavServer$BCAADREST> <BCAAD> <BC>
Changing Server config to NavUserPassword to enable basic web services
Service Tier doesn't exist / is not installed
at <ScriptBlock>, C:\Run\navstart.ps1: line 169
at <ScriptBlock>, C:\Run\start.ps1: line 377
at <ScriptBlock>, <No file>: line 1
Starting EventLog Monitor
Monitoring EventSources from EventLog[Application]:
- MicrosoftDynamicsNavServer$BC
- MSSQL$SQLEXPRESS

New-BcContainer Telemetry Correlation Id: 5df883aa-0991-404b-b72c-637993c32dad
Initialization of container BCAAD failed
At C:\Program Files\WindowsPowerShell\Modules\bccontainerhelper\3.0.11\ContainerHandling\Wait-NavContainerReady.ps1:42 char:17
+ ...             throw "Initialization of container $containerName failed" ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Initialization of container BCAAD failed:String) [], RuntimeException
    + FullyQualifiedErrorId : Initialization of container BCAAD failed

The reason imho is that the service name is defined here https://github.com/microsoft/nav-docker/blob/master/generic/Run/150-new/navinstall.ps1#L309 during install, so when I start the container, it checks for MicrosoftDynamicsNavServer$BCAADREST as service name, but only MicrosoftDynamicsNavServer$BC exists, because that was set when the image was created.

When I check the service in the container, it also shows BC instead of BCAADREST:

PS C:\> get-service | Where-Object { $_.ServiceName.StartsWith("MicrosoftDyn") }  | Format-List

Name                : MicrosoftDynamicsNavServer$BC
DisplayName         : Dynamics 365 Business Central Server [BC]
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {HTTP}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

What am I missing?

freddydk · 2022-07-24T22:17:51Z

Try to remove the -imagename
MyScripts are not transferred from New-BcContainer when JIT-building the image - hence you will have the wrong ServiceSettings (reason a lot of these scripts are instance specific)

You can call New-BcImage manually (which also has a myscripts) and then pre-build your image before calling New-BcContainer.

tfenster · 2022-07-25T06:25:06Z

But that would mean building a new image for every container? I'll try, but if that is true, that wouldn't be a practical workaround

tfenster · 2022-07-25T07:23:50Z

The issue is that the metadata request is case-sensitive: The Excel add-in tried to get https://.../BCAADREST/ODatav4/$metadata, which resulted in a 404 as it should be https://.../BCAADrest/ODatav4/$metadata, because the traefik labels are defined lowercase. The solution is that the instance needs to be named BCAADrest instead of BCAADREST. For my script:

@"
`$NavServiceName = 'MicrosoftDynamicsNavServer`$$("$basename".ToUpperInvariant())REST'
`$WebServerInstance = "$($basename.ToUpperInvariant())"
`$ServerInstance = "$($basename.ToUpperInvariant())rest"
"@ | Set-Content "c:\myfolder\ServiceSettings.ps1"

I think with that we have validated that it indeed is the very old bug which is haunting as again here. As I wrote above, while this is indeed a workaround, it is not a very practical one as it would require us to create a new image for every container :(

Do you know what the problem is with solving this bug? Would it help to create an official bug report? This is "only" demo/test/dev environments for now, but that might change in the future and even for those scenarios this already hurts quite a bit. Obviously, it is an amazing demo feature, but also for test/dev it can be quite handy for adding or changing data.

Oh and just FYI: The Excel debugger (for add-ins) is the well-known web console. You can launch it in Excel with the little arrow in the top right corner of the add-in window and then "attach debugger":

freddydk · 2022-07-25T07:27:16Z

Yeah - the image would of course have the service tier name stamped in:-(
I guess I could make a small change to the generic image, which would recreate the service tier if the name has changed - if this would solve anything.

tfenster · 2022-07-25T07:31:48Z

I actually don't like the "solution" anyway. The fact that the instance always is called "BC" (unless someone really goes out of the way to change it) is nice, in my opinion. But your answer seems to indicate that there is no way that we can get a real solution for this?

freddydk · 2022-07-25T07:38:39Z

I don't like the "solution" either.
I will do one more attempt to get a fix for this - I will create a bug in our system.
The other bugs have just mentioned that the discovery was wrong - this one actually blocks functionality.

tfenster · 2022-07-25T07:40:11Z

Great, thanks. Let me know if it helps to also create an official service request through support. I am pretty sure that I could ask other partners to also create one, but I don't know if that helps or not

freddydk · 2022-07-25T07:40:34Z

I will reference this issue in our bug system.

freddydk · 2022-07-25T08:24:29Z

Looking in our system, i found several bugs concerning the same - will try to get them prioritized.
This is also a problem if you have 2 service tiers behind a load balancer in an on-prem environment - or a service tier behind a proxy.

tfenster · 2022-10-14T06:27:45Z

@freddydk Do you know whether there is any progress on this issue?

freddydk · 2022-10-14T06:32:53Z

will checkup with Jens

tfenster · 2023-01-25T09:35:21Z

@freddydk Three months later 😊 Any update on this? I just now tried with 21.3, but the issue seems to be the same still (didn't run the debugger, but symptoms are unchanged)

NavNab · 2023-03-11T15:24:00Z

Hello @freddydk @tfenster
Did you manage to make it work?
Thanks in advance

tfenster · 2023-03-11T15:35:31Z

No progress that I am aware of

freddydk · 2023-05-06T16:59:55Z

Just re-requested a status

tfenster · 2023-06-15T06:33:04Z

Just re-requested a status

But no response?

freddydk · 2023-06-15T07:42:46Z

Just saw that the fix for this should be in 22.2 - but I haven't checked myself.

tfenster · 2023-06-15T08:36:44Z

I just tried DE Business Central 22.2 (Platform 22.0.57579.0 + Application 22.2.56969.57617) and the issue still exists there

tfenster · 2023-06-15T13:38:07Z

Also directly talking to odata still returns bad data:

You can see the requested URL is ....azure.com/f08e3b15a259rest/ODataV4... but the context in the payload is still returned as ...azure.com/BC/ODataV4... although the PublicODataBaseUrl is properly set

NavNab · 2023-10-11T20:15:10Z

any chance the fix was shipped with v23?

warlof · 2024-01-25T14:05:58Z

unfortunately, it appears it's still valid with v23...
endpoint discovery is not taking care of the NST configuration :(

warlof · 2024-01-31T09:46:11Z

@tfenster @freddydk while it is still annoying, I might have found an alternative which might be easier to handle excel callback.

Instead naming container server instance BC - we will name the instance as same as the container itself (uppercase) - but without all the rest part.
This way, we got a pivot on the URI generated by the instance with its web services metadata, allowing us to know from which container the call was coming. At the end, it's all we need.

This is an involve from Tobias scripting :

@"
`$NavServiceName = 'MicrosoftDynamicsNavServer`$$("$container".ToUpperInvariant())'
`$WebServerInstance = "$($container.ToUpperInvariant())"
`$ServerInstance = "$($container.ToUpperInvariant())"
"@ | Set-Content $serviceSettingsOverrideScriptPath
    $myScripts += @($serviceSettingsOverrideScriptPath)

together with that change, we will add an extra traefik rule which will redirect all calls with an endpoint looking like /container/odatav4/... which we will redirect to OData component backend :

    $excelRule = "PathPrefix:/{path:(?i:$($container.ToUpperInvariant()))/(?i:odatav4)}"
    $additionalParameters += @(
		"-l `"traefik.excel.frontend.rule=$excelRule`"",
		"-l `"traefik.excel.port=7048`""
    )

the magic here is the ?i: in the used regex mask which allow us to introduce case insensitivity - so anyway the container or odata values are spelled, the callback will work.

I tested this change with OnPrem container (thank Tobias for the work here) - I'm still testing it on sandbox container type though.

tfenster · 2024-08-20T13:52:52Z

Just to confirm, the problem still exists with 24.4

Happy fifth anniversary to this bug

NavNab · 2024-08-20T13:58:10Z

I've given up hope that it will ever be fixed 😅

MatthiasSchaefer · 2025-01-29T19:05:15Z

Hi @tfenster,
I tested it with DE Business Central 24.2 (Platform 24.0.20409.0 + Application 24.2.20227.20424) and I got a different result for the OData Connections.

I refer to this message:

Just to confirm, the problem still exists with 24.4

Happy fifth anniversary to this bug

My tests of the OData service results in a correct "@odata.context":

I have manually installed 2 different OnPrem BC instances (without Docker and without navcontainerhelper). Using the following bc instance configuration:

1st BC instance:

ServerInstance = BC_Client_Instance
ClientServicesEnabled = true
ODataServicesEnabled = false
PublicODataBaseUrl = https://LetsHaveBullShitHere
PublicWebBaseUrl = https://MoreBullShitHere

2nd BC instance

ServerInstance = BC_WebService_Instance
ClientServicesEnabled = false
ODataServicesEnabled = true
ODataServicesPort = 1234
PublicODataBaseUrl = https://bcServiceTier.mydomain.xx:1234/BC_WebService_Instance/ODataV4/
PublicWebBaseUrl =https://bcWebserver.mydomain.xx/BC_Client_Instance

OData works with this setup as expected. I would say that the odata results is not related to the result of the office app server configuration.

Unfortunately I have the same issue with office app server configuration as you described in your first posting. Calling the https://bcWebserver.mydomain.xx/BC_Client_Instance/OfficeApps/Public/ServerConfiguration results in correct "AlternateODataHost", but in a wrong "DataEndpointUrl".

AlternateODataHost results in "https://bcServiceTier.mydomain.xx:1234" (correct)
DataEndpointUrl results in "BC_Client_Instance" (wrong. Expected to be "BC_WebService_Instance")

tfenster · 2025-01-29T19:31:54Z

@MatthiasSchaefer It looks to me like you talk about a different setup where you have different BC instance and WebClient instance names, but not behind a reverse proxy, right? If yes, then you have a similar problem with the same bad result. I don't know if and how the DataEndpointUrl can be influenced, but maybe @freddydk knows himself or knows someone who knows?

MatthiasSchaefer · 2025-01-29T20:33:13Z

different setup where you have different BC instance and WebClient instance names, but not behind a reverse proxy

Exactly, that's correct.

tfenster assigned freddydk Jul 19, 2022

freddydk pushed a commit that referenced this issue Jul 21, 2022

Issue #2595

6757308

freddydk added a commit that referenced this issue Jul 21, 2022

Merge pull request #2608 from microsoft/dev

5ea7b9b

Issue #2595

freddydk added the Unrelated to BcContainerHelper label Jul 26, 2022

freddydk mentioned this issue May 6, 2023

Using container with Traefik result in invalid SOAP Public base URL #1895

Closed

freddydk added the bug label May 6, 2023

freddydk mentioned this issue May 6, 2023

Docker with Traefik: odata.context URL are not correct #2137

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Issues with "Edit in Excel" for container behind traefik #2595

Issues with "Edit in Excel" for container behind traefik #2595

tfenster commented Jul 19, 2022 •

edited

Loading

freddydk commented Jul 21, 2022 •

edited

Loading

freddydk commented Jul 21, 2022

tfenster commented Jul 22, 2022

freddydk commented Jul 24, 2022

tfenster commented Jul 25, 2022

tfenster commented Jul 25, 2022 •

edited

Loading

freddydk commented Jul 25, 2022

tfenster commented Jul 25, 2022

freddydk commented Jul 25, 2022

tfenster commented Jul 25, 2022

freddydk commented Jul 25, 2022

freddydk commented Jul 25, 2022

tfenster commented Oct 14, 2022

freddydk commented Oct 14, 2022

tfenster commented Jan 25, 2023

NavNab commented Mar 11, 2023

tfenster commented Mar 11, 2023

freddydk commented May 6, 2023

tfenster commented Jun 15, 2023

freddydk commented Jun 15, 2023

tfenster commented Jun 15, 2023

tfenster commented Jun 15, 2023

NavNab commented Oct 11, 2023

warlof commented Jan 25, 2024

warlof commented Jan 31, 2024

tfenster commented Aug 20, 2024

NavNab commented Aug 20, 2024

MatthiasSchaefer commented Jan 29, 2025

tfenster commented Jan 29, 2025

MatthiasSchaefer commented Jan 29, 2025

Issues with "Edit in Excel" for container behind traefik #2595

Issues with "Edit in Excel" for container behind traefik #2595

Comments

tfenster commented Jul 19, 2022 • edited Loading

freddydk commented Jul 21, 2022 • edited Loading

freddydk commented Jul 21, 2022

tfenster commented Jul 22, 2022

freddydk commented Jul 24, 2022

tfenster commented Jul 25, 2022

tfenster commented Jul 25, 2022 • edited Loading

freddydk commented Jul 25, 2022

tfenster commented Jul 25, 2022

freddydk commented Jul 25, 2022

tfenster commented Jul 25, 2022

freddydk commented Jul 25, 2022

freddydk commented Jul 25, 2022

tfenster commented Oct 14, 2022

freddydk commented Oct 14, 2022

tfenster commented Jan 25, 2023

NavNab commented Mar 11, 2023

tfenster commented Mar 11, 2023

freddydk commented May 6, 2023

tfenster commented Jun 15, 2023

freddydk commented Jun 15, 2023

tfenster commented Jun 15, 2023

tfenster commented Jun 15, 2023

NavNab commented Oct 11, 2023

warlof commented Jan 25, 2024

warlof commented Jan 31, 2024

tfenster commented Aug 20, 2024

NavNab commented Aug 20, 2024

MatthiasSchaefer commented Jan 29, 2025

tfenster commented Jan 29, 2025

MatthiasSchaefer commented Jan 29, 2025

tfenster commented Jul 19, 2022 •

edited

Loading

freddydk commented Jul 21, 2022 •

edited

Loading

tfenster commented Jul 25, 2022 •

edited

Loading