Closed tscottjendev closed 1 year ago
tscottjendev
commented
1 year ago I have added 'dbserver.containerhelper.internal' to the host's "hosts" file and given it the IP of the SQL server, still the same error.
How can I tell what the problem is.
tscottjendev
commented
1 year ago This is the new output when I have added dbserver.containerhelper.internal
BcContainerHelper is version 4.0.6 BcContainerHelper is running as administrator Hyper-V is Disabled UsePsSession is True Host is Microsoft Windows Server 2022 Standard - ltsc2022 Docker Client Version is 20.10.9 Docker Server Version is 20.10.9 Removing Session bcsqlremote Removing container bcsqlremote Removing entries from hosts Removing bcsqlremote from container hosts file Removing bcsqlremote-* from container hosts file Removing Desktop shortcuts Removing C:\ProgramData\BcContainerHelper\Extensions\bcsqlremote Fetching all docker images Fetching all docker volumes Using image mcr.microsoft.com/businesscentral:10.0.20348.1249 Creating Container bcsqlremote Style: onprem Multitenant: No Version: 21.1.48363.48638 Platform: 21.0.48357.48504 Generic Tag: 1.0.2.13 Container OS Version: 10.0.20348.1249 (ltsc2022) Host OS Version: 10.0.20348.1249 (ltsc2022) Using process isolation Using locale en-GB Disabling the standard eventlog dump to container log every 2 seconds (use -dumpEventLog to enable) Files in C:\ProgramData\BcContainerHelper\Extensions\bcsqlremote\my:
- AdditionalOutput.ps1
- MainLoop.ps1
- SetupVariables.ps1
- updatehosts.ps1 Creating container bcsqlremote from image mcr.microsoft.com/businesscentral:10.0.20348.1249 d8d91965d831d4ef2406b1a6fcb605568681a19b4a0241b751eff3cdd6a397e9 Waiting for container bcsqlremote to be ready Using artifactUrl https://bcartifacts.azureedge.net/onprem/21.1.48363.48638/gb Using installer from C:\Run\210-new Installing Business Central Installing from artifacts Starting Local SQL Server WARNING: Waiting for service 'SQL Server (SQLEXPRESS) (MSSQL$SQLEXPRESS)' to start... Starting Internet Information Server Copying Service Tier Files c:\dl\onprem\21.1.48363.48638\platform\ServiceTier\Program Files c:\dl\onprem\21.1.48363.48638\platform\ServiceTier\System64Folder Copying PowerShell Scripts c:\dl\onprem\21.1.48363.48638\platform\WindowsPowerShellScripts\Cloud\NAVAdministration c:\dl\onprem\21.1.48363.48638\platform\WindowsPowerShellScripts\WebSearch Copying Web Client Files c:\dl\onprem\21.1.48363.48638\platform\WebClient\Microsoft Dynamics NAV Copying ModernDev Files c:\dl\onprem\21.1.48363.48638\platform c:\dl\onprem\21.1.48363.48638\platform\ModernDev\program files\Microsoft Dynamics NAV Copying additional files Copying ConfigurationPackages C:\dl\onprem\21.1.48363.48638\gb\ConfigurationPackages Copying Test Assemblies C:\dl\onprem\21.1.48363.48638\platform\Test Assemblies Copying Applications C:\dl\onprem\21.1.48363.48638\gb\Applications 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 185 seconds Installation complete Initializing... Setting dbserver.containerhelper.internal to 10.100.2.166 in container hosts file (copy from host hosts file) Setting host.containerhelper.internal to 172.23.32.1 in container hosts file Starting Container Hostname is bcsqlremote PublicDnsName is bcsqlremote Using Windows Authentication 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
, C:\Run\SetupDatabase.ps1: line 152 at , C:\Run\navstart.ps1: line 138 at , C:\Run\start.ps1: line 384 at , : line 1Error Using artifactUrl https://bcartifacts.azureedge.net/onprem/21.1.48363.48638/gb Using installer from C:\Run\210-new Installing Business Central Installing from artifacts Starting Local SQL Server WARNING: Waiting for service 'SQL Server (SQLEXPRESS) (MSSQL$SQLEXPRESS)' to start... Starting Internet Information Server Copying Service Tier Files c:\dl\onprem\21.1.48363.48638\platform\ServiceTier\Program Files c:\dl\onprem\21.1.48363.48638\platform\ServiceTier\System64Folder Copying PowerShell Scripts c:\dl\onprem\21.1.48363.48638\platform\WindowsPowerShellScripts\Cloud\NAVAdministration c:\dl\onprem\21.1.48363.48638\platform\WindowsPowerShellScripts\WebSearch Copying Web Client Files c:\dl\onprem\21.1.48363.48638\platform\WebClient\Microsoft Dynamics NAV Copying ModernDev Files c:\dl\onprem\21.1.48363.48638\platform c:\dl\onprem\21.1.48363.48638\platform\ModernDev\program files\Microsoft Dynamics NAV Copying additional files Copying ConfigurationPackages C:\dl\onprem\21.1.48363.48638\gb\ConfigurationPackages Copying Test Assemblies C:\dl\onprem\21.1.48363.48638\platform\Test Assemblies Copying Applications C:\dl\onprem\21.1.48363.48638\gb\Applications 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 185 seconds Installation complete Initializing... Setting dbserver.containerhelper.internal to 10.100.2.166 in container hosts file (copy from host hosts file) Setting host.containerhelper.internal to 172.23.32.1 in container hosts file Starting Container Hostname is bcsqlremote PublicDnsName is bcsqlremote Using Windows Authentication 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
, C:\Run\SetupDatabase.ps1: line 152 at , C:\Run\navstart.ps1: line 138 at , C:\Run\start.ps1: line 384 at , : line 1 New-BcContainer Telemetry Correlation Id: a09ba606-a111-497f-a82a-7b5285d8e849 Initialization of container bcsqlremote failed At C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\4.0.6\ContainerHandling\Wait-NavContainerReady.ps1:42 char:17
- ... throw "Initialization of container $containerName failed" ...
+ CategoryInfo : OperationStopped: (Initialization ...qlremote failed:String) [], RuntimeException + FullyQualifiedErrorId : Initialization of container bcsqlremote failed
tscottjendev
commented
1 year ago This is the updated script
$containerName = 'bcsqlremote'
$credential = Get-Credential -Message 'Using Windows authentication. Please enter your Windows credentials for the host computer.'
$auth = 'Windows'
$artifactUrl = Get-BcArtifactUrl -type 'OnPrem' -country 'gb' -select 'Latest'
$databaseServer = 'dbserver.containerhelper.internal' #####
$databaseInstance = ''
$databaseName = 'Demo Database BC (21-0)'
$databaseUsername = 'Docker'
$databasePassword = <password>
$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 `
-databaseServer $databaseServer -databaseInstance $databaseInstance -databaseName $databaseName `
-databaseCredential $databaseCredential `
-usessl -installCertificateOnHost `
-updateHosts
Semour81
commented
1 year ago I also had an issue with connecting to host SQL server from BC container. In my case it was a missing setting on the host SQL server. Named Piped and TCIP/IP connections had to be enabled. See https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-errors/.
tscottjendev
commented
1 year ago I am sure TCP/IP is enabled but why would I need Named Pipes? I haven't enabled that in years.
Semour81
commented
1 year ago I have tested access with a simple Powershell script and got following error:
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) Access is denied
Then I enabled TCP/IP and Names Pipes and it worked. Actually I didn't validate if both is needed. In my case both was disabled by default.
freddydk
commented
1 year ago What I learned 20 years ago was: Named pipes only works when client is running on the same machine as server. TCP/IP is needed for machine-to-machine communication (which is the case with docker containers)
Whether Named pipes will work with process isolated containers - I don't know.
Semour81
commented
1 year ago I have tested it again.
So Named Pipes are not needed in that case.
freddydk
commented
1 year ago @tscottjendev - you exposed your internal docker password in the thread above, you should change that, even though it is internal.
Did you enable mixed auth mode:
You probably did, just want to make sure.
Another question (just to make sure): You do have a database on that SQL Server called Demo Database BC (21-0)?
if these are true, please try this:
From inside the container (use Enter-BcContainer) - please try:
ping dbserver.containerhelper.internalIf that works, please try this from inside the container:
Invoke-Sqlcmd -Query 'select count(*) from dbo.Company' -Database 'Demo Database BC (21-0)' -ServerInstance 'dbserver.containerhelper.internal' -Username 'username' -Password 'password'Okay, the ping worked but the Invoke-SqlCmd command did not. It failed with a login failure for the the dbUser. The SQL Server was configured for SQL Server and Windows Authentication mode but I could not even sign in via SQL Management Studio with the login. I restarted the SQL server and then the login worked. I am certain that when I changed the server to mixed mode the server was restarted but maybe not. Anyway, I can successfully run the SQL command inside the container now.
I have re-run the script it works now. Thank you.
Is there a "troubleshooting" page I missed to find these steps to try?
freddydk
commented
1 year ago There might be a troubleshooting page on SQL docs, but these things are unrelated to containers and docker. My approach to troubleshooting is always to remove as many parameters as possible - in this case remove all scripts and see if you can connect to a SQL Server from a container at all. In other cases, create a container with the fewest number of parameters - and then work your way forward.
Happy that it worked out.
PLEASE DO NOT INCLUDE ANY PASSWORDS OR TOKENS IN YOUR ISSUE!!!
Describe the issue I am trying to connect to a remote SQL server within the container. I have specified the connection string. The db user is a SYSADMIN on the SQL server. I am getting this error:
Also, would like to know the minimum security role(s) that the db user needs to be able to make the connection. I need to use a least privileged access model.
What do I need to change to get this work? What else could be blocking it?
Scripts used to create container and cause the issue
Full output of scripts