ERROR_SQL_EXECUTION_FAILURE | While installing second instance of Sitecore XP 9.0.2

Question

On my development machine, I had installed Sitecore 9.0.2 months ago and now, for POC purposes, I am trying to install the second instance of Sitecore 9.0.2. While doing that I am facing a strange issue, where-in SQL execution is failing whilst creating marketing automation DB. Here is complete stack trace (snippet from the SIF log file): How can it be solved?

> msdeploy.exe : Error Code: ERROR_SQL_EXECUTION_FAILURE At C:\Program
> Files\WindowsPowerShell\Modules\SitecoreInstallFramework\1.2.1\Public\Tasks\Invoke-CommandTask.ps1:31
> char:13
> +             & $Path $Arguments | Out-Default
> +             ~~~~~~~~~~~~~~~~~~
>     + CategoryInfo          : NotSpecified: (Error Code: ERROR_SQL_EXECUTION_FAILURE:String) [], RemoteException
>     + FullyQualifiedErrorId : NativeCommandError msdeploy.exe : Error Code: ERROR_SQL_EXECUTION_FAILURE At C:\Program
> Files\WindowsPowerShell\Modules\SitecoreInstallFramework\1.2.1\Public\Tasks\Invoke-CommandTask.ps1:31
> char:13
> +             & $Path $Arguments | Out-Default
> +             ~~~~~~~~~~~~~~~~~~
>     + CategoryInfo          : NotSpecified: (Error Code: ERROR_SQL_EXECUTION_FAILURE:String) [], RemoteException
>     + FullyQualifiedErrorId : NativeCommandError
> 
> More Information: An error occurred during execution of the database
> script. The error occurred between the following  lines of the script:
> "12" and "16". The verbose log might have more information about the
> error. The command started  with the following: More Information: An
> error occurred during execution of the database script. The error
> occurred between the following lines of the script: "12" and "16". The
> verbose log might have more information about the error. The command
> started with the following:
> 
> "EXEC sp_addrolemember 'db_datareader', [marketin"  Execution Timeout
> Expired.  The timeout period elapsed prior to completion of the
> operation or the server is not  responding.
> http://go.microsoft.com/fwlink/?LinkId=178587  Learn more at: 
> http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE.
> "EXEC sp_addrolemember 'db_datareader', [marketin"  Execution Timeout
> Expired.  The timeout period elapsed prior to completion of the
> operation or the server is not responding.
> http://go.microsoft.com/fwlink/?LinkId=178587  Learn more at:
> http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE.
> 
> Error: Execution Timeout Expired.  The timeout period elapsed prior to
> completion of the operation or the server is not  responding. Error:
> Execution Timeout Expired.  The timeout period elapsed prior to
> completion of the operation or the server is not responding.
> 
> Error: The wait operation timed out Error: The wait operation timed
> out
> 
> Error count: 1. Error count: 1.
> 
> ********************** Command start time: 20180918054113
> ********************** PS>TerminatingError(Invoke-CommandTask): "The running command stopped because the preference variable
> "ErrorActionPreference" or common parameter is set to Stop: Command
> C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe returned a
> non-zero exit code - (-1)"
> >> TerminatingError(Invoke-CommandTask): "The running command stopped because the preference variable "ErrorActionPreference" or common
> parameter is set to Stop: Command C:\Program Files\iis\Microsoft Web
> Deploy V3\msdeploy.exe returned a non-zero exit code - (-1)"
> >> TerminatingError(Invoke-CommandTask): "The running command stopped because the preference variable "ErrorActionPreference" or common
> parameter is set to Stop: Command C:\Program Files\iis\Microsoft Web
> Deploy V3\msdeploy.exe returned a non-zero exit code - (-1)"
> Install-SitecoreConfiguration : Command C:\Program Files\iis\Microsoft
> Web Deploy V3\msdeploy.exe returned a non-zero  exit code - (-1) At
> D:\Sitecore SCG\Sitecore91XP\install_sitecore.ps1:50 char:1
> + Install-SitecoreConfiguration @xconnectParams
> + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>     + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
>     + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Install-SitecoreConfiguration
> Install-SitecoreConfiguration : Command C:\Program Files\iis\Microsoft
> Web Deploy V3\msdeploy.exe returned a non-zero exit code - (-1) At
> D:\Sitecore SCG\Sitecore91XP\install_sitecore.ps1:50 char:1
> + Install-SitecoreConfiguration @xconnectParams
> + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>     + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
>     + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Install-SitecoreConfiguration

Answer 1

Thanks, everyone for your pointers. The issue was occurring, because SIF was trying to create duplicate DB users.

I narrowed down the issue with the SIF parameter configuration. It seems that SIF doesn't anticipate 2 Sitecore instances DBs over one SQL server and hence DB user names are hardcoded.

SIF PowerShell scripts package comes with a set of parameters - ranging from site names to Db users. SIF PowerShell script (named install_sitecore.ps1) allows to dynamically set some of the parameters, but most of the parameters are hardcoded in the {SIF root folder}\sitecore-XP0.json file.

Refer the snippet below:

If you install second instances of the Sitecore 9.x over the same SQL Server instances, it doesn't allow 2 DB users with the same name and throws the ERROR_SQL_EXECUTION_FAILURE as mentioned above.

I hope it helps.

Answer 2

Please try unblocking the Microsoft.Web.XmlTransform.dll. Normally you get such kind of errors when you extract the SIF zip files without doing unblock before the extract.

I did got something same with error but with the unblock and re-execution everything worked fine.

Answer 3

I got similar error during installation of Sitecore 9.1. My previous installation attempts were unsuccessful due to other errors. While retrying the installation after fixing the earlier errors, I got similar error. I retried the installation by updating the value for Prefix to a new unique value in my XP0-SingleDeveloper.ps1 and the installation script ran successfully.

Answer 4

Just in case it helps anyone else: I had the exact same error message installing 9.2. I ran the SQL commands mentioned in the error manually and saw that it was taking quite a while (36 seconds) to execute, so killed pretty much everything except SIF and ran it again (successfully).

Answer 5

For me the solution was to remove everything that the previous run tried to install (IIS Sites and App Pools, Host file entries, databases, Solr Cores), then unblock files before running again:

PS C:\resourcefiles> dir C:\resourcefiles\*.* | Unblock-file

Answer 6

Tried all the above with no luck. This is definitely a SQL time out the issue - So I have increased the remote query time out which did not help either.

On my local machine, I have noticed multiple instances of SQL server, one of which is no use for me. Deleted to see if it helps with the SQL time out.

Magically it worked! Not sure how this is related to the actual issue.