search

microsoft / navcontainerhelper

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

Cannot connect to SQL Server in database on host setting #2769

Closed jacquelinebuettner closed 1 year ago

jacquelinebuettner commented 1 year ago

Cannot connect to SQL Server We want to start an instance of Business Central (onprem) with a database hosted locally. We are running a SQL Server 2019 with a database restored from a backup file. The database server has TCP/IP activated and is configured for mixed mode authentication. I can connect to the database directly and via a command like: Invoke-SQLcmd -ServerInstance ".\MSSQLSERVER01" -Database "mydb" -Query "Select * from [Company]" -Username "someuser" -Password "aPasswd"

However, we get "Cannot connect to SQL Server". We are setting this up for the first time. We would appreciate any hint what we might be missing.

Scripts used to create container and cause the issue

$containerName = 'newbcdev'
$password = ''
$securePassword = ConvertTo-SecureString -String $password -AsPlainText -Force
$credential = New-Object pscredential 'admin', $securePassword
$auth = 'UserPassword'
$artifactUrl = Get-BcArtifactUrl -type 'OnPrem' -version '17.8.27267.0' -country 'de' -select 'Closest'
$databaseServer = 'host.containerhelper.internal'
$databaseInstance = '.\MSSQLSERVER01'
$databaseName = 'mydb'
$databaseUsername = 'ssomeusera'
$databasePassword = 'aPasswd'
$databaseSecurePassword = ConvertTo-SecureString -String $databasePassword -AsPlainText -Force
$databaseCredential = New-Object pscredential $databaseUsername, $databaseSecurePassword
New-BcContainer `
    -accept_eula `
    -containerName $containerName `
    -credential $credential `
    -auth $auth `
    -artifactUrl $artifactUrl `
    -imageName 'yetanewimage' `
    -databaseServer $databaseServer -databaseInstance $databaseInstance -databaseName $databaseName `
    -databaseCredential $databaseCredential `
    -memoryLimit 8G `
    -updateHosts

Full output of scripts

Initializing...

Setting host.docker.internal to 10.50.120.106 in container hosts file (copy from host hosts file)

Setting gateway.docker.internal to 10.50.120.106 in container hosts file (copy from host hosts file)

Setting kubernetes.docker.internal to 127.0.0.1 in container hosts file (copy from host hosts file)

Setting host.containerhelper.internal to 172.27.80.1 in container hosts file

Starting Container

Hostname is newbcdev

PublicDnsName is newbcdev

Using NavUserPassword Authentication

Starting Internet Information Server

Import Encryption Key

Es kann keine Verbindung mit SQL Server/der SQL-Datenbank hergestellt werden.

Dies kann unter anderem folgende Gründe haben:

* SQL Server wurde nicht gestartet.

* Die Datenbank ist nicht vorhanden.

* Dem NAV-Serverkonto wurden keine ordnungsgemäßen Berechtigungen gewährt.

Versuchen Sie es später erneut, oder wenden Sie sich an den Systemadministrator.

at <ScriptBlock>, C:\Run\SetupDatabase.ps1: line 152

at <ScriptBlock>, C:\Run\navstart.ps1: line 138

at <ScriptBlock>, C:\Run\start.ps1: line 384

at <ScriptBlock>, <No file>: line 1

Initializing...

Cannot find path 'C:\run\my\aes.key' because it does not exist.

at <ScriptBlock>, C:\Run\SetupVariables.ps1: line 45

at <ScriptBlock>, C:\Run\my\SetupVariables.ps1: line 2

at <ScriptBlock>, C:\Run\navstart.ps1: line 25

at <ScriptBlock>, C:\Run\start.ps1: line 384

at <ScriptBlock>, <No file>: line 1

Initializing...

Cannot find path 'C:\run\my\aes.key' because it does not exist.

at <ScriptBlock>, C:\Run\SetupVariables.ps1: line 45

at <ScriptBlock>, C:\Run\my\SetupVariables.ps1: line 2

at <ScriptBlock>, C:\Run\navstart.ps1: line 25

at <ScriptBlock>, C:\Run\start.ps1: line 384

at <ScriptBlock>, <No file>: line 1

Initializing...

Cannot find path 'C:\run\my\aes.key' because it does not exist.

at <ScriptBlock>, C:\Run\SetupVariables.ps1: line 45

at <ScriptBlock>, C:\Run\my\SetupVariables.ps1: line 2

at <ScriptBlock>, C:\Run\navstart.ps1: line 25

at <ScriptBlock>, C:\Run\start.ps1: line 384

at <ScriptBlock>, <No file>: line 1

Initializing...

Cannot find path 'C:\run\my\aes.key' because it does not exist.

at <ScriptBlock>, C:\Run\SetupVariables.ps1: line 45

at <ScriptBlock>, C:\Run\my\SetupVariables.ps1: line 2

at <ScriptBlock>, C:\Run\navstart.ps1: line 25

at <ScriptBlock>, C:\Run\start.ps1: line 384

at <ScriptBlock>, <No file>: line 1

Initializing...

Cannot find path 'C:\run\my\aes.key' because it does not exist.

at <ScriptBlock>, C:\Run\SetupVariables.ps1: line 45

at <ScriptBlock>, C:\Run\my\SetupVariables.ps1: line 2

at <ScriptBlock>, C:\Run\navstart.ps1: line 25

at <ScriptBlock>, C:\Run\start.ps1: line 384

at <ScriptBlock>, <No file>: line 1

Initializing...

Cannot find path 'C:\run\my\aes.key' because it does not exist.

at <ScriptBlock>, C:\Run\SetupVariables.ps1: line 45

at <ScriptBlock>, C:\Run\my\SetupVariables.ps1: line 2

at <ScriptBlock>, C:\Run\navstart.ps1: line 25

at <ScriptBlock>, C:\Run\start.ps1: line 384

at <ScriptBlock>, <No file>: line 1

Initializing...

Cannot find path 'C:\run\my\aes.key' because it does not exist.

at <ScriptBlock>, C:\Run\SetupVariables.ps1: line 45

at <ScriptBlock>, C:\Run\my\SetupVariables.ps1: line 2

at <ScriptBlock>, C:\Run\navstart.ps1: line 25

at <ScriptBlock>, C:\Run\start.ps1: line 384

at <ScriptBlock>, <No file>: line 1
...
jacquelinebuettner commented 1 year ago

We've again checked for the firewall and disabled everything without any difference. We also changed $databaseServer = 'host.containerhelper.internal' to $databaseServer = '.' (and 'localhost'). However, the error message remains the same.

freddydk commented 1 year ago

Try $databaseServer = 'host.containerhelper.internal' $databaseInstance = 'MSSQLSERVER01'

jacquelinebuettner commented 1 year ago

Thanks for the hint. Unfortunately this doesn't change the error message.

jacquelinebuettner commented 1 year ago

Ok, so I started from scratch with different image and container names. I changed the databse user, deactivated the firewall again and lastly tried to ping "host.containerhelper.internal". That name however cannot be found. From the script it reads: Setting host.containerhelper.internal to 172.21.32.1 in container hosts file I can ping host.docker.internal, gateway.docker.internal and kubernetes.docker.internal and I find all of these in my hosts file. There ist however no entry host.containerhelper.internal. Should there be one?

For testing purposes I simply added the suspect line to the hosts file.
The Output now changed to at , C:\Run\SetupDatabase.ps1: line 152

at , C:\Run\navstart.ps1: line 138

at , C:\Run\start.ps1: line 384

at , : line 1

Adding BUSINESSCENTRAL to hosts file

Initializing...

Cannot find path 'C:\run\my\aes.key' because it does not exist.

at , C:\Run\SetupVariables.ps1: line 45

at , C:\Run\my\SetupVariables.ps1: line 2

freddydk commented 1 year ago

as a long shot - could you try to add -isolation hyperv?

CPSTOS commented 1 year ago

Hi Freddy, I'm having the same issue. Already tried a lot of things I found, like disabling the Firewall and Windows Defender, activated TCP/IP, started the previously deactivated SQL Browser and finally -isolation hyperv. Unfortunatel I'm still getting the error. Here is my script and the output:

`$accept_eula = $true $containername = 'bc-dev' $licenseFile = '' $auth = 'UserPassword' $usessl = $false $updateHosts = $true $assignPremiumPlan = $false $shortcuts = 'Desktop' $password = ConvertTo-SecureString "X" -AsPlainText -Force $dbPassword = ConvertTo-SecureString "X" -AsPlainText -Force $integrationUserCreds = New-Object System.Management.Automation.PSCredential ("X", $password) $databaseCreds = New-Object System.Management.Automation.PSCredential ("sa", $dbPassword)

$artifactUrl = Get-BcArtifactUrl -country 'DE' -select 'Latest'

New-BcContainer -accept_eula:$accept_eula -replaceExternalDatabases -containername $containername -artifactUrl $artifactUrl -auth $auth -Credential $integrationUserCreds -usessl:$usessl -updateHosts:$updateHosts -licenseFile $licenseFile -assignPremiumPlan:$assignPremiumPlan -isolation Process -shortcuts $shortcuts -databaseServer 'host.containerhelper.internal' -databaseInstance "SQLExpress" -databasePrefix "$containerName-" -databaseName "DB" -databaseCredential $databaseCreds -multitenant: $true

`Using artifactUrl https://bcartifacts.azureedge.net/sandbox/21.2.49946.51081/de Using installer from C:\Run\210-new Installing Business Central Installing from artifacts Starting Local SQL Server Starting Internet Information Server Copying Service Tier Files c:\dl\sandbox\21.2.49946.51081\platform\ServiceTier\Program Files c:\dl\sandbox\21.2.49946.51081\platform\ServiceTier\System64Folder Copying PowerShell Scripts c:\dl\sandbox\21.2.49946.51081\platform\WindowsPowerShellScripts\Cloud\NAVAdministration c:\dl\sandbox\21.2.49946.51081\platform\WindowsPowerShellScripts\WebSearch Copying Web Client Files c:\dl\sandbox\21.2.49946.51081\platform\WebClient\Microsoft Dynamics NAV Copying ModernDev Files c:\dl\sandbox\21.2.49946.51081\platform c:\dl\sandbox\21.2.49946.51081\platform\ModernDev\program files\Microsoft Dynamics NAV Copying additional files Copying ConfigurationPackages C:\dl\sandbox\21.2.49946.51081\de\ConfigurationPackages Copying Test Assemblies C:\dl\sandbox\21.2.49946.51081\platform\Test Assemblies Copying Extensions C:\dl\sandbox\21.2.49946.51081\de\Extensions Copying Applications C:\dl\sandbox\21.2.49946.51081\platform\Applications Copying Applications.DE C:\dl\sandbox\21.2.49946.51081\de\Applications.DE 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' Installation took 27 seconds Installation complete Initializing... Setting host.docker.internal to 192.168.0.101 in container hosts file (copy from host hosts file) Setting gateway.docker.internal to 192.168.0.101 in container hosts file (copy from host hosts file) Setting kubernetes.docker.internal to 127.0.0.1 in container hosts file (copy from host hosts file) Setting host.containerhelper.internal to 172.21.112.1 in container hosts file Starting Container Hostname is bc-dev PublicDnsName is bc-dev Using NavUserPassword Authentication Waiting for database creation to finish Success Import Encryption Key Es kann keine Verbindung mit SQL Server/der SQL-Datenbank hergestellt werden.

Dies kann unter anderem folgende Gr├╝nde haben:

Versuchen Sie es später erneut, oder wenden Sie sich an den Systemadministrator. at , C:\run\setupDatabase.ps1: line 152 at , C:\Run\my\SetupDatabase.ps1: line 2 at , C:\Run\navstart.ps1: line 138 at , C:\Run\start.ps1: line 384 at , : line 1 New-BcContainer Telemetry Correlation Id: 78efb9ee-8666-4f7f-b89d-c667f6bd3dea Initialization of container bc-dev failed In C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\4.0.7\ContainerHandling\Wait-NavContainerReady.ps1:42 Zeichen:17

jacquelinebuettner commented 1 year ago

We came to the conclusion that our network / firewall setup is the cause of the problems. It seems that the database must be avaialable openly from the internet (intranet is not sufficient). Which we really do not want, if we host the database server on the developers machine. Therefore we set up a server with less restrictions and now start the containers from there. Not what we yielded at, but no other way worked.

CPSTOS commented 1 year ago

I got it working now. The missing piece was that SQL Server Express 2019 was not listening on port 1433. After setting that port for IPAll in the SQL Server Configuration Manager it did work!