SQL user creation is setting password of 12345, fails deployment

Question

Using the updated Sitecore Azure Toolkit 2.0.1 rev. 171218, which has resources for 9.0.1, I'm hitting an error on the xc-collect\MSDeploy stage where it's attempting to apply the default sql user password of 12345 which is against Azure's password policy. When running for 9.0.0 a random generated password is created for each of these sql user accounts.

I'm using the new WDP packages for 9.0.1 however the ARM templates are still 9.0.0 as I don't see an updated version on github (Sitecore-Azure-Quickstart-Templates). Should I be attempting to modify the WDP packages or is anyone aware of a new ARM template available for 9.0.1 to be able to deploy to Azure PaaS?

Full Error from Azure

{"code":"DeploymentFailed","message":"At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/arm-debug for usage details.","details":[{"code":"Conflict","message":"{\r\n \"status\": \"failed\",\r\n \"error\": {\r\n \"code\": \"ResourceDeploymentFailure\",\r\n \"message\": \"The resource operation completed with terminal provisioning state 'failed'.\",\r\n \"details\": [\r\n {\r\n \"code\": \"Failed\",\r\n \"message\": \"Package deployment failed\r\nAppGallery Deploy Failed: 'Microsoft.Web.Deployment.DeploymentDetailedClientServerException: An error occurred during execution of the database script. The error occurred between the following lines of the script: \\"1\\" and \\"3\\". The verbose log might have more information about the error. The command started with the following:\r\n\\"CREATE USER [messaginguser] WITH PASSWORD = '12345\\"\r\n Password validation failed. The password does not meet policy requirements because it is too short. http://go.microsoft.com/fwlink/?LinkId=178587 Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE. ---> System.Data.SqlClient.SqlException: Password validation failed. The password does not meet policy requirements because it is too short.\r\n at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\\r\\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\\r\\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\\r\\n at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)\\r\\n at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\r\n at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)\r\n --- End of inner exception stack trace ---\r\n at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)\r\n at Microsoft.Web.Deployment.DBConnectionWrapper.ExecuteSql(DBStatementInfo sqlStatement, DeploymentBaseContext baseContext, Int32 timeout)\r\n at Microsoft.Web.Deployment.SqlScriptToDBProvider.AddHelper(DeploymentObject source, Boolean whatIf)\r\n at Microsoft.Web.Deployment.DeploymentObject.AddChild(DeploymentObject source, Int32 position, DeploymentSyncContext syncContext)\r\n at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAddChild(DeploymentObject destParent, DeploymentObject sourceObject, Int32 position)\r\n at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\r\n at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\r\n at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)\r\n at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable, Nullable`1 syncPassId, String syncSessionId)\r\n at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)\r\n at Microsoft.Web.Deployment.WebApi.AppGalleryPackage.Deploy(String deploymentSite, String siteSlotId, Boolean doNotDelete)\r\n at Microsoft.Web.Deployment.WebApi.DeploymentController.<DownloadAndDeployPackage>d__17.MoveNext()'\"\r\n }\r\n ]\r\n }\r\n}"}]}

Answer 1

I found the problem. The 9.0.1 parameters.xml file in the Sitecore 9.0.1 rev. 171219 (Cloud)_xp1collection.scwdp.zip has a parameter for Messaging Database Application User:

<parameter name="Messaging Database Application User Name" description="Messaging Database Application User Name" defaultValue="messaginguser" tags="">
<parameterEntry type="TextFile" scope="createuser_text_messaging.sql" match="PlaceHolderForUser" />

That user is not referenced in the 9.0.0 ARM template sample's application-xconnect.json file so it was defaulting to 12345. I've added a param in the json file to create and send along a username and password for that parameter:

"xcMdSqlDatabaseUserName": {
  "type": "string",
  "minLength": 1,
  "defaultValue": "xcmduser"
},
"xcMdSqlDatabasePassword": {
  "type": "securestring",
  "minLength": 8,
  "defaultValue": "[concat(toUpper(uniqueString('xcmd', parameters('passwordSalt'))), uniqueString('xcmd', parameters('sqlServerPassword'), parameters('passwordSalt')), toUpper(uniqueString('xcmd', parameters('sitecoreAdminPassword'), parameters('passwordSalt'))), '4@')]"
},

And then passing it to the MSDeploy package:

{
  "name": "[concat(variables('xcCollectWebAppNameTidy'), '/', 'MSDeploy')]",
  "type": "Microsoft.Web/sites/extensions",
  "location": "[parameters('location')]",
  "apiVersion": "[variables('webApiVersion')]",
  "properties": {
    "packageUri": "[parameters('xcCollectMsDeployPackageUrl')]",
    "dbType": "SQL",
    "connectionString": "[concat('Data Source=tcp:', variables('sqlServerFqdnTidy'), ',1433;Initial Catalog=master;User Id=', parameters('sqlServerLogin'), ';Password=', parameters('sqlServerPassword'), ';')]",
    "setParameters": {
      "IIS Web Application Name": "[variables('xcCollectWebAppNameTidy')]",
      "Database Server Name": "[variables('sqlServerFqdnTidy')]",
      "Database Admin User Name": "[parameters('sqlServerLogin')]",
      "Database Admin User Password": "[parameters('sqlServerPassword')]",
      "Collection Database Server Name": "[variables('sqlServerFqdnTidy')]",
      "Collection Shard Map Manager Database Name": "[variables('shardMapManagerSqlDatabaseNameTidy')]",
      "Collection Shard 0 Database Name": "[variables('shard0SqlDatabaseNameTidy')]",
      "Collection Shard 1 Database Name": "[variables('shard1SqlDatabaseNameTidy')]",
      "Processing Pools Database Name": "[variables('poolsSqlDatabaseNameTidy')]",
      "Marketing Automation Database Name": "[variables('maSqlDatabaseNameTidy')]",
      "Collection Database Application User Name": "[parameters('xcShardMapManagerSqlDatabaseUserName')]",
      "Collection Database Application User Password": "[parameters('xcShardMapManagerSqlDatabasePassword')]",
      "Processing Pool Database Application User Name": "[parameters('poolsSqlDatabaseUserName')]",
      "Processing Pool Database Application User Password": "[parameters('poolsSqlDatabasePassword')]",
      "Marketing Automation Database Application User Name": "[parameters('xcMaSqlDatabaseUserName')]",
      "Marketing Automation Database Application User Password": "[parameters('xcMaSqlDatabasePassword')]",
      "Messaging Database Application User Name": "[parameters('xcMdSqlDatabaseUserName')]",
      "Messaging Database Application User Password": "[parameters('xcMdSqlDatabasePassword')]",
      "XConnect Server Configuration Environment": "[parameters('xcServerConfigurationEnvironment')]",
      "XConnect Server Certificate Validation Thumbprint": "[parameters('authCertificateThumbprint')]",
      "Allow Invalid Client Certificates": "[parameters('allowInvalidClientCertificates')]",
      "XConnect Server Application Insights Key": "[reference(resourceId('Microsoft.Insights/Components', variables('appInsightsNameTidy')), variables('appInsightsApiVersion')).InstrumentationKey]",
      "XConnect Server Instance Name": "Collection",
      "License Xml": "[parameters('licenseXml')]"
    }
  }
},

I'm not sure what other differences might come up so hopefully a new 9.0.1 template hits github soon.

Answer 2

FYI, and in case it could help, the new Sitecore ARM Templates just had been released here: https://github.com/Sitecore/Sitecore-Azure-Quickstart-Templates with this commit: https://github.com/Sitecore/Sitecore-Azure-Quickstart-Templates/commit/d258f794489abc3e56c67f27af708b519cc157d3

Answer 3

If you don't pass in a Sql password to the ARM template it will fallback to the (unsafe) defaults in the MsDeploy package.

Try setting the sqlServerPassword parameter when calling the PowerShell script to deploy your instance. In their example they have a $additionalParams property that creates an object to pass to the ARM template.

Example usage:

$additionalParams.sqlServerPassword = "Super_Secure_password"

Here they have some additional information on what parameters you can pass to the template.