New-NAVWebServerInstance – The data is invalid (0x8007000D)

When trying to install a new web client instance of Business Central 19.1 on Windows Server 2022 I ran into this error:

Set-WebConfigurationProperty : The data is invalid. (Exception from HRESULT: 0x8007000D)
At C:\Program Files\Microsoft Dynamics 365 Business Central\xxxxxx\Service\NAVWebClientManagement.psm1:654 char:5

• Set-WebConfigurationProperty -Filter ‘/system.webServer/security/ …
• ~~~~~~~~~~~~~
  • CategoryInfo : NotSpecified: (:) [Set-WebConfigurationProperty], COMException
  • FullyQualifiedErrorId : System.Runtime.InteropServices.COMException,Microsoft.IIs.PowerShell.Provider.SetConfigurationPropertyCommand

Set-WebConfigurationProperty : The data is invalid. (Exception from HRESULT: 0x8007000D)
At C:\Program Files\Microsoft Dynamics 365 Business Central\xxxxxx\Service\NAVWebClientManagement.psm1:655 char:5

• Set-WebConfigurationProperty -Filter ‘/system.webServer/security/ …
• ~~~~~~~~~~~~~
  • CategoryInfo : NotSpecified: (:) [Set-WebConfigurationProperty], COMException
  • FullyQualifiedErrorId : System.Runtime.InteropServices.COMException,Microsoft.IIs.PowerShell.Provider.SetConfigurationPropertyCommand

The error was solved by downloading and installing the latest version of .Net Runtime from https://dotnet.microsoft.com/download/dotnet/5.0/runtime (Choose “Download Hosting Bundle”). In my case the latest version was 5.0.11. After installing this version the problem was gone.

Setting Windows time zone with Powershell

I was having some trouble changing the time zone from the control panel in Windows Server 2019, got this error ‘You do not have permission to perform this task…’. So I thougt I would just do it from Powershell instead.

Run an elevated Powershell (run as administrator) and use this command:

Get-TimeZone -ListAvailable | Out-GridView -PassThru | Set-TimeZone

This will show you a list of the available time zones and change the time zone to the one you choose.

Databaseversionno and NAV build versions

I have been working on a Powershell script that can combine the Databaseversionno from the table $ndo$dbproperty in a NAV database with the NAV build version. I will post my script here soon.

These are the combinations that I have found so far.

Databaseversionno	NAV build version
140	6.0.27808.0
150	6.0.29626.0
60200	6.0.32813.0
60210	6.0.33750.0
70340	7.0.42591.0
70340	7.0.36347.0
70340	7.0.49750.0
70340	7.0.40468.0
70730	7.1.38455.0
70730	7.1.41768.0
70730	7.1.36703.0
70740	7.1.42221.0
70740	7.1.44366.0
70750	7.1.49751.0
70750	7.1.46057.0
71040	8.0.38457.0
71040	8.0.37874.0
71050	8.0.40938.0
71050	8.0.39663.0
71061	8.0.42222.0
71061	8.0.43389.0
71062	8.0.45483.0
71063	8.0.47254.0
80190	9.0.44365.0
80190	9.0.44974.0
80190	9.0.46773.0
80190	9.0.42815.0
80190	9.0.43402.0
80211	9.0.46773.0
80211	9.0.46621.0
80212	9.0.47444.0
91470	10.0.14767.0
91470	10.0.14199.0
91470	10.0.15140.0
91470	10.0.13682.0
91481	10.0.16996.0
91483	10.0.18197.0
91484	10.0.18609.0
91485	10.0.19831.0
91485	10.0.18976.0
91487	10.0.29965.0
91487	10.0.21440.0
91487	10.0.18197.0
100550	11.0.19394.0
100550	11.0.19846.0
100570	11.0.20783.0
100580	11.0.21441.0
100581	11.0.23019.0
130270	13.0.26413.0
130270	13.0.25924.0
130270	13.0.27183.0
130270	13.0.34560.0
130450	14.0.35916.0

I will post more about this later.I’m going to use this to make another Powershell script, that will be able to install a NAV Server-tier with only the database name as a parameter.

PS. If anyone has a similar list I would be very happy if you would send me a copy or show me where to find it.

Sync-NAVTenant: Object reference not set to an instance of an object

In Business Central Spring 2019 CU04 on Azure SQL I got this error when I was trying to run Sync-NAVTenant after mounting a default tenant:

PS C:\Windows\system32> Sync-NAVTenant -ServerInstance xxx -Tenant default -Mode Sync

MicrosoftDynamicsNavServer$xxx/default
Confirm synchronization of default tenant in Sync mode
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): y
Sync-NAVTenant : Object reference not set to an instance of an object.
At line:1 char:1
+ Sync-NAVTenant -ServerInstance xxx -Tenant default -Mode Sync
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (0:Int32) [Sync-NAVTenant], NavCommandException
+ FullyQualifiedErrorId : MicrosoftDynamicsNavServer$spy/default,Microsoft.Dynamics.Nav.Management.Cmdlets.SyncNavTenant

In the event log there was an event with ID 701 and it looked like this:

Server instance: xxx
Category: DatabaseSync
ClientSessionId: 00000000-0000-0000-0000-000000000000
ClientActivityId: cf4f8801-ea24-4177-9259-aedeb86d4629
ServerSessionUniqueId: 56e1965f-3b8a-43bb-be36-664b185b07cd
ServerActivityId: ae13f927-b596-455e-a453-dbe665cc525d
EventTime: 10/07/2019 09:25:47
Message (NullReferenceException): RootException: NullReferenceException
Object reference not set to an instance of an object.
ExceptionStackTrace:
   at Microsoft.Dynamics.Nav.Runtime.DeadlockAnalyzer.SetupEventSessionForDatabase(NavDatabase database)
   at Microsoft.Dynamics.Nav.Runtime.NavEnvironment.<>c.<SetupDeadlockAnalyzer>b__213_0(NavDatabase database)
   at Microsoft.Dynamics.Nav.Runtime.NavDatabase.OnFirstDatabaseAccess()
   at Microsoft.Dynamics.Nav.Runtime.NavTenant.OnFirstDatabaseAccess()
   at Microsoft.Dynamics.Nav.Runtime.NavSqlDatabaseSync.EnsureDatabaseInSync(SyncMode syncMode, Boolean perTable, Boolean sharedSchema)
CallerStackTrace:
   at Microsoft.Dynamics.Nav.Runtime.NavSqlDatabaseSync.EnsureDatabaseInSync(SyncMode syncMode, Boolean perTable, Boolean sharedSchema)
   at Microsoft.Dynamics.Nav.Runtime.NavSqlDatabaseSync.<>c__DisplayClass116_0.<StartProcessIfNotAlreadyStarted>b__0()
   at Microsoft.Dynamics.Nav.Runtime.NavTaskFactory.<>c__DisplayClass1_0.<RunTask>b__0()
   at System.Threading.Tasks.Task.Execute()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
   at System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)
   at System.Threading.ThreadPoolWorkQueue.Dispatch()

I noticed the word DeadlockAnalyzerin the ExceptionStackTrace. I went to ServiceTierAdministration and unchecked ‘Enable Deadlock Monitoring’, restarted the service and re-ran the sync-navtentant cmdlet without errors.

Import-NAVData into an empty database

I have been struggling with this a few times, and I was sure that I succeeded earlier when trying to import a .navdata file into an empty SQL database, so this time I’m going to write a short post about it.

Looks like it is really quite simple.

If you have a .navdata file that contains application, application data, global data and some company data as well, then you can import this with PowerShell directly into an empty SQL Server database.

First you need to create the empty database and you can do this from Microsoft SQL Server Management Studio or the Azure Portal if you are on Azure SQL.

Then open up PowerShell and import your NAV-modules. I use the Import-NAVModules cmdlet from Cloud.Ready.Software.NAV, but you can also use…

Import-module “C:\Program Files (x86)\Microsoft Dynamics NAV\11.0.20783\Service\Microsoft.Dynamics.Nav.Management.dll”

Then run Import-NAVData…

Import-NAVData -DatabaseServer <SQL server name> -DatabaseName <Database name> -ApplicationDatabaseServer <SQL server name> -ApplicationDatabaseName <Database name> -FilePath “C:\temp\backup.navdata” -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -CommitPerTable -Force

Note this example is a single tenant database. If you are using SQL server instances you can type mysqlserver\myinstance under -DatabaseServer and -AppliationDatabaseServer.

Does it work on Azure SQL? Yes it does…

Import-NAVData -DatabaseServer $azuresql -DatabaseName $azuresqldb -DatabaseCredentials $azuresqlcred -ApplicationDatabaseServer $azuresql -ApplicationDatabaseName $azuresqldb -ApplicationDatabaseCredentials $azuresqlcred -FilePath “C:\temp\backup.navdata” -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -CommitPerTable -Force

If you omit the -Force parameter you will get this…

Import-NAVData : System table $ndo$dbproperty is missing or database MyDatabase is not a Dynamics NAV application database
.
At line:1 char:1
+ Import-NAVData -DatabaseServer $azuresql -DatabaseName $azuresqldb -D …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (0:Int32) [Import-NAVData], NavSqlException
+ FullyQualifiedErrorId : agidonsql2.database.windows.net\nhtest2,Microsoft.Dynamics.Nav.Management.Cmdlets.Import
NavData

EDIT: I noticed that the actual company data was not imported with the above commands, not sure why. But if you experience the same you can always run the commands again without the -IncludeApplication, -IncludeApplicationData and -IncludeGlobalData parameters.

Also I got this message on Azure, when the Import-NAVData command finished…

Import-NAVData : Error while trying to set the collation for database nhtest2 containing tenant default. The error was:
The following SQL error was unexpected:
The database could not be exclusively locked to perform the operation.
ALTER DATABASE failed. The default collation of database ‘nhtest2’ cannot be set to Danish_Greenlandic_100_CS_AS.

I tried deleting the database and recreating it from the Azure Portal specifying ‘Danish_Greenlandic_100_CS_AS’ as collation. After that I was able to restore everything to the Azure SQL database.

 

RDP – An internal error has occured

I’m using a Let’s Encrypt SSL certificate with Remote Desktop Services and have set up a script to automatically renew the certificate and also update the certificate for the Remote Desktop Services.

The script renews the certificate just fine but after the has been rebooted I can no longer connect via RDP and I get the error ‘An internal error has occured’. Not very informative. The server is a Azure VM and I tried every suggestion I could find including restarting, redeploying and resetting the password. The boot diagnostics wouldn’t even update the screenshot.

I bet there are more ways to solve this, but here’s what works for me.

On another server in the same domain, start the Server Manager. Click on ‘Add other servers to manage’ and select the troubled server and add it. Wait while the servers roles are being fetched. The ‘Remote Desktop Services’ role should appear on the list to the left. Click on it and on the ‘DEPLOYMENT OVERVIEW’ under ‘TASKS’ select ‘Edit Deployment Properties’.

Now select Certificates, select ‘RD Connection Broker – Enable Single Sign On’ and click the ‘Select existing certificate…’ button.

Enter the password for the certificate (or choose a different certificate) and mark ‘Allow the certificate to be added to the Trusted Root…’. Click OK.

That’s what I had to do to fix my problem, hope this will save someone else some time. Now I just have to figure out what I have to do to prevent it from happening every time the certificate is renewed.

How to restore Microsoft SQL database from Azure blob (with special characters in logical name)

I was struggling with this and now I found a solution I would like to share 🙂

I have setup Managed Backup on a SQL server and now I have a lot of backupfiles located in a storage account on Azure. I needed to figure out how to use these files for database restore. First I tried simply using SQL Server Management Studio, but I didn’t get very far. I can select the blob storage, but for some reason it does not show any files?

Next option was restoring using a query.

I wanted to restore the database to a new server and I want the database files to be located in another folder. To do that I need to know the logical names of all files in the database backup, you can get that by using this query…

RESTORE FILELISTONLY

FROM URL = 'https://mybackupstorage.blob.core.windows.net/mybackupcontainer/MyDatabase 2015_726ef093fa6d4165b0492221922832bb_20170614141530+02.bak'

 

The result of this query lists the logical name of alle database files, and the physical location of the files on the server where the backup was taken.

Notice that the above logical names contains parentheses. I could not find a way to escape these, so instead I used variables.

DECLARE @fn1 VARCHAR(50)

DECLARE @fn2 VARCHAR(50)

SET @fn1 = 'Demo Database NAV (7-0)_data'

SET @fn2 = 'Demo Database NAV (7-0)_log'

RESTORE DATABASE [My database 2015]

FROM URL = 'https://mybackupstorage.blob.core.windows.net/mybackupcontainer/Mydatabase 2015_726ef093fa6d4165b0492221922832bb_20170614141530+02.bak'

WITH

MOVE @fn1 TO 'C:\SQL data\Mydatabase2015_Data.mdf'

,MOVE @fn2 TO 'C:\SQL data\Mydatabase2015_Log.ldf'

 

The result should hopefully be something like this…

Azure SQL Long-term backup retention

Azure SQL Long-term retention just want into public preview. I was looking for a guide to set this up, but I couldn’t find anything, so here is my own little guide 🙂

• Log into your Azure portal (https://portal.azure.com)
• Click on  and search for ‘backup’. Click the result ‘Backup and Site Recovery (OMS)’ to create a new Recovery Services Vault.

• Fill out the form. Select the subscription that also contains your SQL server
• Select the Resource group that your SQL server is also a member of
• Select your preferred Location
• Click Create
• Wait a few seconds while your Recovery Services vault is being created
• Go into SQL Servers
• Click on your SQL server
• Click on the new setting called “Long-term backup retention”

• If you have not yet accepted the preview terms you will see this message:

• Click the message, click the checkbox that shows up, and click the OK button
• Select the databases you would like to backup
• Click the Configure button on top of the pane
• Select your Recovery service vault
• Create a new retention policy by filling out the name and choosing the retention period
• Click OK

• Click the Save button on top of the pane

• That should be it 🙂

Loading all NAV cmdlets in your Powershell profile

If you often need to run both the NAV Development Shell and the NAV Administration Shell and your tired of opening up two windows or tired of loading both modules manually here is a solution.

First of all, you must check to see if you already have a profile, if not you have to create one. Please check out this post to see how it’s done (http://www.howtogeek.com/50236/customizing-your-powershell-profile/)

I copied most of the examples from the above site, but had to change a few things because I got some errors and warnings. Also I added the two Import-Module lines to load the NAV cmdlets. Here is what I ended up with…

Set-Location C:
$Shell = $Host.UI.RawUI
$size = $Shell.WindowSize
$size.width=200
$size.height=60
$Shell.WindowSize = $size
$size = $Shell.BufferSize
$size.width=200
$size.height=2000
$Shell.BufferSize = $size
$Shell.WindowTitle="NAV console"
Import-Module 'C:\Program Files\Microsoft Dynamics NAV\90\Service\NavAdminTool.ps1'
Import-Module 'C:\Program Files (x86)\Microsoft Dynamics NAV\90\RoleTailored Client\Microsoft.Dynamics.Nav.Model.Tools.psd1'
Clear-Host
Write-Host -ForegroundColor Green "All NAV 2016 cmdlets has been loaded."

It’s easier to copy the code from here.

NAV Multitenant sync

I haven’t worked with multitenant database before, so I had to figure out how to get the database objects updated. It’s not a big deal really, but here is how I did it, using Powershell of course 😀

First I tried to run this command

(You need to run this from the Dynamics NAV 20xx Delelopment Shell)

Import-NAVApplicationObject -Path "\\tsclient\U\Nicolai\300516.fob" -NavServerInstance MyInstance -DatabaseName "My 2016 APP"
 -DatabaseServer mydatabaseserver.database.windows.net -LogPath 'C:\Temp\logfile.txt' -ImportAction Overwrite -SynchronizeSchemaChanges Yes -Username <your_user_name> -Password <your_password>

And I ran into this error message:

Check that:
. The Microsoft Dynamics NAV Server is running
. There is only one tenant mounted on the server instance.
At line:1 char:1
+ Import-NAVApplicationObject -Path "\\tsclient\U\Nicolai\300516.fob" - ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Import-NAVApplicationObject

Then I tried change the SynchronizeSchemaChanges to No, like this:

Import-NAVApplicationObject -Path "\\tsclient\U\Nicolai\300516.fob" -NavServerInstance MyInstance -DatabaseName "My 2016 APP"
-DatabaseServer mydatabaseserver.database.windows.net -LogPath 'C:\Temp\logfile.txt' -ImportAction Overwrite -SynchronizeSchemaChanges No -Username <your_user_name> -Password <your_password>

And voila, it worked! But we have not synchronized the schema changes yet, take a look at the state of your tenants, either in the NAV Administration GUI or with the cmdlet Get-NAVTenant -ServerInstance myinstance (from NAV Administration Shell).

Notice the state of your tenants. The default instance says might say mounted but other tenants say OperationalWithSyncPending. If you are ready to sync one or more of your tenants you have to run the Sync-NAVTenant cmdlet from your NAV Administration Shell.

Sync-NAVTenant -ServerInstance myNAVInstance -Tenant myTenant -Mode Sync -Force

That should be it. You might need to set mode to ForceSync instead of Sync, this is required when the table changes cause loss of data. Please note that you need to sync the default instance as well, it’s state should also be Operational.

If you run the Get-NAVTenant cmdlet again you will see that the state has changed to Operational.

If you want to sync all your tenants at once you could do something like this:

Get-NAVTenant -ServerInstance myNAVInstance | Sync-NAVTenant -Mode ForceSync -Force

You can add the -Verbose parameter if you want more output, and you can leave out the -Force parameter if you want to confirm every tenant and maybe skip some of them.

Or take a look at Waldo’s blog – http://www.waldo.be/2014/07/17/nav-2013-r2-multi-tenancy-force-full-sync-on-all-tenants/ if you want to control the output you get.