Installing Sitecore using the Sitecore Install Framework (SIF) is generally a streamlined process, but it can occasionally hit a roadblock when database configurations don't align with the expected state. One of the most frustrating errors you might encounter, particularly with Sitecore 9.1, is the SQL containment error. This typically manifests as: 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.
This error is particularly common after a failed installation attempt. Even if you think you have cleared the slate, residual database artifacts can prevent SIF from correctly configuring the database containment settings required for a successful Sitecore deployment. In this guide, we will walk through why this happens and how to thoroughly clean your environment to ensure a smooth installation.
Understanding the SQL Containment Property Error
Sitecore 9.1 and later versions utilize Contained Databases in SQL Server. A contained database includes all database settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed.
When SIF runs, it attempts to set the containment property of the databases. If a previous installation failed, SIF might find an existing database or a user associated with that database that prevents it from switching the containment level. SQL Server throws the "Msg 12809" error because you cannot move a database to a non-contained state (or vice versa) while specific users with passwords exist that are tied to the containment logic.
Method 1: The SIF Native Uninstall Approach
The most reliable way to clean up after a failed SIF installation is to use SIF itself. Many developers simply delete the IIS sites and the databases manually, but this often misses underlying SQL users or certificates.
If you are using the standard installation scripts provided by Sitecore, you should have an installation script and an uninstallation script. Instead of just running the install, try running the Uninstall-SitecoreConfiguration command using your existing configuration files. This ensures that the framework attempts to reverse every step it took, including the removal of SQL users and database entries.
# Example of triggering the uninstall process
Uninstall-SitecoreConfiguration -Path .\Your-Install-Script.json
By leveraging the uninstallation task, you ensure that the cleanup logic matches the installation logic, reducing the risk of orphaned SQL users.
Method 2: Automated SQL Cleanup via PowerShell
If the standard SIF uninstallation fails or if you have already manually deleted some components, you may need a more aggressive cleanup script. The following PowerShell script is designed to find all databases matching a specific prefix (e.g., "Sitecore91") and forcefully remove them along with their connections.
$modName = "SqlServer"
$serverName = "YOUR_SERVER_NAME"
$pw = "YOUR_SA_PASSWORD"
$dbPrefix = "sitecore" # The prefix used in your SIF config
$foundMod = get-module -name $modName
if ($foundMod) {
write-host "Module $modName is already imported" -ForegroundColor Green
}
else {
write-host "Importing Module $modName" -ForegroundColor Yellow
import-module $modName
}
$DBListQuery = "select * from sys.databases where Name like '$($dbPrefix)_%';"
write-host "Searching for databases... $DBListQuery"
$DBList = invoke-sqlcmd -ServerInstance "$serverName" -U "sa" -P $pw -Encrypt Optional -TrustServerCertificate -Query $DBListQuery
if ($DBList) {
ForEach($DB in $DBList) {
# Set to single user to kill existing connections, then drop
$AlterQuery = "ALTER DATABASE [$($DB.Name)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;`r`nDROP DATABASE [$($DB.Name)];"
Write-host -foregroundcolor Green "Deleting Database $($DB.Name) ... script:`r`n$AlterQuery `r`n"
invoke-sqlcmd -ServerInstance "$serverName" -U "sa" -P "$pw" -Encrypt Optional -TrustServerCertificate -Query $AlterQuery -Verbose
}
}
else {
write-host "No databases found on $serverName with prefix $dbPrefix" -ForegroundColor Green
}
This script is highly effective because it sets the database to SINGLE_USER mode with ROLLBACK IMMEDIATE, which terminates any lingering connections that might be locking the database files.
Method 3: Manual Verification in SSMS
Sometimes, scripts alone aren't enough. If you are still seeing the error after running cleanup scripts, open SQL Server Management Studio (SSMS) and perform these checks:
- Check for Duplicate Prefixes: Ensure you don't have databases that differ only by capitalization. While SQL Server is often case-insensitive, SIF scripts and DACPAC deployments can sometimes behave unexpectedly if multiple databases with similar names exist.
- Verify Server-Level Logins: Navigate to
Security > Logins. Look for users that SIF created (usually matching your database prefix). If the database is gone but the login remains, the next SIF run might fail when trying to create that user or map it to a new database. - Contained Database Authentication: Ensure that your SQL Server instance allows contained databases. You can verify this by running:
sql sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'contained database authentication', 1; RECONFIGURE;
Frequently Asked Questions
Why does this error only happen on the second or third install attempt?
This error is almost always a result of "leftovers." On the first attempt, the SQL instance is clean. If the installation fails midway, SIF may have already created the database and added users. Because the database is in a partially configured state, subsequent attempts to modify the containment property fail because those users already exist.
Do I need to delete the SQL Server Logins manually?
Yes, it is highly recommended. SIF creates SQL Logins at the server level to interact with the databases. If you only delete the database, the Login remains. When SIF tries to recreate the environment, it may encounter conflicts with the existing Login's SID (Security Identifier).
Can I just change the containment property manually in SSMS?
While you can change the containment property in the database properties window in SSMS, it is better to let SIF handle it. If you are seeing this error, it's a sign that your environment is out of sync. It is always safer to delete the problematic databases and start the SIF process fresh.
Wrapping Up
The "You must remove all users with password before setting the containment property to NONE" error is a classic sign of a dirty environment. By using a combination of Uninstall-SitecoreConfiguration, custom PowerShell cleanup scripts, and manual verification in SSMS, you can clear the path for a successful installation. Always remember to back up any non-Sitecore databases on the same instance before running bulk deletion scripts, and ensure your SQL Server is properly configured for contained database authentication before starting.
