Merge ASP Membership users and tbUsers from DataBase Win Application on sql

Question

Hello. I've got a real trouble now.

I've got my win Application and a lot of functionality with TbUser table. It's simple table with ID , Username and Password , and some nullable fields

And now I need to merge it with my ASP.NET Membership table :S

__Finally I need to register users in my WinApplication !

And login into ASP.NET web application >_<

    TABLE [dbo].[aspnet_Membership](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [Password] [nvarchar](128) COLLATE Cyrillic_General_CI_AI_KS_WS NOT NULL,
    [PasswordFormat] [int] NOT NULL DEFAULT ((0)),
    [PasswordSalt] [nvarchar](128) COLLATE Cyrillic_General_CI_AI_KS_WS NOT NULL,
    [MobilePIN] [nvarchar](16) COLLATE Cyrillic_General_CI_AI_KS_WS NULL,
    [Email] [nvarchar](256) COLLATE Cyrillic_General_CI_AI_KS_WS NULL,
    [LoweredEmail] [nvarchar](256) COLLATE Cyrillic_General_CI_AI_KS_WS NULL,
    [PasswordQuestion] [nvarchar](256) COLLATE Cyrillic_General_CI_AI_KS_WS NULL,
    [PasswordAnswer] [nvarchar](128) COLLATE Cyrillic_General_CI_AI_KS_WS NULL,
    [IsApproved] [bit] NOT NULL,
    [IsLockedOut] [bit] NOT NULL,
    [CreateDate] [datetime] NOT NULL,
    [LastLoginDate] [datetime] NOT NULL,
    [LastPasswordChangedDate] [datetime] NOT NULL,
    [LastLockoutDate] [datetime] NOT NULL,
    [FailedPasswordAttemptCount] [int] NOT NULL,
    [FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
    [FailedPasswordAnswerAttemptCount] [int] NOT NULL,
    [FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
    [Comment] [ntext] COLLATE Cyrillic_General_CI_AI_KS_WS NULL,
PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])

Tables got a different names and different content , also I wondering in ASP.NET Password and User name situated in different tables :S

I think I need to call some SQL convertation procedure OnClose my winApplication or somewhere inside ASP web site. This way I will convert TbUsers to AspMembership just to allow multilogin into the site and stay on using my TbUsers table.

But that's will be really hard SQL procedure :(

asp table with username :

    TABLE [dbo].[aspnet_Users](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL DEFAULT (newid()),
    [UserName] [nvarchar](256) COLLATE Cyrillic_General_CI_AI_KS_WS NOT NULL,
    [LoweredUserName] [nvarchar](256) COLLATE Cyrillic_General_CI_AI_KS_WS NOT NULL,
    [MobileAlias] [nvarchar](16) COLLATE Cyrillic_General_CI_AI_KS_WS NULL DEFAULT (NULL),
    [IsAnonymous] [bit] NOT NULL DEFAULT ((0)),
    [LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])

and one more hard way - is rewrite all procedures to ASP.Membership databace, but then I need to use sql asp stored procedures and just hope it will works fine . . .

And the second way is more Acceptable fro me as long but more correct and not hardcoding ...

So I need your advices :-/

Answer 1

OK, I would recommend against your existing approach of trying to merge your two authentication systems together using just SQL. You are asking for problems and the headaches you already have.

Instead, look at creating your own membership provider. A membership provider is (along with other things) the bit of code that allows asp.net to authenticate your user.

Writing your own provider will allow you to authenticate your user against your table tbUsers rather than aspnet_Users

Have a look at this video (not looked at it myself but should get you started).

Create custom membership provider video how-to

Hope this helps.

Answer 2

You can use Forms Authentication with a non ASP.NET application as well, e.g., a Windows Forms application. Besides, Forms Authentication is a well tested and widely used feature out of the box.

So, if I were in your position, I would transfer all the user data from the custom tables to the corresponding ones in ASP.NET membership, and then I wouldn't use any more these tables.

Just don't forget to adjust the corresponding parameters that, for example, control how many characters are allowed in password (by default Forms Authentication requires minimum of 7 characters and one delimiter in password), in the app.config, to comply with your current scheme. Perhaps there are incompatibilities not mentioned here, but that was going to be my plan.