Business Central Musings

This is a second article targeting BC On-Prem installations.

First one referred to auditing BC cmdlets usage using powershell transcription.

Give it a read!

PowerShell transcription: Extension Management auditing for Business Central on-prem – Business Central Musings (svirlan.com)

Working with encryption keys for NAV/BC server instances

When your NAV/BC database is outside of your domain network (including in Azure SQL Server) we need to set up not only a BC Service account to run the application service layer, but we also need to provide the BC instance sql credentials to be able to access the database data.
That is done in BC Administration tool, in the Database Credentials blade for the service instance.

Check Learn:

New-NAVEncryptionKey (Microsoft.Dynamics.Nav.Management) – Dynamics NAV | Microsoft Learn

“When using SQL Server authentication between the Business Central Server instance and database in SQL Server, Business Central encrypts passwords that are used by a server instance to access to Business Central databases in SQL Server. This includes, for example, the server instance service account credentials and the database credentials.

To encrypt and decrypt the passwords, an encryption key is used. Business Central uses a single encryption key per server instance. Encryption and decryption is performed by a RSA algorithm as provided by the cryptographic service provider (see RSACryptoServiceProvider(Int32)). The generated encryption key size is 2048 bits.”

Check Learn:

Import-NAVEncryptionKey (Microsoft.Dynamics.Nav.Management) – Dynamics NAV | Microsoft Learn

The encryption key is unique to the hardware and the instance and is kept in this folder:

C:\ProgramData\Microsoft\Microsoft Dynamics NAV\200\Server\Keys

Replace “200” with your version of Business Central.

After restoring the database from another database or when moving database backups between environments, the encryption within database does not correspond with the encryption key stored on the application server in the folder mentioned above.

That is when we need to regenerate the encryption key for this server instance.

What do we need to do?

1. Create a new encryption key
2. Import the new key to the application server

Here is a sample script inspired from Tobias Fenster blog post in 2017 (How to connect NAV on Docker to an existing SQL database (quick and dirty) - Axians Infoma (axians-infoma.com)):

Import-Module "C:\Program Files\Microsoft Dynamics 365 Business Central\200\Service\NavAdminTool.ps1"

Write-Host "Creating SQL Server Encryption Key"
$ServiceTierFolder = 'C:\Keys\'
$SQLEncryptKey =  Join-Path $ServiceTierFolder 'MyInstance.key'

$Password = 'some password to protect the key'
New-NAVEncryptionKey -KeyPath $SQLEncryptKey -Password (ConvertTo-SecureString -AsPlainText -Force $Password) -Force

$TrustSQLServerCertificate = $true
$NewBcServerInstance = 'MyInstance'
$DatabaseServer = 'mysqldb.database.windows.net'
$ApplicationDatabase = 'MyDB'

                       
[SecureString]$pwd = ConvertTo-SecureString 'sa_password' -AsPlainText -Force
$dbcred = New-Object System.Management.Automation.PSCredential ('sa', $pwd)

Write-Host "Importing Encryption Key"
Import-NAVEncryptionKey 
      -ServerInstance $NewBcServerInstance 
      -ApplicationDatabaseServer $DatabaseServer 
      -ApplicationDatabaseCredentials $dbcred 
      -ApplicationDatabaseName $ApplicationDatabase 
      -KeyPath $SQLEncryptKey -Password (ConvertTo-SecureString -AsPlainText -Force $Password) 
      -Force 
      -Verbose

Set-NAVServerConfiguration -ServerInstance $NewBcServerInstance -KeyName "EnableSqlConnectionEncryption" -KeyValue "true" -WarningAction SilentlyContinue

Set-NAVServerConfiguration -ServerInstance $NewBcServerInstance -KeyName "TrustSQLServerCertificate" -KeyValue $TrustSQLServerCertificate.Tostring().ToLowerInvariant() -WarningAction SilentlyContinue

Set-NAVServerConfiguration -ServerInstance $NewBcServerInstance -DatabaseCredentials $dbcred -Force

Restart-NAVServerInstance -ServerInstance $NewBcServerInstance

Sometimes Import-NAVEncryptionKey will fail with various errors:

• • Import-NAVEncryptionKey : Unable to decrypt data. The data was encrypted using a different key.

• • Cannot establish a connection to the SQL Server/Database

• • What we need to do in this case is to remove the existing key dependent of the hardware and the instance from the location above (C:\ProgramData\Microsoft\Microsoft Dynamics NAV\200\Server\Keys).

• • Even after removing the existing encryption key I still encountered errors at importing the new key. In this case try switching temporarily to Windows Authentication for Database Credentials save, and switch back to SQL Server Authentication and save. Then re-create the key and attempt re-importing it and re-start the service.

Summary

This article explains the process of setting up database credentials in Business Central Administration tool when the NAV/BC database is outside of the domain network. It outlines the steps to create a new encryption key and import it to the application server. The article also provides a sample script for creating the encryption key and importing it. Additionally, it offers troubleshooting tips for common errors encountered during the import process.