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.
CodePudding user response:
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)
- 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;
CodePudding user response:
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.