Sitecore 9.1 update 1 installation error with Azure SQL database

Question

I'm trying to install Sitecore 9.1 update 1 with `Azure SQL database as a service, I'm getting below error. Please help.

> [---------------------------------- XConnectXP0_InstallWDP : WebDeploy
> -----------------------------------------------] [WebDeploy]:[Path] C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe msdeploy.exe
> : Error Code: ERROR_SQL_EXECUTION_FAILURE At C:\Program
> Files\WindowsPowerShell\Modules\SitecoreInstallFramework\2.1.0\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: "1" and "3". The
> verbose log might have more information about the error. The command
> started  with the following: "e xec sp_configure 'contained database
> authenticati"  Could not find stored procedure 'sp_configure'.
> http://go.microsoft.com/fwlink/?LinkId=178587  Learn more at: 
> http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE.
> 
> Error: Could not find stored procedure 'sp_configure'.
> 
> Error count: 1.
> 
> Install-SitecoreConfiguration : Command C:\Program Files\iis\Microsoft
> Web Deploy V3\msdeploy.exe returned a non-zero  exit code - (-1) At
> C:\Source Files\XP0 Configuration files 9.1.1 rev.
> 002459\XP0-SingleDeveloper-non-signed.ps1:74 char:1
> + Install-SitecoreConfiguration @singleDeveloperParams *>&1 | Tee-Objec ...
> + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>     + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
>     + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Install-SitecoreConfiguration
> [TIME] 00:15:15 

Answer 1

I don't want this to be a link only answer, but I have several IaaS installs using Azure SQL as the database. You need to extract the user creation SQL scripts from the SIF scwsp files, modify them and then repackage the scwsp files.

You can follow the steps here to repackage the sql scripts you need for Azure SQL.

http://www.chrissulham.com/modify-sitecore-install-framework-packages-for-azure-sql/

Also in the xconnect-xp0.json file, you need to change Sharding.DacPac.Path to point at azure file and not the normal SQL path.

Sitecore.Xdb.Collection.Database.Sql.dacpac

becomes

Sitecore.Xdb.Collection.Database.Sql.Azure.dacpac

The full like is:

"Sharding.DacPac.Path": "[joinpath(variable('Sharding.Root.Path'), 'Sitecore.Xdb.Collection.Database.Sql.dacpac')]",

to

"Sharding.DacPac.Path": "[joinpath(variable('Sharding.Root.Path'), 'Sitecore.Xdb.Collection.Database.Sql.Azure.dacpac')]",

Answer 2

sp_configure is only available on managed instance, it's not available on Azure SQL Database.

To fix this issue try creating contained database users on Azure SQL Database.

Answer 3

Thanks @Peter. We can not execute sp_configure with Contained Database option in current Azure SQL Database.

Approach of Deployment should be

1. Full on Premise Deployment
2. IaaS (Infrastructure As A Service)
3. Sitecore in Azure App Service. (PaaS)