simplest way to password protect website with SQL Server

  • Thread starter Thread starter Brent Burkart
  • Start date Start date
B

Brent Burkart

I want to protect my website with a user and password. I have SQL Server
2000 where I want to store the users and passwords and the website is
complete. I just need to add in some security with password protection.

Can anyone help me out?
 
CREATE TABLE User
(
UserID int IDENTITY PRIMARY KEY,
UserName varchar(50) NOT NULL,
UserPwd varchar(15) NOT NULL
)

You can then query this table from your page and use the
FormsAuthentication.RedirectFromLoginPage(userName, persistCookie) to
redirect them back to the default page.

It is better if you set encryption, but accessing the table to check for a
user is rather simple. For performance you can do the query like:

CREATE PROCEDURE [dbo].[CheckUser]
(
@UserName varchar(50)
, @UserPwd varchar(15)
)
AS

SELECT UserName FROM User
WHERE UserName = @UserName
AND UserPwd = @UserPwd

You can then use ExecuteScalar like so:

string userName = cmd.ExecuteScalar();

This will reduce the amount of info pulled.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Brent,

You should consider storing passwords encrypted:



-- store them in a table (e.g. tblUser) with pwdencrypt

Update tblUser

Set Password = cast(pwdencrypt(@Passwort) as varbinary(256)),

ModifyDate = GetDate()

Where UserID = @UserID



-- read the password when you want to validate a user

Declare @password1 varbinary(256)

Select @password1 = Cast(password As varbinary(256)),

From tblUser

Where UserID = @UserID



-- and compare the password from your table with the one the user provided

if (isNull(pwdcompare(@Password,@Password1,0),0) <> 1)

print 'password is correct'



Hope this helps

Best regards


Daniel Walzenbach

P.S. If you need to contact me simply remove ".NOSPAM" from my email address.



Cowboy (Gregory A. Beamer) said:
CREATE TABLE User
(
UserID int IDENTITY PRIMARY KEY,
UserName varchar(50) NOT NULL,
UserPwd varchar(15) NOT NULL
)

You can then query this table from your page and use the
FormsAuthentication.RedirectFromLoginPage(userName, persistCookie) to
redirect them back to the default page.

It is better if you set encryption, but accessing the table to check for a
user is rather simple. For performance you can do the query like:

CREATE PROCEDURE [dbo].[CheckUser]
(
@UserName varchar(50)
, @UserPwd varchar(15)
)
AS

SELECT UserName FROM User
WHERE UserName = @UserName
AND UserPwd = @UserPwd

You can then use ExecuteScalar like so:

string userName = cmd.ExecuteScalar();

This will reduce the amount of info pulled.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
Brent Burkart said:
I want to protect my website with a user and password. I have SQL Server
2000 where I want to store the users and passwords and the website is
complete. I just need to add in some security with password protection.

Can anyone help me out?
Click to expand...
Click to expand...
 
This looks like it will work fine, however, I only want certain people to
have different access to pages within the website. I really don't need to
password protect the first part but I need to password protect the second
part. Is this a possibility or will I need to seperate them into two
different websites?

Thanks,
Brent
Cowboy (Gregory A. Beamer) said:
CREATE TABLE User
(
UserID int IDENTITY PRIMARY KEY,
UserName varchar(50) NOT NULL,
UserPwd varchar(15) NOT NULL
)

You can then query this table from your page and use the
FormsAuthentication.RedirectFromLoginPage(userName, persistCookie) to
redirect them back to the default page.

It is better if you set encryption, but accessing the table to check for a
user is rather simple. For performance you can do the query like:

CREATE PROCEDURE [dbo].[CheckUser]
(
@UserName varchar(50)
, @UserPwd varchar(15)
)
AS

SELECT UserName FROM User
WHERE UserName = @UserName
AND UserPwd = @UserPwd

You can then use ExecuteScalar like so:

string userName = cmd.ExecuteScalar();

This will reduce the amount of info pulled.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
Brent Burkart said:
I want to protect my website with a user and password. I have SQL Server
2000 where I want to store the users and passwords and the website is
complete. I just need to add in some security with password protection.

Can anyone help me out?
Click to expand...
Click to expand...
 
Brent Burkart said:
This looks like it will work fine, however, I only want certain people to
have different access to pages within the website. I really don't need to
password protect the first part but I need to password protect the second
part. Is this a possibility or will I need to seperate them into two
different websites?
Click to expand...

Have you looked into Forms Authentication?

Also, you can protect different parts of the web site so that only
particular people can access them. Look up the <authentication> and
<authorization> elements in web.config.
 
This is a half-way solution as the passwords are still sent to the SQL
server unencrypted. It's a lot better solution to create a hash of the
password in the Asp.Net page validating the user (which will always be 16
bytes for MD5 and 20 bytes for SHA1) and compare the hashed values.

Jerry

message Brent,

You should consider storing passwords encrypted:



-- store them in a table (e.g. tblUser) with pwdencrypt

Update tblUser

Set Password = cast(pwdencrypt(@Passwort) as varbinary(256)),

ModifyDate = GetDate()

Where UserID = @UserID



-- read the password when you want to validate a user

Declare @password1 varbinary(256)

Select @password1 = Cast(password As varbinary(256)),

From tblUser

Where UserID = @UserID



-- and compare the password from your table with the one the user provided

if (isNull(pwdcompare(@Password,@Password1,0),0) <> 1)

print 'password is correct'



Hope this helps

Best regards


Daniel Walzenbach

P.S. If you need to contact me simply remove ".NOSPAM" from my email
address.



Cowboy (Gregory A. Beamer) said:
CREATE TABLE User
(
UserID int IDENTITY PRIMARY KEY,
UserName varchar(50) NOT NULL,
UserPwd varchar(15) NOT NULL
)

You can then query this table from your page and use the
FormsAuthentication.RedirectFromLoginPage(userName, persistCookie) to
redirect them back to the default page.

It is better if you set encryption, but accessing the table to check for a
user is rather simple. For performance you can do the query like:

CREATE PROCEDURE [dbo].[CheckUser]
(
@UserName varchar(50)
, @UserPwd varchar(15)
)
AS

SELECT UserName FROM User
WHERE UserName = @UserName
AND UserPwd = @UserPwd

You can then use ExecuteScalar like so:

string userName = cmd.ExecuteScalar();

This will reduce the amount of info pulled.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
Brent Burkart said:
I want to protect my website with a user and password. I have SQL Server
2000 where I want to store the users and passwords and the website is
complete. I just need to add in some security with password protection.

Can anyone help me out?
Click to expand...
Click to expand...
 
Back
Top