Sharing common database between .Net core and .Net standard Identity framework

Question

I have old legacy MVC app that uses Identity 2.0. Also recently I have created .Net 6 API with Identity 6.0 (AspNetCore.Identity.EntityFrameworkCore).

Note: There are some schema related changes in both of this framework, like some additional tables and fields. check snapshot that I added at last.

The issue is the common database that I have to manage.

I have tried and successfully completed updation of existing database with latest schema.
I have also migrated the data for the identity tables.
The only issue I face right now is I'm not sure how to support old legacy app going further. (I want to have old app as it is, but want it to use new identity tables.)
Since the exisiting/old app is in .Net framework 4.6.1, I can not really update identity and it's tables to have latest schema in it. (I don't mind doing any hack or any fix. old app should work with latest idenitty schema is what imp for me at the moment.)

Is there any option that I can follow without changing code much, I have tight deadline to follow and I don't really want to create external API to authenticate old legacy app. Any help or idea would be really appreciated.

user_5816119 · Answer

I didn't find any way to update existing app (.Net standard 4.6) to support new identity structure. So here is what I did to resolve this issue.

As #Jeremy suggested in comments, I configured my .Net core Identity models to have different names (other then by default, so it does not have any conflict with existing tables) https://docs.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-6.0#change-tablecolumn-names-and-facets (keep order of model configuration as it is as given in thread. I got issues when I kept my base.OnModelCreating(builder); below builder configuration.)
I have found a script to create identity tables schema and move records. see below screenshot. _New represents the .Net core identity tables scehma. I have attached that script here with and I have run this script in common database.

So now I have common database with both old and new identity tables. old application is still pointing and working well with old tables which .Net core identity framework is poiting to new database.
Since I have moved all the data and records from old tables to new table, I can access them in new idenity app too.
Till now I have no need to run migration in new app, so I disabled it. but there was concern of what if I run migration and how will it affect existing database. so I did some R&D and found that we can create migration and before running that migration, we can just comment out the code inside the up and down method. after that we can run this migration and update database. basically empty migration to keep in sync with existing database. (haven't checked this working but this was my idea if I needed it)

Here is the sql Script that I used.

STEP 1 : Change name of existing tables

EXEC sp_rename 'AspNetRoles', 'AspNetRoles_old';
EXEC sp_rename 'AspNetUserClaims', 'AspNetUserClaims_old';
EXEC sp_rename 'AspNetUserLogins', 'AspNetUserLogins_old';
EXEC sp_rename 'AspNetUserRoles', 'AspNetUserRoles_old';
EXEC sp_rename 'AspNetUsers', 'AspNetUsers_old';

STEP 2 : Create ASP.NET Core Identity tables

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetRoleClaims](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RoleId] [nvarchar](450) NOT NULL,
[ClaimType] [nvarchar](max) NULL,
[ClaimValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_AspNetRoleClaims] PRIMARY KEY CLUSTERED
(
[Id] 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_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetRoles](
[Id] [nvarchar](450) NOT NULL,
[Name] [nvarchar](256) NULL,
[NormalizedName] [nvarchar](256) NULL,
[ConcurrencyStamp] [nvarchar](max) NULL,
 CONSTRAINT [PK_AspNetRoles] PRIMARY KEY CLUSTERED
(
[Id] 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_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserClaims](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [nvarchar](450) NOT NULL,
[ClaimType] [nvarchar](max) NULL,
[ClaimValue] [nvarchar](max) NULL,
 CONSTRAINT [PK_AspNetUserClaims] PRIMARY KEY CLUSTERED
(
[Id] 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_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserLogins](
[LoginProvider] [nvarchar](128) NOT NULL,
[ProviderKey] [nvarchar](128) NOT NULL,
[ProviderDisplayName] [nvarchar](max) NULL,
[UserId] [nvarchar](450) NOT NULL,
 CONSTRAINT [PK_AspNetUserLogins] PRIMARY KEY CLUSTERED
(
[LoginProvider] ASC,
[ProviderKey] 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_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserRoles](
[UserId] [nvarchar](450) NOT NULL,
[RoleId] [nvarchar](450) NOT NULL,
 CONSTRAINT [PK_AspNetUserRoles] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUsers](
[Id] [nvarchar](450) NOT NULL,
[UserName] [nvarchar](256) NULL,
[NormalizedUserName] [nvarchar](256) NULL,
[Email] [nvarchar](256) NULL,
[NormalizedEmail] [nvarchar](256) NULL,
[EmailConfirmed] [bit] NOT NULL,
[PasswordHash] [nvarchar](max) NULL,
[SecurityStamp] [nvarchar](max) NULL,
[ConcurrencyStamp] [nvarchar](max) NULL,
[PhoneNumber] [nvarchar](max) NULL,
[PhoneNumberConfirmed] [bit] NOT NULL,
[TwoFactorEnabled] [bit] NOT NULL,
[LockoutEnd] [datetimeoffset](7) NULL,
[LockoutEnabled] [bit] NOT NULL,
[AccessFailedCount] [int] NOT NULL,
 CONSTRAINT [PK_AspNetUsers] PRIMARY KEY CLUSTERED
(
[Id] 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_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserTokens](
[UserId] [nvarchar](450) NOT NULL,
[LoginProvider] [nvarchar](128) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[Value] [nvarchar](max) NULL,
 CONSTRAINT [PK_AspNetUserTokens] PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[LoginProvider] ASC,
[Name] 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


CREATE NONCLUSTERED INDEX [IX_AspNetRoleClaims_RoleId] ON [dbo].[AspNetRoleClaims]
(
[RoleId] 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


CREATE UNIQUE NONCLUSTERED INDEX [RoleNameIndex] ON [dbo].[AspNetRoles]
(
[NormalizedName] ASC
)
WHERE ([NormalizedName] IS NOT NULL)
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
SET ANSI_PADDING ON
GO


CREATE NONCLUSTERED INDEX [IX_AspNetUserClaims_UserId] ON [dbo].[AspNetUserClaims]
(
[UserId] 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


CREATE NONCLUSTERED INDEX [IX_AspNetUserLogins_UserId] ON [dbo].[AspNetUserLogins]
(
[UserId] 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


CREATE NONCLUSTERED INDEX [IX_AspNetUserRoles_RoleId] ON [dbo].[AspNetUserRoles]
(
[RoleId] 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


CREATE NONCLUSTERED INDEX [EmailIndex] ON [dbo].[AspNetUsers]
(
[NormalizedEmail] 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


CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex] ON [dbo].[AspNetUsers]
(
[NormalizedUserName] ASC
)
WHERE ([NormalizedUserName] IS NOT NULL)
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
ALTER TABLE [dbo].[AspNetRoleClaims]  WITH CHECK ADD  CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetRoleClaims] CHECK CONSTRAINT [FK_AspNetRoleClaims_AspNetRoles_RoleId]
GO
ALTER TABLE [dbo].[AspNetUserClaims]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_AspNetUserClaims_AspNetUsers_UserId]
GO
ALTER TABLE [dbo].[AspNetUserLogins]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_AspNetUserLogins_AspNetUsers_UserId]
GO
ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_AspNetUserRoles_AspNetRoles_RoleId]
GO
ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_AspNetUserRoles_AspNetUsers_UserId]
GO
ALTER TABLE [dbo].[AspNetUserTokens]  WITH CHECK ADD  CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserTokens] CHECK CONSTRAINT [FK_AspNetUserTokens_AspNetUsers_UserId]
GO

STEP 3 : Migrate data from old tables (ASP.NET Identity) to new tables (ASP.NET Core Identity)

INSERT INTO AspNetRoles ([Id], [Name], [NormalizedName], [ConcurrencyStamp])
SELECT [Id], [Name], UPPER([Name]), LOWER(NEWID()) FROM Objectra_Development.dbo.AspNetRoles

INSERT INTO AspNetUsers ([Id], [UserName], [NormalizedUserName], [Email], [NormalizedEmail], [EmailConfirmed], [PasswordHash], [SecurityStamp], [ConcurrencyStamp], [PhoneNumber], [PhoneNumberConfirmed], [TwoFactorEnabled], [LockoutEnd], [LockoutEnabled], [AccessFailedCount], 
AssignObjects, AccessOwnObjectsOnly, UserObjectId, UserObjectDefd, GoogleAuth, LastPasswordResetDate, FriendlyName, AccessibleObjectDefinitions)
SELECT [Id], [UserName], UPPER([UserName]), [Email], UPPER([Email]), [EmailConfirmed], [PasswordHash], [SecurityStamp], LOWER(NEWID()), [PhoneNumber], [PhoneNumberConfirmed], 0, null, 1, 0, 
AssignObjects, AccessOwnObjectsOnly, UserObjectId, UserObjectDefd, GoogleAuth, LastPasswordResetDate, FriendlyName, AccessibleObjectDefinitions
FROM Objectra_Development.dbo.AspNetUsers;

INSERT INTO AspNetUserRoles ([UserId], [RoleId])
SELECT [UserId], [RoleId] 
FROM Objectra_Development.dbo.AspNetUserRoles;

user_5455124 · Answer

I faced the same problem and ended up creating new identity core tables using a schema.

builder.ToTable(name: "Users", "Identity");
builder.ToTable(name: "Roles", "Identity");
builder.ToTable(name: "UserRoles", "Identity");
builder.ToTable(name: "UserClaims", "Identity");
builder.ToTable(name: "UserLogins", "Identity");
builder.ToTable(name: "UserTokens", "Identity");
builder.ToTable(name: "RoleClaims", "Identity");

Then I used a trigger on the old identity users table to keep the new identity users table in sync.

CREATE/*ALTER*/ TRIGGER [dbo].[IdentityUsers_Sync] ON [dbo].[Users] 
AFTER INSERT, UPDATE AS 
BEGIN
    --INSERT
    IF NOT EXISTS (SELECT * FROM deleted)
        BEGIN
            INSERT INTO [Identity].[Users] 
            (
                Id,
                UserName,
                NormalizedUserName,
                Email,
                NormalizedEmail,
                EmailConfirmed,
                PasswordHash,
                SecurityStamp,
                PhoneNumber,
                PhoneNumberConfirmed,
                TwoFactorEnabled,
                LockoutEnabled,
                AccessFailedCount
            )
            SELECT
                Id,
                UserName,
                UPPER(UserName),
                Email,
                UPPER(Email),
                EmailConfirmed,
                PasswordHash,
                SecurityStamp,
                PhoneNumber,
                PhoneNumberConfirmed,
                TwoFactorEnabled,
                0,
                0
            FROM inserted
        END
    --UPDATE
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        BEGIN
            UPDATE iu
            SET 
                iu.UserName = i.UserName,
                iu.NormalizedUserName = UPPER(i.UserName),
                iu.Email = i.Email,
                iu.NormalizedEmail = UPPER(i.Email),
                iu.EmailConfirmed = i.EmailConfirmed,
                iu.PasswordHash = i.PasswordHash,
                iu.SecurityStamp = i.SecurityStamp,
                iu.PhoneNumber = i.PhoneNumber,
                iu.PhoneNumberConfirmed = i.PhoneNumberConfirmed,
                iu.TwoFactorEnabled = i.TwoFactorEnabled,
                iu.LockoutEnabled = 0,
                iu.AccessFailedCount = 0
            FROM [Identity].[Users] iu, inserted i
            WHERE iu.Id = i.Id
        END
END
GO
ALTER TABLE [dbo].[Users] ENABLE TRIGGER [IdentityUsers_Sync]
GO

And my plan is to create an api controller in the legacy system to handle new registrations (via HttpClient in any new Core apps). Then the trigger will pick it up and keep the new system in sync.

Having the legacy system use the new identity tables doesn't look like an option at this point. Maybe additional fields could be added to the new schema and use a trigger to fill them in? Haven't tried that.

Sharing common database between .Net core and .Net standard Identity framework

2 Answers