Moving Sitecore Security Data Outside of the Core Database
Tags: sitecore
Published: 2021-02-10 15:36:40

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](https://doc.sitecore.com/developers/100/platform-administration-and-architecture/en/walkthrough--moving-security-data-to-a-separate-database.html). 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!