Experience Sitecore ! | SQL-level access to database in XM Cloud

Could you ever guess that you can still access databases in XM Cloud directly at SQL level? Yes, you can, and below I am showing how exactly.

Disclaimer
The step‑by‑step SQL‑level techniques described in this post are provided strictly for educational purposes. Directly querying or modifying the underlying database of Sitecore XM Cloud is strongly discouraged, as it bypasses critical application‑level safeguards, voids support agreements, and may lead to data corruption, security vulnerabilities, or service outages. Always interact with your XM Cloud instance through the officially supported APIs, Sitecore CLI, or designated administration tools. Proceeding with direct database access is done entirely at your own risk, and the author and any affiliated parties disclaim all liability for any loss, damage, or disruption resulting from such actions.

The key trick here goes from using Sitecore PowerShell Extensions, if you have them enabled at your instance, you're good to go! This applies to both cloud and locally containerized databases. PowerShell has a useful commandlet Invoke-SqlCommand that allows making SQL-level connections and accepts two parameters: $connection and $query:

Invoke-SqlCommand -Connection $connection -Query $query

But how exactly do we get a connection? Luckily, since we operate from within ASP.NET application, we can use its own API to get it, no even needing to look up the connection strings config:

$connection = [Sitecore.Configuration.Settings]::GetConnectionString("master")

The other thing is that we must know the physical name of the database to operate against. Once again, we can calculate that without even a lookup into configs:

$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $connection
$dbName = $builder.InitialCatalog

Please note that on a local, your database name is always Sitecore.Master, while on a cloud instance, it is very long, combined from the org, project, and environment names, thus always varies from environment to environment, so you have to calculate it like that.

Next, let's build a query. To start with we can do a basic select for the items availabe. Having a physical database name, we can pass it into a query as below:

$sql = @"
USE [{0}]
SELECT ID, [Name], [TemplateID], Created from  [dbo].[Items]
"@

With that in mind, let's combine everything together into a single script:

$sql = @"
USE [{0}]
SELECT ID, [Name], [TemplateID], Created from  [dbo].[Items]
"@

Import-Function Invoke-SqlCommand

Write-Verbose "Cleaning up the History, EventQueue, and PublishQueue tables in the $($db.Name) database."
$connection = [Sitecore.Configuration.Settings]::GetConnectionString("master")
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $connection
$dbName = $builder.InitialCatalog
$query = [string]::Format($sql, $dbName)

Invoke-SqlCommand -Connection $connection -Query $query

Running it brings the desired result:

But what is especially amazing is that you also have write access to master database! With that in mind, I create a query that creates a new item under a specific location and also populates some of its fields. It looks slightly more complicated, but again, nothing extraordinary:

$sql = @"
USE [Sitecore.Master];

-- Variables
DECLARE @NewItemId UNIQUEIDENTIFIER = NEWID();
DECLARE @ParentId UNIQUEIDENTIFIER = '{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}';
DECLARE @TemplateId UNIQUEIDENTIFIER = '{76036F5E-CBCE-46D1-AF0A-4143F9B557AA}'; -- Sample Item template
DECLARE @TextFieldId UNIQUEIDENTIFIER = '{A60ACD61-A6DB-4182-8329-C957982CEC74}'; -- Text field ID
DECLARE @Now DATETIME = GETUTCDATE();
DECLARE @ItemName NVARCHAR(255) = 'SQL-inserted item';
DECLARE @Language NVARCHAR(10) = 'en';
DECLARE @Version INT = 1;
DECLARE @TextValue NVARCHAR(MAX) = 'This item was created entirely with SQL INSERT script';

-- 1. Insert into Items
INSERT INTO [dbo].[Items] 
    ([ID], [Name], [TemplateID], [ParentID], [MasterID], [Created], [Updated])
VALUES 
    (@NewItemId, @ItemName, @TemplateId, @ParentId, '00000000-0000-0000-0000-000000000000', @Now, @Now);

-- 2. Insert into VersionedFields (CORRECT field ID now)
INSERT INTO [dbo].[VersionedFields]
    ([ItemId], [FieldId], [Language], [Version], [Value], [Created], [Updated])
VALUES
    (@NewItemId, @TextFieldId, @Language, @Version, @TextValue, @Now, @Now);
"@

Import-Function Invoke-SqlCommand

Write-Verbose "Cleaning up the History, EventQueue, and PublishQueue tables in the $($db.Name) database."
$connection = [Sitecore.Configuration.Settings]::GetConnectionString("master")
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder $connection
$dbName = $builder.InitialCatalog
#$query = [string]::Format($sql, $dbName)
$query = $sql

Invoke-SqlCommand -Connection $connection -Query $query

Upon the execution, I saw no changes in Content Tree, which likely happens due to some CM caches in place. For the sake of clarity, I restarted CM and got this newly created item with all the fields, as expected:

Note, that $name token from standard vales does not expand here - this gets done by Sitecore API upon item creation logic, while we directly inserted into database bypassing it.

That approach is really awesome, despite feeling so hacky! Imagine combining it with the SPE Remoting and/or combining that with AI - it brings unlimited potential.

But once again, it all goes for educational purposes exclusively.

Related posts