VM Template fail for Azure SQL at SetupNavContainer.ps1

[megel]

SetupNavContainer.ps1 Fails for Azure SQL Databases at:

settipngs.ps1:

$sqlServerType = "AzureSQL"
$azureSqlServer = "sqlierpbctest02.database.windows.net"
$appBacpacUri = "https://xxxxxxxx.blob.core.windows.net/db-backups/database.bacpac?st=2019-04-16T15%3A27%3A23Z&se=2022-04-17T15%3A27%3A00Z&sp=rl&sv=2018-03-28&sr=b&sig=THMFG%2FH1ydLLYWJmEEEEEEE2B%2FSj4dN8QmLTOWuYo3c%3D"
$tenantBacpacUri = ""
$includeAppUris = ""
$enableSymbolLoading = "Yes"
$clickonce = "No"
$enableTaskScheduler = "Yes"
$licenseFileUri = ""
$publicDnsName = "xxxxxx.westeurope.cloudapp.azure.com"
$workshopFilesUrl = ""
$style = "demo"
$RunWindowsUpdate = "Yes"
$AssignPremiumPlan = "No"
$CreateTestUsers = "No"
$CreateAadUsers = "No"
$Multitenant = "No"
$WindowsInstallationType = "Server"
$WindowsProductName = "Windows Server 2019 Datacenter"

Error:

[megel]

Changing this script inside of the VM helps:

if ($sqlServerType -eq "AzureSQL") {
    if (Test-Path "c:\demo\objects.fob" -PathType Leaf) {
        Log "Importing c:\demo\objects.fob to container"
        Import-ObjectsToNavContainer -containerName $containerName -objectsFile "c:\demo\objects.fob" -sqlCredential $azureSqlCredential
    }
### >> CHANGED
    # Check for Multitenant
    if ($multitenant -eq "Yes") {
        New-NavContainerTenant -containerName $containerName -tenantId "default" -sqlCredential $azureSqlCredential
    }    
    # Included:  -ErrorAction Continue
    New-NavContainerNavUser -containerName $containerName -tenant "default" -Credential $credential -AuthenticationEmail $Office365UserName -ChangePasswordAtNextLogOn:$false -PermissionSetId "SUPER" -ErrorAction Continue
### <<
} else {
    if (Test-Path "c:\demo\objects.fob" -PathType Leaf) {
        Log "Importing c:\demo\objects.fob to container"
        $sqlCredential = New-Object System.Management.Automation.PSCredential ( "sa", $credential.Password )
        Import-ObjectsToNavContainer -containerName $containerName -objectsFile "c:\demo\objects.fob" -sqlCredential $sqlCredential
    }
}

[freddydk]

azure sql was intended for multi-tenancy only, So you are saying that it works?

[megel]

It seams to me. However the AzureSQL database was created and the container id's working after this change. I will create some scripts to import / export the database for CD maybe in waldos pull request of nav container helper are some snippets.

[megel]

When I switched my laptop, I can create a pull request 😂 too.

[megel]

@freddydk the template seams now to fail during Import of the bacpac file. I created the backup with Export-NavContainerDatabaseAsBacpac. The bacpac file is OK, because, I can import into Azure SQL - alfter the template has failed :/

Error 1 on Azure SQL:

Error encountered during the service operation. 

    Could not load package.

        File contains corrupted data.

            File contains corrupted data.

Error 2 on Azure SQL:

Error encountered during the service operation. 

    Verification of the deployment plan failed.

Warning SQL0: A project which specifies SQL Server vNext CTP as the target platform may experience compatibility issues with Microsoft Azure SQL Database v12.

Warning SQL72012: The object [data_0] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Warning SQL72012: The object [log] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Warning SQL72012: The object [XTP] exists in the target, but it will not be dropped even though you selected the 'Generate drop statements for objects that are in the target database but that are not in the source' check box.

Error SQL72030: An incompatible collation combination has been detected between the source and target. Deploying a case insensitive model to a case sensitive target can produce unintended changes and possible data loss. Consider using a case sensitive source model collation.

Any suggestion?

[megel]

OK, I discovered, the bacpac wasn't imported into Azure SQL too :( This means, Export-NavContainerDatabaseAsBacpac from NavContainerHelper does not create a baclup for AzureSQL from an SQL Express database.

[megel]

OK, I have tried these steps:

1) Export database from Container (Cronus) with Export-NavContainerDatabaseAsBacpac from NavContainerHelper:

• Import in AzureSQL from ARM template: fails
• Import in AzureSQL from Azure Portal: fails 2) Export database from Container (Cronus) with SQL Server Management Studio 18 (RC1)
• Import in AzureSQL from ARM template: fails
• Import in AzureSQL from Azure Portal: OK after ARM has failed, but before SetupNavContainer.ps1 in VM was finished :) --> ARM Template fail partialy, but VM is OK

Finally, the ARM template and SetupNavContainer.ps1 works, WHEN you are able to replace the App database during deployment.

But

on Landing Page:

inside of VM:

Get-NavContainerServerConfiguration navserver:

PS C:\ProgramData\navcontainerhelper> Get-NavContainerServerConfiguration navserver

ContainerName                                   : navserver
NetworkProtocol                                 : Default
DatabaseServer                                  : localhost
DatabaseInstance                                : SQLEXPRESS
DatabaseName                                    : Cronusde
EnableSqlConnectionEncryption                   : false
TrustSQLServerCertificate                       : false
ServerInstance                                  : NAV
EnableSqlInformationDebugger                    : true
AmountOfSqlStatementsInDebugger                 : 10
EnableLongRunningSqlStatementsInDebugger        : true
LongRunningSqlStatementsInDebuggerThreshold     : 500
ClientServicesPort                              : 7046
SOAPServicesPort                                : 7047
ODataServicesPort                               : 7048
ManagementServicesPort                          : 7045
ManagementServicesEnabled                       : true
ClientServicesEnabled                           : true
DeveloperServicesPort                           : 7049
DeveloperServicesEnabled                        : true
DeveloperServicesSSLEnabled                     : true
ExtensionAllowedTargetLevel                     : Internal
ReportPDFFontEmbedding                          : true
ReportAppDomainIsolation                        : true
EnableSaveToWordForRdlcReports                  : true
EnableSaveToExcelForRdlcReports                 : true
EnableSaveFromReportPreview                     : true
CompileBusinessApplicationAtStartup             : true
SOAPServicesEnabled                             : true
ODataServicesEnabled                            : true
ODataServicesV3EndpointEnabled                  : true
ODataServicesV4EndpointEnabled                  : true
SOAPServicesSSLEnabled                          : true
ODataServicesSSLEnabled                         : true
ODataMaxConnections                             : 0
ODataMaxConnectionsPerTenant                    : 0
DeltaServiceClientTimeout                       : 00:02:00
ODataServicesOperationTimeout                   : MaxValue
PublicODataBaseUrl                              : https://CUSTOMER.westeurope.cloudapp.azure.com:7048/NAV/OData
PublicSOAPBaseUrl                               : https://CUSTOMER.westeurope.cloudapp.azure.com:7047/NAV/WS
PublicWebBaseUrl                                : https://CUSTOMER.westeurope.cloudapp.azure.com/NAV/
PublicWinBaseUrl                                : DynamicsNAV://CUSTOMER.westeurope.cloudapp.azure.com:7046/NAV/
DefaultClient                                   : Web
ServicesOptionFormat                            : OptionCaption
ServicesLanguage                                : en-US
CalendarTwoDigitYearMax                         : -1
SOAPServicesMaxMsgSize                          : 1024
ServicesUseNTLMAuthentication                   : false
ServicesDefaultTimeZone                         : UTC
ServicesDefaultCompany                          :
ODataServicesMaxPageSize                        : 1000
ClientServicesOperationTimeout                  : MaxValue
ClientServicesProtectionLevel                   : EncryptAndSign
MaxConcurrentCalls                              : 1000
ClientServicesMaxConcurrentConnections          : 500
ClientServicesReconnectPeriod                   : 00:10:00
ClientServicesMaxNumberOfOrphanedConnections    : 20
ClientServicesCompressionThreshold              : 64
ClientServicesMaxUploadSize                     : 150
EnableDebugging                                 : false
DebuggingAllowed                                : true
ClientServicesMaxItemsInObjectGraph             : 512
ClientServicesChunkSize                         : 28
ClientServicesProhibitedFileTypes               : ade;adp;asp;bas;bat;chm;cmd;com;cpl;csh;exe;fxp;gadget;hlp;hta;inf;in
                                                  s;isp;its;js;jse;ksh;lnk;mad;maf;mag;mam;maq;mar;mas;mat;mau;mav;maw;
                                                  mda;mdb;mde;mdt;mdw;mdz;msc;msi;msp;mst;ops;pcd;pif;prf;prg;pst;reg;s
                                                  cf;scr;sct;shb;shs;url;vb;vbe;vbs;vsmacros;vss;vst;vsw;ws;wsc;wsf;wsh
ClientServicesAllowedFileTypes                  :
NASServicesStartupCodeunit                      :
NASServicesRunWithAdminRights                   : false
EnableDeadlockMonitoring                        : true
NASServicesStartupMethod                        :
NASServicesStartupArgument                      :
NASServicesEnableDebugging                      : false
ClientServicesCredentialType                    : NavUserPassword
UIElementRemovalOption                          : LicenseFileAndUserPermissions
ClientServicesTokenSigningKey                   :
ClientServicesFederationMetadataLocation        :
ServicesCertificateThumbprint                   : 5EA212A21BE4D8D6C39476E77122F91E7B109268
ServicesCertificateValidationEnabled            : false
DisableTokenSigningCertificateValidation        : false
TokenSigningCertificateValidationMode           : IssuerNameValidation
DataCacheSize                                   : 10
SessionEventTableRetainInterval                 : 90.00:00:00
NonInteractiveSessionsLogRetainInterval         : 5.00:00:00
SqlCommandTimeout                               : 00:30:00
SqlConnectionTimeout                            : 00:01:30
BufferedInsertEnabled                           : true
DisableSmartSql                                 : false
EnableFullALFunctionTracing                     : false
Multitenant                                     : False
SqlConnectionIdleTimeout                        : 00:05:00
EnableALServerFileAccess                        : true
EnableApplicationChannelLog                     : true
EncryptionProvider                              : LocalKeyFile
ExchangeAuthenticationMetadataLocation          :
AppIdUri                                        :
WSFederationLoginEndpoint                       :
AzureActiveDirectoryClientId                    :
AzureActiveDirectoryClientSecret                :
AzureActiveDirectoryClientCertificateThumbprint :
EnableMembershipEntitlement                     :
MaxRowsToExportToExcel                          : MaxValue
EnableTaskScheduler                             : true
TaskSchedulerMaximumConcurrentRunningTasks      : 10
ClientServicesKeepAliveInterval                 : 00:02:00
UseSimplifiedFilters                            : false
ExcelAddInAzureActiveDirectoryClientId          :
DisableWriteInsideTryFunctions                  : true
ExtendedSecurityTokenLifetime                   : 0
ClientServicesIdleClientTimeout                 : MaxValue
ODataEnableExcelAddInAnnotations                : true
NavHttpClientMaxResponseContentSize             : 150
NavHttpClientMaxTimeout                         : 00:05:00
SqlParametersByOrdinal                          : true
ClientBuildRestriction                          : WarnClient
TraceLevel                                      : Normal
ExternalTraceLevel                              : Error
EnableDataExportImport                          : true
MaxStreamReadSize                               : 1000000
ValidAudiences                                  :
DefaultLanguage                                 :
SupportedLanguages                              :
ApiServicesEnabled                              : True
ApiSubscriptionsEnabled                         : true
ApiSubscriptionExpiration                       : 3
ApiSubscriptionNotificationUrlTimeout           : 5000
ApiSubscriptionSendingNotificationTimeout       : 30000
ApiSubscriptionDelayTime                        : 30000
ApiSubscriptionMaxNumberOfNotifications         : 100
ApiSubscriptionMaxNumberOfSubscriptions         : 200
EnableSymbolLoadingAtServerStartup              : True
SqlLongRunningThreshold                         : 1000
SqlBulkImportBatchSize                          : 448
UseIncrementalCompanyDelete                     : true
TaskSchedulerSystemTaskStartTime                : 00:00:00
TaskSchedulerSystemTaskEndTime                  : 23:59:59
XmlMetadataCacheSize                            : 500
AllowSessionWhileSyncAndDataUpgrade             : false
SearchTimeout                                   : 00:00:10
OverwriteExistingTranslations                   : true
LockoutPolicyFailedAuthenticationCount          : 0
LockoutPolicyFailedAuthenticationWindow         : 0
ReplaceReportExecutionTimeWithClientTime        : true
UseFindMinusWhenPopulatingPage                  : true
DisableQueryHintOptimizeForUnknown              : false
DisableQueryHintForceOrder                      : true
DisableQueryHintLoopJoin                        : true
SecurityProtocol                                : Tls12
AzureKeyVaultClientId                           : 00000000-0000-0000-0000-000000000000
AzureKeyVaultClientCertificateStoreLocation     : LocalMachine
AzureKeyVaultClientCertificateStoreName         : My
AzureKeyVaultClientCertificateThumbprint        :
AzureKeyVaultKeyUri                             :

@freddydk - NO, Azure SQL is not working :(

[freddydk]

I will have a look at this one of the next days

[megel]

I found the problem:

if ("$appBacpacUri" -ne "" -and "$tenantBacpacUri" -ne "") {
    if ("$sqlServerType" -eq "SQLExpress") {
        $additionalParameters += @("--env appbacpac=$appBacpacUri",
                                   "--env tenantbacpac=$tenantBacpacUri")
    } else {
        Log "using $azureSqlServer as database server"
        $params += @{ "databaseServer"     = "$azureSqlServer"
                      "databaseInstance"   = ""
                      "databaseName"       = "App"
                      "databaseCredential" = $azureSqlCredential }
        $multitenant = "Yes"
    }
}

In my case, I use only $appBacpacUri so I'll change the condition into:

# Enabled the setup of Azure SQL for existing $appBacpacUri 
if ("$appBacpacUri" -ne "") {
    if ("$sqlServerType" -eq "SQLExpress" -and "$tenantBacpacUri" -ne "") {
        $additionalParameters += @("--env appbacpac=$appBacpacUri",
                                   "--env tenantbacpac=$tenantBacpacUri")
    } else {
        Log "using $azureSqlServer as database server"
        $params += @{ "databaseServer"     = "$azureSqlServer"
                      "databaseInstance"   = ""
                      "databaseName"       = "App"
                      "databaseCredential" = $azureSqlCredential }
        # $tenantBacpacUri for check multi tenant included 
        if ("$tenantBacpacUri" -ne "") {
            $multitenant = "Yes"
        }
    }
}

… and create another pull request

[freddydk]

is in dev branch, will be in master soon

[freddydk]

Shipped