Confusing myself with Sitecore's database authentication

Sometimes things you think you know turn out not to be right. I got bitten by this issue recently, and it seemed like something to write down. Because being wrong is fine, as long as you learn something...

The issue

I was looking at a project that my company was taking on, which was set up to use containers for development. Where this was different to other projects I'd worked on was that it included custom databases which were added to the SQL container. That's a scenario that Sitecore's base images support. Your DockerFile can copy your own .dacpac files into the c:\resources folder in the SQL Initialisation image, and on start-up they should get attached in the same way the standard Sitecore ones are. And similarly to the Sitecore DBs, the underlying files for these end up in the data folder mapped for your SQL container.

When I ran docker compose up for the first time (so the SQL container's mapped data folder was empty) this worked fine, and the site started correctly. But if I shut down the containers and started them again (with the SQL data files present in the mapped data folder) it would fail. The site would throw a an exception saying that it could not log in to one of the custom databases:

That error was reliable for these subsequent starts, and if I cleared the mapped data folder it would start once, but then go back to the broken state once there were files present there on start-up.

Digging a bit

Why couldn't it open the DB? Well that answer turned out to be easy: When the containers started for the first time this custom database was present and available. But for second and subsequent starts it wasn't there in the list of databases for the SQL Server when I looked with SQL Management tools. So the error message was a bit misleading - it didn't really mean "can't log in" it meant "not present!" instead. (That's a common issue with SQL Server - it deliberately tries not to give away risky information in its error messages, so they tend to be a bit generic when you're debugging)

Looking at the data folder for the SQL role, the database files (.mdf and .ldf) were present for the missing database when the site was broken, so underlying issue seemed to be that the database had not been attached at start-up. I did some digging at this point, and looked at what appeared in the SQL container's logs while it started up. And what caught my attention was this message at the point it tried to attach the missing database:

2024-02-23 20:57:44 Msg 12824, Level 16, State 1, Server A40EE98587EC, Line 1
2024-02-23 20:57:44 The sp_configure value 'contained database authentication' must be set to 1 in order to create a contained database.  You may need to use RECONFIGURE to set the value_in_use.

					

Well that explained why it wasn't attached. But what was causing that?

I tried using SQL Management Studio to reattach the database, and that failed:

Clicking the relevant link for details also gave a "Contained Authentication" error as above. So I looked at the properties for the server itself, and it showed that this setting was indeed disabled:

So I tried running the following SQL to enable it:

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

					

(You can also make this change in the properties dialog for your database server shown above, if you prefer)

And having done that, trying to reattach the database worked and refreshing the website got rid of the error and ran OK. So clearly the contained authentication was the problem...

But this confused me. My experience of working with Sitecore outside of containers was that all the databases apart from the xConnect collection shards, have contained users. That was definitely the pattern in PaaS / IaaS, because I've had to use exactly the SQL commands above to prepare an IaaS SQL Server for a Sitecore install in the past.

Flailing about for an answer

So at this point I went diving down a rabbit hole of trying to work out why the Contained Authentication setting was failing.

I looked at the state of the Contained Authentication setting at various states in the start-up process. I noted that on first start (with an empty data folder) SQL Server was indeed configured to allow contained auth. But when I restarted it, it was disabled again. That seemed to fit with the behaviour I was seeing: The custom database attached ok first time because the setting was on, but failed the second time because it was off.

And with a bit of spelunking through the container data I could see in the PowerShell scripts of the init container that it did in fact enable Contained Authentication when attaching databases. If you look in the DeployDatabases.ps1 script that is called by the SQL init's entrypoint when there are no databases in the data folder, you'll see:

if($EnableContainedDatabaseAuth) {
    $sqlcmd = Add-SqlAzureConditionWrapper -SqlQuery "DECLARE @containedAuthenticationEnabled int = CONVERT(int, (SELECT [value] FROM sys.configurations WHERE name = 'contained database authentication'));
    IF @containedAuthenticationEnabled = 0
    BEGIN
        EXEC sys.sp_configure N'contained database authentication', N'1'
        exec ('RECONFIGURE WITH OVERRIDE')
    END"

    Invoke-Sqlcmd -SqlServer:$SqlServer -SqlAdminUser:$SqlAdminUser -SqlAdminPassword:$SqlAdminPassword -Query $sqlcmd
    if($LASTEXITCODE -ne 0) {
        throw "sqlcmd exited with code $LASTEXITCODE"
    }
    Write-Verbose "Enabled contained databases"
}

					

And that is indeed enabling the setting.

I sat at the bottom of my rabbit hole of thought for a while, wondering why the Sitecore databases all worked when this setting was clearly not persisted in the SQL Server container, but this one custom database did not...

The solution

As is often the case, I came up with the answer after walking away from the problem for a while. After asking about this on the weekly Sitecore Lunch zoom call, I went off to cook some dinner and relax for a bit in front of the TV.

And when I came back to it, I figured I should check Sitecore's databases and verify my assumption that they did require Contained Authentication. And when I looked at the core and master databases I saw this:

They do not have Contained Auth turned on! My working assumptions on this issue were wrong! Container-based Sitecore instances are set up differently to PaaS/IaaS ones.

So that lead me to deciding that the underlying problem was really that the copy of this custom database I'd been given had been exported with Contained Authentication turned on, but I needed this turned off for everything to work.

You can check if this setting is in place if you look inside the .dacpac file. They're zips internally, so if you change their extension you can open them up and inside you'll find a file named model.xml. If that includes an XML <Property/> element for Containment set to 1 then Contained Authentication is required. Set to 0 or missing and it's not required:

<?xml version="1.0" encoding="utf-8"?>
<DataSchemaModel FileFormatVersion="1.2" SchemaVersion="2.9" DspName="Microsoft.Data.Tools.Schema.Sql.Sql140DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False" xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">
	<Model>
		<Element Type="SqlDatabaseOptions" Disambiguator="1">
			<Property Name="Collation" Value="SQL_Latin1_General_CP1_CI_AS" />
			<Property Name="IsAnsiPaddingOn" Value="False" />
			<Property Name="IsQuotedIdentifierOn" Value="False" />
			<Property Name="IsCursorDefaultScopeGlobal" Value="True" />
			<Property Name="IsTornPageProtectionOn" Value="False" />
			<Property Name="IsFullTextEnabled" Value="True" />
			<Property Name="IsReadCommittedSnapshot" Value="True" />
			<Property Name="Containment" Value="1" />
			<Property Name="DefaultLanguage" Value="us_english" />
			<Property Name="DefaultFullTextLanguage" Value="us_english" />
			<Property Name="TargetRecoveryTimePeriod" Value="60" />
			<Property Name="QueryStoreDesiredState" Value="2" />
			<Property Name="QueryStoreCaptureMode" Value="2" />
			<Property Name="QueryStoreMaxStorageSize" Value="1000" />
			<Relationship Name="DefaultFilegroup">
				<Entry>
					<References ExternalSource="BuiltIns" Name="[PRIMARY]" />
				</Entry>
			</Relationship>
		</Element>
... snip ...
	</Model>
</DataSchemaModel>

					

Fixing this

So the overall solution is "don't have Contained Authentication enabled for your custom databases in a Sitecore container solution!"

Easy, huh? The steps for fixing this on my development instance were:

• Enable Contained Authentication on the SQL instance.
• Attach the problematic database.
• Check if the database has any users defined in it, and if so delete them from the database.
• Change the database's Contained Authentication setting to "None".
• Turn Contained Authentication back off for the SQL instance to verify the previous change was ok.
• Export the .dacpac file of the database from this state.

That seems like a few steps - and you might think that just editing the data in the .dacpac would be quicker and simpler here. But that approach doesn't work. SQL Server checksums the data in the model in the Origin.xml file:

<Checksums>
    <Checksum Uri="/model.xml">9D3B45E9FDE300019AEDDF6F82FCC15247D04581887E17D3194BA7A5C7C2B149</Checksum>
</Checksums>

					

And the code verifies this hash of the data when importing it. So if you edit the model data, the .dacpac install will raise errors.

But with the change made "the right way", I could replace the problematic .dacpac file in the Docker build folder for the SQL Init image, empty the SQL Server data folder and re-run docker compose build. With that done, docker compose up works reliably, and I can get back to productive work.

Problem solved!

↑ Back to top