Connecting NAV Container and SQL Container

[krregg]

I am trying to connect NAV Container with another container that is holding SQL Server with a restored database which I would like to use with Business Central from the container.

I am following this post: https://www.axians-infoma.de/techblog/how-to-connect-nav-on-docker-to-an-existing-sql-database-quick-and-dirty/

To create a container with SQL server I use this command: docker run -d -it --storage-opt size=250G -p 1433:1433 --name sql -e sa_password=strongpass -e ACCEPT_EULA=Y -v C:/temp/db:C:/data/ microsoft/mssql-server-windows-developer

Restoring the database work well also, but there is a problem when I run command for nav container: docker run -e accept_eula=Y -e ACCEPT_OUTDATED=Y -e DatabaseServer=sql -e DatabaseName="mydbname" -m=6G -e licensefile=c:\run\my\licence.flf --name nav --hostname nav -v c:\temp\my:c:\run\my mcr.microsoft.com/businesscentral/onprem:15.1.37881.38071

The process stop at "Import Encryption Key": Cannot process command because of one or more missing mandatory parameters: ApplicationDatabaseServer ApplicationDatabaseName KeyPath.

ApplicationDatabaseServer is, ApplicationDatabaseName, KeyPath are all working well, all have the right value, I even tried with the IP address of the container but nothing.

ApplicationDatabaseServer is IP in my case (I hardcoded it) ApplicationDatabaseName is the name of the restored database. KeyPath is valid path to the key file.

I hope someone can help me with this because I tried so much and I don't have any ideas anymore.

[freddydk]

I cannot remember what parameters you need, but this blog post explains how to do things with navcontainerhelper: https://freddysblog.com/2019/11/04/using-sql-server-on-the-host/ Maybe you can find the info in there?

[krregg]

Thank you for the link @freddydk . I have to admit that I was reading your blog posts and somehow I missed this one. Anyway, I managed to create and connect containers in three steps:

1. Create SQL Server Container

docker run -d -it --storage-opt size=250G -p 1433:1433 --name sqlContainer -e sa_password="pass" -e ACCEPT_EULA=Y -v C:/temp/BC150/app/backup:C:/data/ microsoft/mssql-server-windows-developer

2. Restore the Database

For restoring the database, I used MSSMS and it worked well. This is enough for me to proceed to another step. The only thing here is that I have a little problem with restoring the database with PowerShell. Its the first time for me to work with MDF and LDF files so for this case I opened another issue, I hope someone will be able to help me.

3. Create BcContainer

New-BCContainer `
    -accept_eula `
    -containerName $containerName `
    -imageName $imageversion `
    -updateHosts `
    -auth UserPassword `
    -Credential $credential `
    -databaseServer 'sqlContainer' `
    -databaseInstance '' `
    -licenseFile $licenseFile `
    -databaseName $DatabaseName `
    -databaseCredential $dbcredentials

New-NavContainerNavUser -containerName $containerName -Credential $credential -ChangePasswordAtNextLogOn:$false -PermissionSetId SUPER

[krregg]

When I try to restore the database in a container with image microsoft/mssql-server-windows-developer I get the error below. @freddydk Is there any option to get a newer version of the SQL server with this image?

Restore-SqlDatabase : System.Data.SqlClient.SqlError: The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 14.00.3015. Either restore the database on a server that 
supports the backup, or use a backup that is compatible with this server.
At line:1 char:1
+ Restore-SqlDatabase -ServerInstance 172.24.10.100 -Database "l-datab...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

I looked around and I found SQL Server 2019 only on the Linux platform. I tried to create container on the Linux platform with this command:

docker run --name sql -e ACCEPT_EULA=Y -e "SA_PASSWORD=1Secure*Password1" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest -e attach_dbs="[{"dbName":"dbname","dbFiles":["C:\temp\BC150\dbname_Data.mdf","C:\temp\BC150\dbname_Log.ldf"]}]"

It returns me a failed container. I tried to use \\ instead of \. I am sure that backup is working well it should cause any problem. But I am a bit confused now. Should I try to do it with SQL Server on Linux containers or is it better to use some real SQL (Development) Server for my case (the host as you described in one of your posts).

[Koubek]

@krregg I think you should be able to attach them on a container running current microsoft/mssql-server-windows-developer image as well. Maybe the restore doesn't work, but attaching them might work properly.

We run prioritized tasks that try to run restores on several sources with fallback to db attach. And we use usually this image too (we also run some restores on Azure SQL but this is another story) and no problems so far. Anyway, I am now building my own sql images based on the existing dockerfile, slightly modified. Reason - Windows Server 2019 support. So I believe you might create your images too and do practically anything you need.

[krregg]

@Koubek thank you for the information. I never worked with dockerfile before and I found this I guess its that, right? But I have a question, how to manage that I get for example SQL Server 2019 exe and box install files like in case on the link? Is this only modification I need to do if I would want to use SQL Server 2019 instead of 2016? How did you manage to do it? I would be really happy if you can help me with that a little bit :)

[freddydk]

Alternatively, you can install SQL on the host and use that. Gives a better performance as well.

[krregg]

I am not sure what am I doing wrong when running this:

$DatabaseName = "BC-mydatabase-D0"
$imageName = "mcr.microsoft.com/businesscentral/onprem:15.1.37881.38071"
$containerName = "testapp"
$credential = New-Object pscredential 'sa', (ConvertTo-SecureString -String 'fda3fa34fgreg3' -AsPlainText -Force)

New-BCContainer `
    -accept_eula `
    -containerName $containerName `
    -imageName $imageName `
    -updateHosts `
    -auth UserPassword `
    -Credential $credential `
    -databaseServer 'MY-PC-NAME\sqldocker' `
    -databaseInstance '' `
    -licenseFile "C:\temp\6866676-20200608-app365-Dev.flf" `
    -databaseName $DatabaseName `
    -databaseCredential $credential

It stuck at 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 132
at <ScriptBlock>, C:\Run\navstart.ps1: line 113
at <ScriptBlock>, C:\Run\start.ps1: line 121
at <ScriptBlock>, <No file>: line 1
Initialization of container testapp failed
At C:\Program Files\WindowsPowerShell\Modules\navcontainerhelper\0.7.0.2\ContainerHandling\Wait-NavContainerReady.ps1:44 char:17
+ ...             throw "Initialization of container $containerName failed" ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (Initialization ...inkPanda failed:String) [], RuntimeException
    + FullyQualifiedErrorId : Initialization of container testapp failed

The credentials are fine, I followed your blog post. I assume the parameter -databaseServer is correct. I tried also localhost\sqldocker. That's how I usually call SQL server on my computer so I guess it should work here also. There is no error in Event Viewer, but I bet it's this * Proper permissions have not been given to the NAV Server Account. What should I do to set proper permissions?

For example if I run the code from above, but connecting to another container running SQL Server Developer instead, the connection works fine and BC container works as expected. The command I used:

New-BCContainer `
    -accept_eula `
    -containerName $containerName `
    -imageName $imageName `
    -updateHosts `
    -auth UserPassword `
    -Credential $credential `
    -databaseServer 'sql' `
    -databaseInstance '' `
    -licenseFile "C:\temp\6866676-20200608-app365-Dev.flf" `
    -databaseName $DatabaseName `
    -databaseCredential $credential

sql is just the name of the SQL container which is also mentioned a few comments above.

I am sorry if my questions are stupid or to basic but I would appreciate any help with that.

[freddydk]

The container cannot see he host by name. You can see how to use SQL on the host in this blog post: https://freddysblog.com/2019/11/04/using-sql-server-on-the-host/ Your database server should be -databaseServer 'host.containerhelper.internal' and I guess the instance is sqldocker? (not sure how you set it up)

[krregg]

I also tried with this first when I read it:

    -databaseServer 'host.containerhelper.internal' `
    -databaseInstance 'sqldocker' `

Yes, sqldocker is SQL instance.

This gives me the same error as before. I was checking the hosts file, it also looks fine.

[freddydk]

And sql on the host is setup for windows and username/password authentication? I am doing this every single day - the blog post should explain all pitfalls.

If you in a command prompt write docker exec -it powershell

then you start a powershell prompt inside the faulty container. The you can ping host.containerhelper.internal and use invoke-sqlcmd to see if you can connect.

[krregg]

Yes, log in for SQL on the host is set for both types of authentication.

I could enter in container's Powershell and I could ping ping host.containerhelper.internal.

Then I tried to connect to SQL but the connection failed. This the command I used:

Invoke-Sqlcmd -ServerInstance "host.containerhelper.internal" -Database BC-mydatabase-D0 -Username sa -Password "fda3fa34fgreg3" -Query "SELECT GETDATE() AS TimeOfQuery"

I am not sure what to use for -ServerInstance, I think I am doing it wrong. I tried different values as sqldocker, MY-PC-NAME\sqldocker, host.containerhelper.internal\sqldocker, localhost. event IPs. Every time is the same error:

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I tried the same command to connect on SQL from the host, but it works as expected (I tested credentials only that I was sure it should work also from the container). SQL is set to accept remote connections, inbound ports are set as well.

[freddydk]

Did you do all of this:

1. Install SQL Server 2017 Developer edition on my laptop (my docker host)
2. Install SSMS (SQL Server Management Studio) on my laptop
3. Open SQL Server 2017 Configuration Manager and enable TCP/IP protocol for SQL Server.
4. Open SSMS, connect to localhost SQL Server, select Properties on localhost, Security and enable SQL Server and Windows Authentication mode.
5. In the Object Explorer in SSMS, expand Security and Logins. Select properties on sa and set a password (under General) and enable sa (under Status).
6. Open port 1433 for inbound traffic in the Firewall.

[krregg]

I did, the only thing I did different is that I used SQL Server 2019 Developer edition. My databases are all version 15.