?
Solved

user password database

Posted on 2005-04-19
13
Medium Priority
?
881 Views
Last Modified: 2012-05-05
Hi,

Please kindly guide me on desining a username/password table.

what are the typical fields needed? we need a multiuser login. using asp.net

presently columns are:
Date
Username
Password
workstation ip address ?

Thanks.
0
Comment
Question by:EdwardPeter
  • 4
  • 4
  • 2
  • +3
13 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13815574
emailid
phone
address
register_date
0
 
LVL 28

Assisted Solution

by:rafrancisco
rafrancisco earned 200 total points
ID: 13815593
You can also add some user information if you want such as:

First Name
Last Name
LastLoginTime (datetime)
Email Address
0
 
LVL 23

Accepted Solution

by:
adathelad earned 800 total points
ID: 13815620
It all depends on what you need for your specific environment.
i.e.
I usually start with the following:

UserId (INT IDENTITY)
UserName
ForeName
Surname
Password (encrypted)
Active

Other common columns include:
LastLoggedIn
CreationDate

But to be honest, the only fields you should include are the ones that you think you need, which depends on the functionality that you need - there's no point adding in some of these fields if you will have no use for them. In other words, only you know what fields you will need ;)

Hope this helps
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:EdwardPeter
ID: 13815639
adathelad,

Your the man!

Can you kindly assist me how to encrypt ?

Thanks.

0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13815679
SQL Server's Undocumented Password Encryption Functions

http://www.windowsitpro.com/Article/ArticleID/9809/9809.html
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 13815706
Here is the code for encrypting passwords

DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'itsvtk'
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)
0
 

Author Comment

by:EdwardPeter
ID: 13815753
itsvtk,

Is it okay if you could kindly guide me how to encrypt and decrypt?

Thanks so much.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 13815804
As the pwdencrypt() function is undocumented, I'd personally stay away from it - there's no guarantees on it's use/functionality in future versions.

Instead, I prefer to have my front-end do the encryption - especially as .NET has its own Cryptography classes to make it easier. (e.g. http://www.dart.com/samples/cryptdecryptdotnet.asp )

there's also a lot of other 3rd party encryption tools out there.
0
 
LVL 14

Assisted Solution

by:Thandava Vallepalli
Thandava Vallepalli earned 800 total points
ID: 13815810
Eg:

WHILE INSERTING USER INFO INTO TABLE


INSERT INTO <TABLENAME> ( UNAME, PWD, EMAIL )
SELECT 'ITSVTK',   pwdencrypt( 'EGYPT' ), 'VTK@DOMAIN.COM'

the above statement will insert encrypted password in table

while checking the password entered by user with the  pwd field in table....


DECLARE @PWD VARCHAR(255)

SELECT @PWD = PWD
FROM <TABLENAME>
WHERE UNAME = 'ITSVTK'

select pwdcompare('EGYPT', @PWD)   -- If it returns 1, password entered by the user is correct... otherwise its wrong..

itsvtk




0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 200 total points
ID: 13815976
0
 

Author Comment

by:EdwardPeter
ID: 13816019
itsvtk,

Please kindly assist if it is like this.

________________________________________________________________procedure encrypt

exec dbo.encrypt @username='my username here', @password='my password here'

create proc dbo.encrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as
DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'itsvtk'                                            <----------------------------what is this ?
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)
INSERT INTO <TABLENAME> ( UNAME, PWD,)
SELECT 'ITSVTK',   pwdencrypt( @mypassword )
GO


________________________________________________________________procedure decrypt
exec dbo.decrypt @username='my username here', @password='my password here'

create proc dbo.decrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as
DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'itsvtk'                                            <----------------------------what is this ?
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

SELECT @PWD = PWD
FROM <TABLENAME>
WHERE UNAME = @myusername
select pwdcompare(@mypassword, @PWD)  <---------hope this is correct?
Go
0
 
LVL 4

Expert Comment

by:Greg Rowland
ID: 13816232
Excerpt from DotNetNuke.com Open Source Initiative.

CREATE TABLE [Users] (
      [UserID] [int] IDENTITY (1, 1) NOT NULL ,
      [FirstName] [nvarchar] (50) NOT NULL ,
      [LastName] [nvarchar] (50) NOT NULL ,
      [Street] [nvarchar] (50) NULL ,
      [City] [nvarchar] (50) NULL ,
      [Region] [nvarchar] (50) NULL ,
      [PostalCode] [nvarchar] (50) NULL ,
      [Country] [nvarchar] (50) NULL ,
      [Password] [nvarchar] (50) NOT NULL ,
      [Email] [nvarchar] (100) NOT NULL ,
      [Unit] [nvarchar] (50) NULL ,
      [IsSuperUser] [bit] NOT NULL CONSTRAINT [DF_Users_IsSuperUser] DEFAULT (0),
      [Telephone] [nvarchar] (50) NULL ,
      [Username] [nvarchar] (100) NOT NULL ,
      CONSTRAINT [PK_Users] PRIMARY KEY  NONCLUSTERED
      (
            [UserID]
      )  ON [PRIMARY] ,
      CONSTRAINT [IX_Users] UNIQUE  NONCLUSTERED
      (
            [Username]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO


0
 

Author Comment

by:EdwardPeter
ID: 13816413
Hi,

I'll repost and hopefully other experts will join.

Thanks.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question