simplest way to password protect website with SQL Server

**Cowboy \(Gregory A. Beamer\)** · Nov 17 '05, 09:59 PM

Re: simplest way to password protect website with SQL Server

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
FormsAuthentica tion.RedirectFr omLoginPage(use rName, 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.ExecuteScal ar();

This will reduce the amount of info pulled.

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

*************** *************** *************** *************** **********
Think Outside the Box!
*************** *************** *************** *************** **********
"Brent Burkart" <Brent.Burkart@ wvmb.com> wrote in message
news:ehsyt31kDH A.2512@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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?
>
>[/color]

**Daniel Walzenbach** · Nov 17 '05, 09:59 PM

Re: simplest way to password protect website with SQL Server

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(pwdcomp are(@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)" <NoSpamMgbworld @comcast.netNoS pamM> schrieb im Newsbeitrag news:#jnTm71kDH A.3700@TK2MSFTN GP11.phx.gbl...[color=blue]
> 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
> FormsAuthentica tion.RedirectFr omLoginPage(use rName, 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.ExecuteScal ar();
>
> This will reduce the amount of info pulled.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> *************** *************** *************** *************** **********
> Think Outside the Box!
> *************** *************** *************** *************** **********
> "Brent Burkart" <Brent.Burkart@ wvmb.com> wrote in message
> news:ehsyt31kDH A.2512@TK2MSFTN GP09.phx.gbl...[color=green]
> > 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?
> >
> >[/color]
>
>[/color]

**Brent Burkart** · Nov 17 '05, 09:59 PM

Re: simplest way to password protect website with SQL Server

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)" <NoSpamMgbworld @comcast.netNoS pamM> wrote in
message news:%23jnTm71k DHA.3700@TK2MSF TNGP11.phx.gbl. ..[color=blue]
> 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
> FormsAuthentica tion.RedirectFr omLoginPage(use rName, 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.ExecuteScal ar();
>
> This will reduce the amount of info pulled.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> *************** *************** *************** *************** **********
> Think Outside the Box!
> *************** *************** *************** *************** **********
> "Brent Burkart" <Brent.Burkart@ wvmb.com> wrote in message
> news:ehsyt31kDH A.2512@TK2MSFTN GP09.phx.gbl...[color=green]
> > I want to protect my website with a user and password. I have SQL[/color][/color]
Server[color=blue][color=green]
> > 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?
> >
> >[/color]
>
>[/color]

**John Saunders** · Nov 17 '05, 10:02 PM

Re: simplest way to password protect website with SQL Server

"Brent Burkart" <Brent.Burkart@ wvmb.com> wrote in message
news:%231MyHw2k DHA.1284@TK2MSF TNGP09.phx.gbl. ..[color=blue]
> 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?[/color]

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
<authorizatio n> elements in web.config.
--
John Saunders
Internet Engineer
john.saunders@s urfcontrol.com

**Jerry III** · Nov 17 '05, 10:03 PM

Re: simplest way to password protect website with SQL Server

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

"Daniel Walzenbach" <daniel.walzenb ach.NOSPAM@freu denberg.de> wrote in
message news:e76zFc2kDH A.392@TK2MSFTNG P11.phx.gbl...
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(pwdcomp are(@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)" <NoSpamMgbworld @comcast.netNoS pamM> schrieb im
Newsbeitrag news:#jnTm71kDH A.3700@TK2MSFTN GP11.phx.gbl...[color=blue]
> 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
> FormsAuthentica tion.RedirectFr omLoginPage(use rName, 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.ExecuteScal ar();
>
> This will reduce the amount of info pulled.
>
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
>
> *************** *************** *************** *************** **********
> Think Outside the Box!
> *************** *************** *************** *************** **********
> "Brent Burkart" <Brent.Burkart@ wvmb.com> wrote in message
> news:ehsyt31kDH A.2512@TK2MSFTN GP09.phx.gbl...[color=green]
> > I want to protect my website with a user and password. I have SQL[/color][/color]
Server[color=blue][color=green]
> > 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?
> >
> >[/color]
>
>[/color]

simplest way to password protect website with SQL Server

simplest way to password protect website with SQL Server

Comment

Comment

Comment

Comment

Comment