10

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&amp; 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}"}]}

5

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.

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

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

0

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.

  • I am setting the sqlServerPassword via the azuredeploy.parameters.json file. That seems to be working fine for the admin accounts. It appears to be throwing the 12345 password when trying to create the role specific users within the xConnect package. I'll try setting it as a parameter directly in the command rather than the json file and see if that goes through. – Joe Behrens Jan 11 '18 at 21:31
  • If that fails I think I'm going to try editing the parameters.xml files in the xConnect wdp packages with my own default passwords and see it will deploy and run that way. – Joe Behrens Jan 11 '18 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.