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
379 stars 243 forks source link

Remote SQL authentication issues with BC17 container. #1553

Closed GreatScott000 closed 3 years ago

GreatScott000 commented 3 years ago

PLEASE DO NOT INCLUDE ANY PASSWORDS OR TOKENS IN YOUR ISSUE!!!

Describe the issue I am trying to invoke the database conversion for BC17 in a container that is connected to a remote SQL server, I am getting 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. Try again later or contact your system administrator.
    • CategoryInfo : NotSpecified: (0:Int32) [Invoke-NAVApplicationDatabaseConversion], NavCSideException
    • FullyQualifiedErrorId : dbserver.containerhelper.internal,Microsoft.Dynamics.Nav.Management.Cmdlets.InvokeNAVApplicationDatabaseConversion

The database is accessible from inside the container as proven in the script below. 

$SettingsFile = 'C:\shared\Settings.json'
$Settings = Get-Content -Path $SettingsFile | ConvertFrom-Json

$securePassword = ConvertTo-SecureString -String $Settings.DatabasePassword -AsPlainText -Force
$sqlCredential = New-Object pscredential $Settings.DatabaseUser, $securePassword

$Config = $Settings.Upgrade140170
$DbsCmd = "SELECT name FROM sys.databases WHERE database_id > 4 and [state] = 0"

$Result = Invoke-SQLCmd -ServerInstance $Config.DatabaseServer -Query $DbsCmd -Credential $sqlCredential `
    | Where-Object {$_.Name -EQ $Config.DatabaseName}

$SQLCMD = "SELECT TABLE_NAME FROM [$($Result.name)].INFORMATION_SCHEMA.TABLES WHERE Table_name like '%`$Customer%'"
Invoke-SQLCmd -ServerInstance $Config.DatabaseServer -Query $SQLCmd -Credential $sqlCredential 

Get-NAVApplication -ServerInstance $Config.ServerInstance

Invoke-NAVApplicationDatabaseConversion -DatabaseServer $Config.DatabaseServer -DatabaseName $Config.DatabaseName -Confirm:$false -Force

With this result

TABLE_NAME

VS Test 12062019$Customer
VS Test 12062019$Customer Amount
VS Test 12062019$Customer Bank Account
VS Test 12062019$Customer Discount Group
VS Test 12062019$Customer Posting Group
VS Test 12062019$Customer Price Group
VS Test 12062019$Customer Template
VS Test 30-01-20$Customer
VS Test 30-01-20$Customer Amount
VS Test 30-01-20$Customer Bank Account
VS Test 30-01-20$Customer Discount Group
VS Test 30-01-20$Customer Posting Group
VS Test 30-01-20$Customer Price Group
VS Test 30-01-20$Customer Template
VS Test 30-07-19$Customer
VS Test 30-07-19$Customer Amount
VS Test 30-07-19$Customer Bank Account
VS Test 30-07-19$Customer Discount Group
VS Test 30-07-19$Customer Posting Group
VS Test 30-07-19$Customer Price Group
VS Test 30-07-19$Customer Template

Database server Database name ApplicationVersion ApplicationFamily

DBSERVER.CONTAINERHELPER.INTERNAL vect17000 17.2.19367.19735 GB

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.
    • CategoryInfo : NotSpecified: (0:Int32) [Invoke-NAVApplicationDatabaseConversion], NavCSideException
    • FullyQualifiedErrorId : dbserver.containerhelper.internal,Microsoft.Dynamics.Nav.Management.Cmdlets.InvokeNAVApplicationDatabaseConversion

I found this entry in the remote SQL server's event log:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. [CLIENT: 10.0.1.7]

So I added NT AUTHORITY\ANONYMOUS LOGON and gave it sysadmin rights and the Invoke-NAVApplicationDatabaseConversion command works. However this is a huge security risk. There has to be a better way.

Shouldn't the command be using the database user and password set in the container? It has worked all through the process of converting this database from NAV2016, and BC14. It what is being passed as the SQLCredential above for the commands. If so, what am I missing?

Is there a way to set the container's NAV service account to be something that the remote SQL server will recognize?

This is how the service looks

[VECT17000]: PS C:\Run> Get-NAVServerInstance BC

ServerInstance : MicrosoftDynamicsNavServer$BC DisplayName : Dynamics 365 Business Central Server [BC] State : Running ServiceAccount : NT AUTHORITY\SYSTEM Version : 17.0.19353.19730 Default : True

This is the container's NAV service config

[VECT17000]: PS C:\Run> Get-NAVServerConfiguration BC

key value

ALCompatibleDateFormatCultureList
ALFunctionTimingEnabled true
ALLongRunningFunctionTracingThresholdForApplicationInsights 10000
AllowReadingCrossTenantApplicationDatabaseTables false
AllowSessionWhileSyncAndDataUpgrade true
AmountOfSqlStatementsInDebugger 10
ApiServicesEnabled True
ApiSubscriptionDelayTime 30000
ApiSubscriptionExpiration 3
ApiSubscriptionMaxNumberOfNotifications 100
ApiSubscriptionMaxNumberOfSubscriptions 200
ApiSubscriptionNotificationUrlTimeout 5000
ApiSubscriptionsEnabled true
ApiSubscriptionSendingNotificationTimeout 30000
AppIdUri
ApplicationInsightsInstrumentationKey
AzureActiveDirectoryClientCertificateThumbprint
AzureActiveDirectoryClientId
AzureActiveDirectoryClientSecret
AzureKeyVaultAppSecretsPublisherValidationEnabled true
AzureKeyVaultClientCertificateStoreLocation LocalMachine
AzureKeyVaultClientCertificateStoreName My
AzureKeyVaultClientCertificateThumbprint
AzureKeyVaultClientId 00000000-0000-0000-0000-000000000000
AzureKeyVaultKeyUri
BufferedInsertEnabled true
CalendarTwoDigitYearMax -1
ChildSessionsMaxConcurrency 5
ChildSessionsMaxQueueLength 100
ClientBuildRestriction WarnClient
ClientServicesAllowedFileTypes
ClientServicesChunkSize 28
ClientServicesCompressionThreshold 64
ClientServicesCredentialType NavUserPassword
ClientServicesEnabled true
ClientServicesFederationMetadataLocation
ClientServicesIdleClientTimeout MaxValue
ClientServicesKeepAliveInterval 00:02:00
ClientServicesMaxConcurrentConnections 500
ClientServicesMaxItemsInObjectGraph 512
ClientServicesMaxNumberOfOrphanedConnections 20
ClientServicesMaxUploadSize 350
ClientServicesOperationTimeout MaxValue
ClientServicesPort 7046
ClientServicesProhibitedFileTypes ade;adp;asp;bas;bat;chm;cmd;com;cpl;csh;exe;fxp;gadget;hlp;hta;inf;ins;isp;its;js;jse;ksh;lnk;ma... ClientServicesProtectionLevel EncryptAndSign
ClientServicesReconnectPeriod 00:10:00
ClientServicesTokenSigningKey
CompileBusinessApplicationAtStartup true
DatabaseInstance
DatabaseName vect17000
DatabaseServer dbserver.containerhelper.internal
DatabaseUserName Docker
DataCacheSize 10
DebuggingAllowed true
DefaultClient Web
DefaultLanguage
DeltaServiceClientTimeout 00:02:00
DeveloperServicesEnabled true
DeveloperServicesPort 7049
DeveloperServicesSSLEnabled false
DisableQueryHintForceOrder true
DisableQueryHintLoopJoin true
DisableQueryHintOptimizeForUnknown false
DisableSmartSql false
DisableTokenSigningCertificateValidation false
DisableWriteInsideTryFunctions true
EnableALServerFileAccess true
EnableApplicationChannelLog true
EnableDataExportImport true
EnableDeadlockMonitoring false
EnableDebugging false
EnableExclusiveApplicationPackageRole true
EnableExclusiveExistsCheckOnModify false
EnableFullALFunctionTracing false
EnableLongRunningSqlStatementsInDebugger true
EnableMembershipEntitlement
EnableMultithreadedCompilation true
EnablePartialRecords true
EnableSaveFromReportPreview true
EnableSaveToExcelForRdlcReports true
EnableSaveToWordForRdlcReports true
EnableSqlConnectionEncryption true
EnableSqlInformationDebugger true
EnableSqlQueryTopParameterization false
EnableSqlReadOnlyReplicaSupport false
EnableSymbolLoadingAtServerStartup False
EnableTaskScheduler false
EnableUserConsistencyValidationOnTasks true
EncryptionProvider LocalKeyFile
ExcelAddInAzureActiveDirectoryClientId
ExchangeAuthenticationMetadataLocation
ExtendedSecurityTokenLifetime 0
ExtensionAllowedTargetLevel Internal
ExternalTraceLevel Error
IntegrationRecordsTableId 5151
LegacyOptionCaptionResolution false
LockoutPolicyFailedAuthenticationCount 0
LockoutPolicyFailedAuthenticationWindow 0
LongRunningSqlStatementsInDebuggerThreshold 500
ManagementServicesEnabled true
ManagementServicesPort 7045
MaxConcurrentCalls 1000
MaxRowsToExportToExcel MaxValue
MaxStreamReadSize 1000000
Multitenant False
NASServicesEnableDebugging false
NASServicesRunWithAdminRights false
NASServicesStartupArgument
NASServicesStartupCodeunit
NASServicesStartupMethod
NavHttpClientMaxResponseContentSize 150
NavHttpClientMaxTimeout 00:05:00
NetworkProtocol Default
NonInteractiveSessionsLogRetainInterval 5.00:00:00
ODataEnableExcelAddInAnnotations true
ODataMaxConnections 0
ODataMaxConnectionsPerTenant 0
ODataServicesEnabled true
ODataServicesMaxPageSize 20000
ODataServicesOperationTimeout 00:08:00
ODataServicesPort 7048
ODataServicesSSLEnabled false
ODataServicesV3EndpointEnabled true
ODataServicesV4EndpointEnabled true
ODataV4MaxConcurrentRequests 5
ODataV4MaxRequestQueueSize 95
OverwriteExistingTranslations true
PageBackgroundTaskDefaultTimeout 00:02:00
PageBackgroundTaskMaxTimeout 00:10:00
ProtectedDatabasePassword Dsw5DQYRJmDNvnVxkPl2eWaKTeNvlkP2w57W9ImR9QL+xZrbUa/mJjDdAOuy/vWZQ/HTsToshyPsR76T7iURdILzmky56PLq... PublicODataBaseUrl http://vect17000:7048/BC/OData
PublicSOAPBaseUrl http://vect17000:7047/BC/WS/
PublicWebBaseUrl http://vect17000/BC/
PublicWinBaseUrl DynamicsNAV://vect17000:7046/BC/
QueryMaxRows MaxValue
QueryTimeout MaxValue
ReplaceReportExecutionTimeWithClientTime true
ReportAppDomainIsolation true
ReportDefaultMaxDocuments 200
ReportDefaultMaxRows 500000
ReportDefaultTimeout 06:00:00
ReportMaxDocuments 500
ReportMaxRows 1000000
ReportPDFFontEmbedding true
ReportTimeout 12:00:00
RequiredExtensions
SearchTimeout 00:00:10
SecurityProtocol Tls12
ServerInstance BC
ServicesCertificateThumbprint 602373DE08FA7F1F372EF33B1A4549D115FC2544
ServicesCertificateValidationEnabled false
ServicesDefaultCompany
ServicesDefaultTimeZone UTC
ServicesLanguage en-US
ServicesOptionFormat OptionCaption
ServicesUseNTLMAuthentication false
SessionEventTableRetainInterval 90.00:00:00
SnapshotDebuggerEnabled true
SnapshotDebuggerServicesPort 7083
SnapshotDebuggerServicesSSLEnabled false
SOAPMaxConcurrentRequests 5
SOAPMaxConnections 0
SOAPMaxConnectionsPerTenant 0
SOAPMaxRequestQueueSize 95
SOAPServicesEnabled true
SOAPServicesMaxMsgSize 65536
SOAPServicesOperationTimeout 00:10:00
SOAPServicesPort 7047
SOAPServicesSSLEnabled false
SolutionVersionExtension 00000000-0000-0000-0000-000000000000
SqlBulkImportBatchSize 448
SqlCommandTimeout 00:30:00
SqlConnectionIdleTimeout 00:05:00
SqlConnectionTimeout 00:01:30
SqlLockTimeoutOverride 0
SqlLongRunningThreshold 750
SqlLongRunningThresholdForApplicationInsights 750
SqlManagementCommandTimeout -1
SqlTracingAllowed true
SupportedLanguages
TaskSchedulerMaximumConcurrentRunningTasks 3
TaskSchedulerSystemTaskEndTime 23:59:59
TaskSchedulerSystemTaskStartTime 00:00:00
TestAutomationEnabled true
TokenSigningCertificateValidationMode IssuerNameValidation
TraceLevel Normal
TrustSQLServerCertificate true
UIElementRemovalOption LicenseFileAndUserPermissions
UnsupportedLanguageIds 1034
UseFindMinusWhenPopulatingPage true
UseIncrementalCompanyDelete false
UseSimplifiedFilters false
ValidAudiences
WSFederationLoginEndpoint
XmlMetadataCacheSize 500

Scripts used to create container and cause the issue

    $imageName = 'internal'
    $dnsServer = '10.0.1.4'
    $password = '<password>'
    $securePassword = ConvertTo-SecureString -String $password -AsPlainText -Force
    $credential = New-Object pscredential '<userName', $securePassword
    $auth = 'UserPassword'
    $artifactUrl = $artifactUrl
    $databaseServer = $dbServer
    $databaseInstance = ''
    $databaseUsername = '<userName>'
    $databasePassword = '<password>'
    $databaseSecurePassword = ConvertTo-SecureString -String $databasePassword -AsPlainText -Force
    $databaseCredential = New-Object pscredential $databaseUsername, $databaseSecurePassword
    New-BcContainer `
        -accept_eula `
        -containerName $Settings.containerName `
        -credential $credential `
        -auth $auth `
        -artifactUrl $Settings.artifactUrl `
        -imageName $imageName `
        -databaseServer $databaseServer -databaseInstance $databaseInstance -databaseName $databaseName `
        -databaseCredential $databaseCredential `
        -licenseFile $Settings.licenseFile `
        -dns $dnsServer `
        -includeCSIDE:$Settings.includeCSIDE `
        -includeAL:$Settings.includeAL `
        -updateHosts `
        -doNotExportObjectsToText `
        -additionalParameters @('-v "F:\Clients\VECT001\Shared":C:\Shared') `

Full output of scripts

BcContainerHelper is version 1.0.15
BcContainerHelper is running as administrator
Host is Microsoft Windows Server 2019 Datacenter - ltsc2019
Docker Client Version is 19.03.12
Docker Server Version is 19.03.12
Removing container vect17000
Removing vect17000 from host hosts file
Removing vect17000-* from host hosts file
Removing f:\bccontainerhelper\Extensions\vect17000
Fetching all docker images
ArtifactUrl and ImageName specified
Image internal:onprem-17.2.19367.19735-gb-nodb already exists
Using image internal:onprem-17.2.19367.19735-gb-nodb
Creating Container vect17000
Version: 17.2.19367.19735-gb
Style: onprem
Multitenant: No
Platform: 17.0.19353.19730
Generic Tag: 1.0.1.2
Container OS Version: 10.0.17763.1637 (ltsc2019)
Host OS Version: 10.0.17763.1637 (ltsc2019)
Using process isolation
Using locale en-GB
Disabling the standard eventlog dump to container log every 2 seconds (use -dumpEventLog to enable)
Using license file F:\License\BC17License.flf
Additional Parameters:
-v "F:\Clients\VECT001\Shared":C:\Shared
Files in f:\bccontainerhelper\Extensions\vect17000\my:
- AdditionalOutput.ps1
- license.flf
- MainLoop.ps1
- SetupVariables.ps1
- updatehosts.ps1
Creating container vect17000 from image internal:onprem-17.2.19367.19735-gb-nodb
b8fba20e8d594b0a63e47e8ac9bbf37ff07f5978b79082b4decc105e076e2eb1
Waiting for container vect17000 to be ready
Initializing...
Setting dbserver.containerhelper.internal to 10.0.1.4 in container hosts file (copy from host hosts file)
Setting host.containerhelper.internal to 172.30.0.1 in container hosts file
Starting Container
Hostname is vect17000
PublicDnsName is vect17000
Using NavUserPassword Authentication
Starting Internet Information Server
Import Encryption Key
Creating Self Signed Certificate
Self Signed Certificate Thumbprint 602373DE08FA7F1F372EF33B1A4549D115FC2544
Modifying Service Tier Config File with Instance Specific Settings
Starting Service Tier
Registering event sources
Creating DotNetCore Web Server Instance
Using license file 'c:\run\my\license.flf'
Import License
Creating http download site
Container IP Address: 172.30.14.46
Container Hostname  : vect17000
Container Dns Name  : vect17000
Web Client          : http://vect17000/BC/
Dev. Server         : http://vect17000
Dev. ServerInstance : BC
Setting vect17000 to 172.30.14.46 in host hosts file

Files:
http://vect17000:8080/ALLanguage.vsix

Container Total Physical Memory is 28.0Gb
Container Free Physical Memory is 18.0Gb

Initialization took 233 seconds
Ready for connections!
Reading CustomSettings.config from vect17000
Creating Desktop Shortcuts for vect17000
Creating .net Assembly Reference Folder for VS Code
Copying DLLs from C:\Windows\assembly to assemblyProbingPath
Copying DLLs from C:\Program Files\Microsoft Dynamics NAV\170\Service to assemblyProbingPath
Copying DLLs from C:\Program Files (x86)\Microsoft Dynamics NAV\170\RoleTailored Client to assemblyProbingPath
Copying DLLs from C:\Test Assemblies\Mock Assemblies to assemblyProbingPath
Copying DLLs from C:\Program Files (x86)\Open XML SDK to assemblyProbingPath
Container vect17000 successfully created

Use:
Get-BcContainerEventLog -containerName vect17000 to retrieve a snapshot of the event log from the container
Get-BcContainerDebugInfo -containerName vect17000 to get debug information about the container
Enter-BcContainer -containerName vect17000 to open a PowerShell prompt inside the container
Remove-BcContainer -containerName vect17000 to remove the container again
docker logs vect17000 to retrieve information about URL's again

Screenshots If applicable, add screenshots to help explain your problem.

Additional context

GreatScott000 commented 3 years ago

So I may be onto the problem related to encryption keys. Checking that out now. (Realized this while writing up the problem).

GreatScott000 commented 3 years ago

So, I have imported the encryption into the database. The same on that was used to create the container. Still get the error. So I think the Invoke-NAVApplicationDatabaseConversion function is using the service account (NT Authority\System) to authentication and not the username/password configured for the service.

How do I fix this?

freddydk commented 3 years ago

https://docs.microsoft.com/en-us/powershell/module/microsoft.dynamics.nav.management/invoke-navapplicationdatabaseconversion?view=businesscentral-ps-16

There is a parameter called ApplicationDatabaseCredentials - I guess that should be your sqlcredential

GreatScott000 commented 3 years ago

Thank you @freddydk I looked at the "help" in PowerShell ISE about 19 times yesterday sure that it should have something like that and did not see it (nor did my 3 juniors when I asked for their help). I got distracted by the message which I suppose is technically accurate but not complete. I will try it out and see what happens. I am sure it will work.

GreatScott000 commented 3 years ago

That worked. Thanks again! Sometimes I get "tunnel vision" and just need another perspective.