SQLSERVER connection error when creating container

[Alexis-MS]

Hello everyone

I am having a problem trying to create a container with the New-bccontainer helper on my local server with a secondary database instance called MSSQLSERVER2019.

In my script when I leave the $databaseinstance="" parameter empty it creates the container without problems in the main instance called MSSQLSERVER.

But when I leave the parameter $databaseinstance="MSSQLSERVER2019" with the databaseinstance where I want to create the database I get this error:

"Cannot establish a connection to the SQL Server/Database.

This could be due to one of the following reasons:

• SQL Server is not started.
• The database does not exist.
• Proper permissions have not been given to the NAV Server Account."

The container is created and creates a SQLEXPRES inside, also the database is created but it does not connect to the databaseinstance.

From the container if I have access to the ip of the local sql and access the port.

I have checked that the password is correct

Scripts used:

$containerName = 'TestSQL19'
$hostname = $containerName
$name = $containerName
$licensefile = 'C:\license\container.bclicense'
$auth = "UserPassword"
$username = 'alexisadmon'
$password = ConvertTo-SecureString 'mysecurePass' -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($username, $password)
$databaseusername = 'sa'
$databasepassword = ConvertTo-SecureString 'mysecurePass' -AsPlainText -Force
$databasecredential = New-Object System.Management.Automation.PSCredential ($databaseusername, $databasepassword)

$artifactUrl = Get-BCArtifactUrl -country es -type OnPrem -version 22 -select Latest
get-variable -name artifactUrl -valueonly

$databaseParams = @{
"databaseServer" = 'host.containerhelper.internal'
"databaseInstance" = 'MSSQLSERVER19'
"databasePrefix" = "$containerName"
"databaseName" = '_'
"databaseCredential" = $databasecredential
"multitenant" = $false
}

#VARIABLES TRAEFIK********************************************************************************
$externaldns = 'myexternal.dns.example'
$restPart = "/${name}rest/"
$soapPart = "/${name}soap/"
$devPart = "/${name}dev/"
$dlPart = "/${name}dl/"
$webclientPart = "/$name/"
$baseUrl = "https://$externaldns"
$restUrl = $baseUrl + $restPart
$soapUrl = $baseUrl + $soapPart
$webclientUrl = $baseUrl + $webclientPart

$customNavSettings = "customnavsettings=PublicODataBaseUrl=$restUrl,PublicSOAPBaseUrl=$soapUrl,PublicWebBaseUrl=$webclientUrl"
$webclientRule="PathPrefix:$webclientPart"
$soapRule="PathPrefix:${soapPart};ReplacePathRegex: ^${soapPart}(.) /BC/WS/$1" $restRule="PathPrefix:${restPart};ReplacePathRegex: ^${restPart}(.*) /BC/OData/$1"
$devRule="PathPrefix:${devPart};ReplacePathRegex: ^${devPart}(.) /BC/`$1"
$dlRule="PathPrefixStrip:${dlPart}"

$additionalParameters = @("--hostname $hostname",
"-e webserverinstance=$name",
"-e publicdnsname=$externaldns",
"-e $customNavSettings",
"-l "traefik.web.frontend.rule=$webclientRule"",
"-l "traefik.web.port=80"",
"-l "traefik.soap.frontend.rule=$soapRule"",
"-l "traefik.soap.port=7047"",
"-l "traefik.rest.frontend.rule=$restRule"",
"-l "traefik.rest.port=7048"",
"-l "traefik.dev.frontend.rule=$devRule"",
"-l "traefik.dev.port=7049"",
"-l "traefik.dl.frontend.rule=$dlRule"",
"-l "traefik.dl.port=8080"",
"-l "traefik.enable=true"",
"-l "traefik.frontend.entryPoints=https""
)

#VARIABLES TRAEFIK********************************************************************************

New-BcContainer @databaseParams -replaceExternalDatabases -accept_eula
-accept_outdated -containerName $containerName
-artifactUrl $artifactUrl -auth $auth
-Credential $credential -licenseFile $licenseFile
-updateHosts -includeAL
-imageName 'myimage' -assignPremiumPlan
-EnableTaskScheduler:$false -memoryLimit 6G
-additionalParameters $additionalParameters `
-myScripts @("c:\traefikforbc\my\CheckHealth.ps1")

Full output of scripts

    https://bcartifacts-exdbf9fwegejdqak.b02.azurefd.net/onprem/22.12.64167.0/es
BcContainerHelper is version 6.0.15
BcContainerHelper is running as administrator
HyperV is Disabled
UsePsSession is True
UsePwshForBc24 is True
Host is Microsoft Windows Server 2019 Standard - 10.0.17763.5458
Docker Client Version is 20.10.7
Docker Server Version is 20.10.7
Removing entries from hosts
Removing testSql19 from container hosts file
Removing testSql19-* from container hosts file
Removing Desktop shortcuts
Removing C:\ProgramData\BcContainerHelper\Extensions\testSql19
Starting Database Restore job from https://bcartifacts-exdbf9fwegejdqak.b02.azurefd.net/onprem/22.12.64167.0/es
C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.15\Import-BcContainerHelper.ps1
Fetching all docker images
Fetching all docker volumes
ArtifactUrl and ImageName specified
Image myimage:onprem-22.12.64167.0-es-nodb already exists
Using image myimage:onprem-22.12.64167.0-es-nodb
Creating Container testSql19
Style: onprem
Multitenant: No
Version: 22.12.64167.0
Platform: 22.0.64134.0
Generic Tag: 1.0.2.18
Container OS Version: 10.0.17763.5576 (ltsc2019)
Host OS Version: 10.0.17763.5458 (ltsc2019)
WARNING: Host OS and Base Image Container OS doesn't match and HyperV is not installed. If you encounter issues, you could try to install HyperV.
Using process isolation
Using locale es-ES
Disabling the standard eventlog dump to container log every 2 seconds (use -dumpEventLog to enable)
Using license file C:\license\container.bclicense
Additional Parameters:
--hostname testSql19
-e webserverinstance=testSql19
-e publicdnsname=myexternal.dns.example
-e customnavsettings=PublicODataBaseUrl=https://myexternal.dns.example/testSql19rest/,PublicSOAPBaseUrl=https://myexternal.dns.example/testSql19soap/,PublicWebBaseUrl=https://myexternal.dns.example/testSql19/,EnableTaskScheduler=False
-l "traefik.web.frontend.rule=PathPrefix:/testSql19/"
-l "traefik.web.port=80"
-l "traefik.soap.frontend.rule=PathPrefix:/testSql19soap/;ReplacePathRegex: ^/testSql19soap/(.*) /BC/WS/$1"
-l "traefik.soap.port=7047"
-l "traefik.rest.frontend.rule=PathPrefix:/testSql19rest/;ReplacePathRegex: ^/testSql19rest/(.*) /BC/OData/$1"
-l "traefik.rest.port=7048"
-l "traefik.dev.frontend.rule=PathPrefix:/testSql19dev/;ReplacePathRegex: ^/testSql19dev/(.*) /BC/$1"
-l "traefik.dev.port=7049"
-l "traefik.dl.frontend.rule=PathPrefixStrip:/testSql19dl/"
-l "traefik.dl.port=8080"
-l "traefik.enable=true"
-l "traefik.frontend.entryPoints=https"
Files in C:\ProgramData\BcContainerHelper\Extensions\testSql19\my:
- AdditionalOutput.ps1
- CheckHealth.ps1
- HelperFunctions.ps1
- license.bclicense
- MainLoop.ps1
- SetupDatabase.ps1
- SetupVariables.ps1
- updatehosts.ps1
Creating container testSql19 from image myimage:onprem-22.12.64167.0-es-nodb
Using Shared Encryption Key file
008987780d75689b8692537225f2b114cd2615f8bd475a63c35d240596bb491b
Waiting for container testSql19 to be ready
Initializing...
Setting host.containerhelper.internal to 172.19.144.1 in container hosts file
Starting Container
Hostname is testSql19
PublicDnsName is myexternal.dns.example
Using NavUserPassword Authentication
Starting Internet Information Server
Success
Import Encryption Key
Cannot establish a connection to the SQL Server/Database.

This could be due to one of the following reasons:

* SQL Server is not started.
* The database does not exist.
* Proper permissions have not been given to the NAV Server Account.

Try again later or contact your system administrator.
at <ScriptBlock>, C:\run\setupDatabase.ps1: line 152
at <ScriptBlock>, C:\Run\my\SetupDatabase.ps1: line 2
at <ScriptBlock>, C:\Run\navstart.ps1: line 141
at <ScriptBlock>, C:\Run\start.ps1: line 390
at <ScriptBlock>, <No file>: line 1Error
Initializing...
Setting host.containerhelper.internal to 172.19.144.1 in container hosts file
Starting Container
Hostname is testSql19
PublicDnsName is myexternal.dns.example
Using NavUserPassword Authentication
Starting Internet Information Server
Success
Import Encryption Key
Cannot establish a connection to the SQL Server/Database.

This could be due to one of the following reasons:

* SQL Server is not started.
* The database does not exist.
* Proper permissions have not been given to the NAV Server Account.

Try again later or contact your system administrator.
at <ScriptBlock>, C:\run\setupDatabase.ps1: line 152
at <ScriptBlock>, C:\Run\my\SetupDatabase.ps1: line 2
at <ScriptBlock>, C:\Run\navstart.ps1: line 141
at <ScriptBlock>, C:\Run\start.ps1: line 390
at <ScriptBlock>, <No file>: line 1
New-BcContainer Telemetry Correlation Id: 39dff4d3-24ad-49d7-977a-10cbd7d64019
Initialization of container testSql19 failed
At C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.15\ContainerHandling\Wait-NavContainerReady.ps1:42 char:17
+ ...             throw "Initialization of container $containerName failed" ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Initialization ...estSql19 failed:String) [], RuntimeException
    + FullyQualifiedErrorId : Initialization of container testSql19 failed

BcContainerHelper is version 6.0.15
NavContainerHelper is running as administrator
Host is Microsoft Windows Server 2019 Version 1809 (OS Build 17763.5458)

• does it happen all the time? Yes, whenever databaseinstance is "MSSQLSERVER2019".
• did it use to work? It has never worked with the database instance "MSSQLSERVER2019".
• works in the default databaseinstance "MSSQLSERVER"

Can someone help me?

[RaKuNbg]

I'm seeing similar problem that all my existing containers that have been running since months cannot connect to my local SQLServer anymore.

When I try to setup the container new, I get the same error as mentioned above also I always use $databaseinstance=""

[RaKuNbg]

Hi @freddydk ,
this becomes a severe issue in our company because it's showing up on several of our development machines starting this Tuesday.
In addition to not being able to create a new container, all my existing containers are unhealthy since Tuesday.
Do you have any idea, what could be the reason?

[freddydk]

If your existing containers started failing out of the blue, I would look at Windows Updates or updates to SQL Server.
@Alexis-MS states that one instance is working - another is not - that also sounds like a configuration problem somehow and not something I can troubleshoot from here.

[RaKuNbg]

These updates were installed on Tuesday

but I guess the problems startet already in the morning when the updates were only downloaded but not installed
As I see there was a security update for the SQL-Server included - could that cause the problems?

[RaKuNbg]

(almost) all-clear signal...
We have to blame our IT department:
...
To secure company notebooks, a policy was rolled out this week that restricts incoming connections within public networks via the Windows Firewall. Public networks are usually networks of hotels, cafés and other public facilities that provide a guest WLAN. The local Docker containers on the notebooks also configured their virtual network as a "public" network. The result was, that the incoming rules of the Windows firewall regarding the Docker containers were allowed for traffic, but still blocked due to the fact that it was a public network. These settings were rolled back.
...
I'm now able to create a new docker again.
@Alexis-MS: I hope, this information will help you, too

But still my existing dockers are unhealthy ==> I'll try to restart the docker / docker engine / notebook again and see if that helps.

[Alexis-MS]

In my case it has nothing to do with what you are talking about. As soon as we manage to solve it, I will let you know here.
Thanks to both of you.