How to Manage Azure SQL Databases for Sitecore 9.3

We've worked with Sitecore for 15+ years at Velir, so we're well-versed in the Sitecore upgrade process. But with upgrades involving Azure, we've noticed that Sitecore's documentation has some gaps. It's missing pieces about custom Sitecore implementation and installation.

To address these gaps, we wrote about the basics of Sitecore 9.3 architecture and Azure installation and how to manage Solr for anyone trying to tweak an out-of-the-box Sitecore setup. To fill in more documentation gaps, we’re also sharing the steps for managing Azure SQL databases while upgrading to Sitecore 9.3.

Restoring Azure Databases

During the process of backing up and restoring databases from local instances to Azure, you'll discover that the new .bacpac file structure no longer stores credentials or permissions. In some cases, you must remove the parts that carry over to replace it properly.

Restoring the Shard databases isn't recommended because they store URLs from the system they were created from. When xConnect tries to start, it uses the context URL to look up data and will fail if the context domain doesn’t match what’s stored in the database. If you must restore the Shard databases, then you’ll need to know how to reset them first.

To successfully restore all the databases, you need to know the permissions they require and how to add them. Another thing to note is that the latest version of SQL separates a ‘Login’ where a username is tied to a password from a ‘User’ where a username is tied to a set of permissions. Here’s how to get set up:

Creating Database Logins

CREATE LOGIN collectionuser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN coreuser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN exmmasteruser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN formsuser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN marketingautomationuser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN masteruser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN messaginguser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN processingengineuser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN poolsuser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN referencedatauser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN reportinguser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN tasksuser WITH PASSWORD = 'SomeUniqueValue'
CREATE LOGIN webuser WITH PASSWORD = 'SomeUniqueValue'

Adding Database Permissions

Core

CREATE USER coreuser
FOR LOGIN coreuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [coreuser]
ALTER ROLE db_datawriter ADD MEMBER [coreuser]
ALTER ROLE aspnet_Membership_BasicAccess ADD MEMBER [coreuser]
ALTER ROLE aspnet_Membership_FullAccess ADD MEMBER [coreuser]
ALTER ROLE aspnet_Membership_ReportingAccess ADD MEMBER [coreuser]
ALTER ROLE aspnet_Profile_BasicAccess ADD MEMBER [coreuser]
ALTER ROLE aspnet_Profile_FullAccess ADD MEMBER [coreuser]
ALTER ROLE aspnet_Profile_ReportingAccess ADD MEMBER [coreuser]
ALTER ROLE aspnet_Roles_BasicAccess ADD MEMBER [coreuser]
ALTER ROLE aspnet_Roles_FullAccess ADD MEMBER [coreuser]
ALTER ROLE aspnet_Roles_ReportingAccess ADD MEMBER [coreuser]

GRANT EXECUTE TO [coreuser];

EXM

CREATE USER exmmasteruser
FOR LOGIN exmmasteruser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [exmmasteruser]
ALTER ROLE db_datawriter ADD MEMBER [exmmasteruser] 

Forms

CREATE USER formsuser
FOR LOGIN formsuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [formsuser]
ALTER ROLE db_datawriter ADD MEMBER [formsuser] 

Marketing Automation

CREATE USER marketingautomationuser
FOR LOGIN marketingautomationuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [marketingautomationuser]
ALTER ROLE db_datawriter ADD MEMBER [marketingautomationuser] 

GRANT EXECUTE TO [marketingautomationuser]; 

Master

CREATE USER masteruser
FOR LOGIN masteruser
WITH DEFAULT_SCHEMA = dbo
ALTER ROLE db_datareader ADD MEMBER [masteruser]
ALTER ROLE db_datawriter ADD MEMBER [masteruser] 

Messaging

CREATE USER messaginguser
FOR LOGIN messaginguser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [messaginguser]
ALTER ROLE db_datawriter ADD MEMBER [messaginguser]

Processing Engine Storage

CREATE USER processingengineuser
FOR LOGIN processingengineuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [processingengineuser]
ALTER ROLE db_datawriter ADD MEMBER [processingengineuser] 

GRANT EXECUTE TO [processingengineuser]; 

Processing Engine Tasks

CREATE USER processingengineuser
FOR LOGIN processingengineuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [processingengineuser]
ALTER ROLE db_datawriter ADD MEMBER [processingengineuser] 

GRANT EXECUTE TO [processingengineuser]; 

Processing Pools

CREATE USER poolsuser
FOR LOGIN poolsuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [poolsuser]
ALTER ROLE db_datawriter ADD MEMBER [poolsuser] 

GRANT EXECUTE TO [poolsuser]; 

Processing Tasks

CREATE USER tasksuser
FOR LOGIN tasksuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [tasksuser]
ALTER ROLE db_datawriter ADD MEMBER [tasksuser] 

GRANT EXECUTE TO [tasksuser]; 

Reference Data

CREATE USER referencedatauser
FOR LOGIN referencedatauser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [referencedatauser]
ALTER ROLE db_datawriter ADD MEMBER [referencedatauser] 

GRANT EXECUTE TO [referencedatauser];

Reporting

CREATE USER reportinguser
FOR LOGIN reportinguser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [reportinguser]
ALTER ROLE db_datawriter ADD MEMBER [reportinguser] 

GRANT EXECUTE TO [reportinguser]; 

Web

CREATE USER webuser
FOR LOGIN webuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [webuser]
ALTER ROLE db_datawriter ADD MEMBER [webuser] 

xDB Shard0

CREATE USER collectionuser
FOR LOGIN collectionuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [collectionuser]
ALTER ROLE db_datawriter ADD MEMBER [collectionuser] 

xDB Shard1

CREATE USER collectionuser
FOR LOGIN collectionuser
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [collectionuser]
ALTER ROLE db_datawriter ADD MEMBER [collectionuser] 

xDB Shard Map Manager

CREATE USER collectionuser
FOR LOGIN collectionuser 
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER [collectionuser]
ALTER ROLE db_datawriter ADD MEMBER [collectionuser] 

GRANT EXECUTE TO [collectionuser] 

Sitecore has put a lot of work into making its platform into a set of scalable microservices. So, it’s important to understand the new architecture and which parts interact together. Hopefully, this article and our other recent pieces on the basics of Sitecore 9.3 architecture and Azure installation and how to manage Solr will make it easier to grasp. Our goal with these articles is to help you efficiently deploy in Azure and get the most out of a custom Sitecore installation when you upgrade it.

Learn more about our Sitecore expertise or contact us to learn how we can help with your next Sitecore upgrade or project.