AzureSQL- Could not find stored procedure 'sp_configure' while installing Sitecore9.1

3
votes

I am installing Sitecore9.1 with powershell using Azure SQL server and databases. While running script, it creates databases and while creating/updating MarketingAutomation databases it is giving error about 'contained database authentication'

error is - The command started with the following: "exec sp_configure 'contained database authenticati" Could not find stored procedure 'sp_configure'. Install-SitecoreConfiguration : Command C:\Program Files\iis\Microsoft Web Deploy V3\msdeploy.exe returned a non-zero exit code - (-1)

I am running below command to Azure SQL server for this issue:

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

but while executing it throws error about syntax.

I could not find commands for AzureSQL or SQL2017 for contained database authentication.

I want commands for contained database authentication for Azure SQL.Please help.

1
sql-serverazure-sql-databasewebdeploysitecore9
As per sp_configure (Transact-SQL), the SP is only available on Managed Instances; I'm guessing yours is not? - Larnu
contained database authentication is enabled by default in Azure SQL database - there is no command to turn it on or off - Martin Smith

1 Answers

1
votes

sp_configure is not available on Azure SQL Database and you should use ALTER DATABASE SCOPED CONFIGURATION instead to configure Azure SQL Database options and parameters.

The following query should tell which databases have contained database authentication enabled.

select [name] as databasename, containment, containment_desc  from   sys.databases

As you can read on this documentation, the containment value of zero applies to Azure SQL Database, but value1 cannot be set on Azure SQL Database. However you can create contained database users on Azure SQL Database without the need to enable containment, and the contained database user model is the recommended model instead of the traditional connection model as mentioned here.