Moving Sitecore Security Data Outside of the Core Database

In the older versions of Sitecore, including 9.0, the core database served as the home for the membership and roles tables. Starting with Sitecore 9.1, they have introduced the security database connection string...but if you look, it will point to the core database by default. The reason for the separation is twofold:

• The CD instance doesn't need the entire core database, just the security elements.
• 9.1 introduced the identity server, which needs the same security elements, again separate from the core database functionality.

By adding the security connection string, it allows you to easily create your own authentication layer into the identity server, or however you like, without compromising the core database. But if you want to stick with the default security layers, you can separate out those elements from the core database to its own.

Basic Setup

Sitecore provides a good starting point for this in its documentation: Walkthrough: Moving security data to a separate database. This will get your database created with the tables/procedures and move the existing security entries over from the core database. And it worked, but the documentation is missing one element: the identity server.

Connect Up Identity Server

In your identity server files, look for /Config/production/Sitecore.IdentityServer.Host.xml and open it in a text editor. You'll see the connection string for security in there; you'll need to change that to match your CM/CD entry. And then you'll fire up your site, and...get a 500 error on the identity server. Oops!

Missing Tables

Looking at the identity server logs, I found this error: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'PersistedGrants'. If you look in the core database, you'll see this table, so you'll need to script it out and bring it over to your new security database. That clears up the 500 error, but a further review of the logs shows the DeviceCodes table is also needed. After adding that and trying another login, it appears the log is cleared of SQL errors.

For convenience, here are the scripts to create the needed tables/indexes, just run this in SQL Management Studio in your security database.

/****** Object:  Table [dbo].[PersistedGrants]    Script Date: 2/9/2021 4:19:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PersistedGrants](
    [Key] [nvarchar](200) NOT NULL,
    [Type] [nvarchar](50) NOT NULL,
    [SubjectId] [nvarchar](200) NULL,
    [ClientId] [nvarchar](200) NOT NULL,
    [CreationTime] [datetime2](7) NOT NULL,
    [Expiration] [datetime2](7) NULL,
    [Data] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_PersistedGrants] PRIMARY KEY CLUSTERED 
(
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


SET ANSI_PADDING ON
GO

/****** Object:  Index [IX_PersistedGrants_SubjectId_ClientId_Type]    Script Date: 2/9/2021 4:19:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_PersistedGrants_SubjectId_ClientId_Type] ON [dbo].[PersistedGrants]
(
    [SubjectId] ASC,
    [ClientId] ASC,
    [Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Table [dbo].[DeviceCodes]    Script Date: 2/10/2021 10:16:19 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DeviceCodes](
    [UserCode] [nvarchar](200) NOT NULL,
    [DeviceCode] [nvarchar](200) NOT NULL,
    [SubjectId] [nvarchar](200) NULL,
    [ClientId] [nvarchar](200) NOT NULL,
    [CreationTime] [datetime2](7) NOT NULL,
    [Expiration] [datetime2](7) NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_DeviceCodes] PRIMARY KEY CLUSTERED 
(
    [UserCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [IX_DeviceCodes_DeviceCode]    Script Date: 2/10/2021 10:16:28 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_DeviceCodes_DeviceCode] ON [dbo].[DeviceCodes]
(
    [DeviceCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

/****** Object:  Index [IX_DeviceCodes_Expiration]    Script Date: 2/10/2021 10:16:48 AM ******/
CREATE NONCLUSTERED INDEX [IX_DeviceCodes_Expiration] ON [dbo].[DeviceCodes]
(
    [Expiration] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I've sent this information to Sitecore's documentation folks, so hopefully it'll be included in their scripts in the future, but as of publishing you'll need this information. Hopefully it helps!

Comments

[jack]

Creating high-quality, valuable content that others want to link to naturally is a powerful off-page SEO technique. This can include blog posts, infographics, videos, and more