Hiccups during Sitecore 9 XP0 Installation on a server

Recently we installed Sitecore 9.0.1 rev 171219 (XP0) on-premise mode on a server machine. This installation was a bit different than my previous one on a local developer machine. Because:

1. We wanted to install on a different drive rather System Drive.
2. Doesn’t have Visual Studio and SQL Server Install on the box.
3. Our SQL server password policies were different than the OOTB passwords provided in the installation JSON files

During the installation we face couple of hiccups hence thought of blogging all of them in a consolidated post.

Error 1: ERROR_SQLCLRTYPES_NEEDED_FOR_SQL_PROVIDER

Exception in logs:

Error code: ERROR_SQLCLRTYPES_NEEDED_FOR_SQL_PROVIDER

Root cause: After all the pre-requisites are done and when we were actually executing the Powershell script to install Sitecore 9, the very first error which we faced was the missing dependencies on dacpac. We were on x64 environment which need both x64 and x86 versions of SQlSysCLRTypes.msi and DacFramework.msi to be installed. We missed to install the x86 version of SQlSysCLRTypes.msi, which caused the error.

Solution: Make sure you install both x64 and x86 version of both MSI, especially if you are on x64 environment.

Error 2: The database containment option has been changed to None

Exception in logs:

Error Code: ERROR_EXECUTING_METHOD
More Information: Could not deploy package.
Warning SQL72023: The database containment option has been changed to None.  This may result in deployment failure if the state of the database is not compliant with this containment level.
Error SQL72014: .Net SqlClient Data Provider: Msg 12809, Level 16, State 1, Line 5 You must remove all users with password before setting the containment property to NONE.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = NONE
            WITH ROLLBACK IMMEDIATE;
    END

Root cause: When the installation interrupted, and we are re-installing is multiple times, the Database Containment flag reset to None.

Solution: Execute the SQL script again to set the flag to 1.

Error 3: Password validation failed

Exception in logs:

Info: Opening SQL Connection with connection string 'data source=mysqlserver;initial catalog=Sitecore.Messaging;user id=sitecoresvc;pooling=False'. The 'transacted' setting for this connection is 'False'.
Error Code: ERROR_SQL_EXECUTION_FAILURE
More Information: An error occurred during execution of the database script. The error occurred between the following lines of the script: "9" and "12". The verbose log might have more information about the error. The command started with the following:
"CREATE USER [messaginguser] WITH PASSWORD = '123"
 Password validation failed. The password does not meet Windows policy requirements because it is too short. 

Root cause: We have SQL server password policies that a password must have alphabets, at least a number and at least a special character in it. Hence the Default Passwords (12345 or Test12345) in various parameters in sitecore-XP0.json and xconnect-xp0.json files were not fulfilling the password policies and we were getting “Password Validation Failed” exception.

Solution: We need to update all the default passwords provided in JSON files by providing password which fulfils the policies. While doing my research I also raised a Sitecore Stack Exchange Question as my routine habit and thanks @gatagordo for the inputs.

Error 4: Cannot process argument transformation on parameter ‘Signer’

Exception in logs:

Cannot process argument transformation on parameter 'Signer'. 
Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Security.Cryptography.X509Certificates.X509Certificate2".

Root cause: Sometimes Sitecore 9 SIF installation script fails as multiple certificates found with same DNS name. Due to any error, when we are re-installing Sitecore 9 using SIF iteratively, the SIF script might get failed.

Solution: You need to remove all the certificates which are created during the previous installation. Please refer this question on Sitecore Stack Exchange for the steps to remove the certs.

Error 5: The term ‘sqlcmd’ is not recognized

Exception in logs:

"The running command stopped because the preference variable "ErrorActionPreference" 
or common parameter is set to Stop: The term 'sqlcmd' is not recognized as the name of 
a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again."

Root cause: If you are installing Sitecore 9.0.1 and following the installation guide there is a miss in the steps. It doesn’t ask to install Microsoft ODBC Driver for SQL Server and Microsoft Command Line Utilities for SQL Server, which are a must to have pre-requisites.

Solution: If Visual Studio and SQL Server are not Installed on the box you need to install Microsoft ODBC Driver for SQL Server and Microsoft Command Line Utilities for SQL Server. Make sure you download one for SQL 2016 and then install. Please refer this question on Sitecore Stack Exchange for more details.

Error 6: ERROR_SMO_NEEDED_FOR_SQL_PROVIDER

Exception in logs:

Error Code: ERROR_SMO_NEEDED_FOR_SQL_PROVIDER
More Information: The SQL provider cannot run because of a missing dependency. Please make sure that Microsoft SQL Server Management Objects (Version 10 or higher) is installed.  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SMO_NEEDED_FOR_SQL_PROVIDER.

Root Cause: SQL Shared Management Objects (SMO) was not found or the installed version is too old.

Solution: Install the latest version of SMO using Web Platform Installer.

How to install Sitecore 9 on other than System Drive

We decided to not install Sitecore 9 on the System drive but change the installation location. I followed the easy steps mentioned on Artsem Prashkovich blog post.

The only problem I faced is, on his blog there is an option to download the customized JSON file for changing the location. But these files are specific to Sitecore 9.0.0 version. If you download the files and use them on 9.0.1, you will run into issues. As 9.0.1 has two new parameters introduced SqlMessagingUser and SqlMessagingPassword, which were not there on 9.0.0. I mistakenly used these files on 9.0.1 and encountered couple of issues. So make a note of it.

That’s all. Once I was able to iron out all the above issues the install was successful and I am happy. 😊

Hope it helps!!!