By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,570 Members | 999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,570 IT Pros & Developers. It's quick & easy.

simplest way to password protect website with SQL Server

P: n/a
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?
Nov 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <Br***********@wvmb.com> wrote in message
news:eh**************@TK2MSFTNGP09.phx.gbl...
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?

Nov 17 '05 #2

P: n/a
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)" <No************@comcast.netNoSpamM> schrieb im Newsbeitrag news:#j**************@TK2MSFTNGP11.phx.gbl...
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" <Br***********@wvmb.com> wrote in message
news:eh**************@TK2MSFTNGP09.phx.gbl...
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?


Nov 17 '05 #3

P: n/a
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)" <No************@comcast.netNoSpamM> wrote in
message news:%2****************@TK2MSFTNGP11.phx.gbl...
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" <Br***********@wvmb.com> wrote in message
news:eh**************@TK2MSFTNGP09.phx.gbl...
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?


Nov 17 '05 #4

P: n/a
"Brent Burkart" <Br***********@wvmb.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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?


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.
--
John Saunders
Internet Engineer
jo***********@surfcontrol.com
Nov 17 '05 #5

P: n/a
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" <da**********************@freudenberg.de> wrote in
message news:e7*************@TK2MSFTNGP11.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(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)" <No************@comcast.netNoSpamM> schrieb im
Newsbeitrag news:#j**************@TK2MSFTNGP11.phx.gbl...
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" <Br***********@wvmb.com> wrote in message
news:eh**************@TK2MSFTNGP09.phx.gbl...
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?


Nov 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.