simplest way to password protect website with SQL Server

Discussion in 'ASP .Net' started by Brent Burkart, Oct 15, 2003.

  1. 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?
    Brent Burkart, Oct 15, 2003
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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?
    >
    >
    Cowboy \(Gregory A. Beamer\), Oct 15, 2003
    #2
    1. Advertising

  3. 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)" <> schrieb im Newsbeitrag news:#...
    > 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" <> wrote in message
    > news:...
    > > 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?
    > >
    > >

    >
    >
    Daniel Walzenbach, Oct 15, 2003
    #3
  4. 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)" <> wrote in
    message news:%...
    > 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" <> wrote in message
    > news:...
    > > 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?
    > >
    > >

    >
    >
    Brent Burkart, Oct 15, 2003
    #4
  5. "Brent Burkart" <> wrote in message
    news:%...
    > 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
    John Saunders, Oct 16, 2003
    #5
  6. Brent Burkart

    Jerry III Guest

    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" <> wrote in
    message news:...
    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)" <> schrieb im
    Newsbeitrag news:#...
    > 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" <> wrote in message
    > news:...
    > > 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?
    > >
    > >

    >
    >
    Jerry III, Oct 16, 2003
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kenneth McDonald
    Replies:
    2
    Views:
    648
    John J. Lee
    Dec 29, 2006
  2. AAaron123
    Replies:
    2
    Views:
    2,097
    AAaron123
    Jan 16, 2009
  3. AAaron123
    Replies:
    1
    Views:
    1,316
    Oriane
    Jan 16, 2009
  4. Gelonida
    Replies:
    4
    Views:
    566
    Gelonida
    Feb 1, 2011
  5. Jerry Krinock
    Replies:
    10
    Views:
    244
Loading...

Share This Page