ASP.NET Login Script authenticate and role based with MS Access DB and SQL

**siraero** · Jul 21st, 2012, 10:46 AM

Hi

First of all i making this thread bc. im new to this and not a pro.
And bc i used 4 days searching the net for help/solutions/tutorials on this, but i dident found any 100% solution.

In this (My first code snip, inhere) i will show a solution on a Login Script in ASP.NET VB, using MS Access DB and SQL DB, with Authenticate and Roles.

(SRY MY ENGLISH)
As i write i used a lot of time searching the net for a solution on this but i dident found one, and i HATE the login script provided with the ASP.NET in Visual Web Developer 2010 Express. (VWD2010E)

I need a login script where the users was saved in a DB and where i can give my page some Roles so an User cant see the same things As a Manager or Admin.

I thread in here was http://www.vbforums.com/showthread.php?p=4201896
In here i tryed to request help but dident get it and i think its bc the other users dident know, that i was not a pro and dident have the education so some of the things i got in feedback i dident understand.

So if u a Noob like me, then i hope u can Use this Code Snip, The code is working, and i have used some code from other tutorials, so i know that some things can be edit and maked better, im looking at that and if other have some fix/edit to this code, then PLZ. come with it.

I used these link as tutorials.
http://support.microsoft.com/kb/308157
http://www.4guysfromrolla.com/articles/082703-1.2.aspx
http://forums.asp.net/t/1419687.aspx/1

Now to the database.

- Start a new website inside VWD2010E, i used VB and started a new ASP.NET Empty Web site.

- Add the Folder "App_Data".

- Make one of these database in the "App_Data".
MS Access DB
Call the DB LogonDB.mdb
CREATE TABLE Users
[UserID] PRIMARY KEY, AUTONUMBERS , NOT NULL
[Username] [varchar/Text] NOT NULL
[Password] [varchar/Text] NOT NULL
CREATE TABLE Groups
[GroupID] PRIMARY KEY, AUTONUMBERS , NOT NULL
[Name] [varchar/Text] NOT NULL
CREATE TABLE Roles
[UserID] [Int/Numbers] NOT NULL
[GroupID] [Int/Numbers] NOT NULL

SQL DB
Call the DB LogonDB.mdf
CREATE TABLE Users
[UserID] PRIMARY KEY, AUTONUMBERS/INT , NOT NULL
[Username] [varchar/Text] NOT NULL
[Password] [varchar/Text] NOT NULL
CREATE TABLE Groups
[GroupID] PRIMARY KEY, AUTONUMBERS/INT , NOT NULL
[Name] [varchar/Text] NOT NULL
CREATE TABLE Roles
[UserID] [Int/Numbers] NOT NULL
[GroupID] [Int/Numbers] NOT NULL

Then insert the data to the DB u are using.
Users
Username = user1, user2, user3
Password = user1, user2, user3

Groups
Name = Manager, Admin, User (U can make whatever u want here)

Roles
(here u are using the UserID from Users and the GroupID from Groups)
UserID 1, 2, 3
GroupID 1, 2, 3

Here i want
user1 to be Manager
user2 to be Admin
user3 to be User
And if i have an user4 maybe he/she need to be Admin to then i will add
the UserID for that user to Roles in UserID and then in GroupID inside Roles i will write 2, then the added user will be an Admin.

Okay now to the code.
Logon.aspx

Code:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Logon.aspx.vb" Inherits="Logon" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <h3>
   <font face="Verdana">Logon Page</font>
</h3>
<table>
   <tr>
      <td>UserName:</td>
      <td><input id="txtUserName" type="text" runat="server"></td>
      <td><ASP:RequiredFieldValidator ControlToValidate="txtUserName"
           Display="Static" ErrorMessage="*" runat="server" 
           ID="vUserName" /></td>
   </tr>
   <tr>
      <td>Password:</td>
      <td><input id="txtUserPass" type="password" runat="server"></td>
      <td><ASP:RequiredFieldValidator ControlToValidate="txtUserPass"
          Display="Static" ErrorMessage="*" runat="server" 
          ID="vUserPass" />
      </td>
   </tr>
   <tr>
      <td>Persistent Cookie:</td>
      <td><ASP:CheckBox id="chkPersistCookie" runat="server" autopostback="false" /></td>
      <td></td>
   </tr>
</table>
<input type="submit" Value="Logon" runat="server" ID="cmdLogin"><p></p>
<asp:Label id="lblMsg" ForeColor="red" Font-Name="Verdana" Font-Size="10" runat="server" />
    </div>
    </form>
</body>
</html>

Logon.aspx.vb (SQL)

Code:

Imports System.Data.SqlClient
Imports System.Web.Security
Imports System.Data

Partial Class Logon
    Inherits System.Web.UI.Page

    Private Function ValidateUser(ByVal userName As String, ByVal passWord As String) As Boolean
        Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim lookupPassword As String

        lookupPassword = Nothing

        ' Check for an invalid userName.
        ' userName  must not be set to nothing and must be between one and 15 characters.
        If ((userName Is Nothing)) Then
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of userName failed.")
            Return False
        End If
        If ((userName.Length = 0) Or (userName.Length > 15)) Then
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of userName failed.")
            Return False
        End If

        ' Check for invalid passWord.
        ' passWord must not be set to nothing and must be between one and 25 characters.
        If (passWord Is Nothing) Then
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of passWord failed.")
            Return False
        End If
        If ((passWord.Length = 0) Or (passWord.Length > 25)) Then
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of passWord failed.")
            Return False
        End If

        Try
            ' Consult with your SQL Server administrator for an appropriate connection
            ' string to use to connect to your local SQL Server.
            conn = New SqlConnection(ConfigurationManager.ConnectionStrings("SQLConnStr").ConnectionString)
            conn.Open()

            ' Create SqlCommand to select pwd field from the users table given a supplied userName.
            cmd = New SqlCommand("SELECT Password, Username FROM Users WHERE Username=@userName AND Password=@passWord", conn)
            cmd.Parameters.Add("@userName", SqlDbType.VarChar, 25)
            cmd.Parameters("@userName").Value = userName
            cmd.Parameters.Add("@passWord", SqlDbType.VarChar, 25)
            cmd.Parameters("@passWord").Value = passWord


            ' Execute command and fetch pwd field into lookupPassword string.
            lookupPassword = cmd.ExecuteScalar()

            ' Cleanup command and connection objects.
            cmd.Dispose()
            conn.Dispose()
        Catch ex As Exception
            ' Add error handling here for debugging.
            ' This error message should not be sent back to the caller.
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Exception " & ex.Message)
        End Try

        ' If no password found, return false.
        If (lookupPassword Is Nothing) Then
            ' You could write failed login attempts here to the event log for additional security.
            Return False
        End If

        ' Compare lookupPassword and input passWord by using a case-sensitive comparison.
        Return (String.Compare(lookupPassword, passWord, False) = 0)

    End Function

    Private Sub cmdLogin_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLogin.ServerClick
        If ValidateUser(txtUserName.Value, txtUserPass.Value) Then
            Dim tkt As FormsAuthenticationTicket
            Dim cookiestr As String
            Dim ck As HttpCookie

            tkt = New FormsAuthenticationTicket(1, txtUserName.Value, DateTime.Now(), DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data")
            cookiestr = FormsAuthentication.Encrypt(tkt)
            ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr)
            If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration
            ck.Path = FormsAuthentication.FormsCookiePath()
            Response.Cookies.Add(ck)

            Dim strRedirect As String
            strRedirect = Request("ReturnURL")
            If strRedirect <> "" Then
                Response.Redirect(strRedirect, True)
            Else
                strRedirect = "Default.aspx"
                Response.Redirect(strRedirect, True)
            End If
        Else
            Response.Redirect("Logon.aspx", True)
        End If
    End Sub

End Class

Logon.aspx.vb (MS Access)

Code:

Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Web.Configuration

Partial Class Logon
    Inherits System.Web.UI.Page

    Private Function ValidateUser(ByVal userName As String, ByVal passWord As String) As Boolean
        Dim conn As OleDbConnection
        Dim cmd As OleDbCommand
        Dim lookupPassword As String

        lookupPassword = Nothing

        ' Check for an invalid userName.
        ' userName  must not be set to nothing and must be between one and 15 characters.
        If ((userName Is Nothing)) Then
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of userName failed.")
            Return False
        End If
        If ((userName.Length = 0) Or (userName.Length > 15)) Then
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of userName failed.")
            Return False
        End If

        ' Check for invalid passWord.
        ' passWord must not be set to nothing and must be between one and 25 characters.
        If (passWord Is Nothing) Then
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of passWord failed.")
            Return False
        End If
        If ((passWord.Length = 0) Or (passWord.Length > 25)) Then
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Input validation of passWord failed.")
            Return False
        End If

        Try
            ' Consult with your SQL Server administrator for an appropriate connection
            ' string to use to connect to your local SQL Server.
            conn = New OleDbConnection(ConfigurationManager.ConnectionStrings("AccessConnStr").ConnectionString)
            conn.Open()

            ' Create SqlCommand to select pwd field from the users table given a supplied userName.
            cmd = New OleDbCommand("SELECT Password, Username FROM Users WHERE Username=@userName AND Password=@passWord", conn)
            cmd.Parameters.Add("@userName", OleDbType.VarChar, 25)
            cmd.Parameters("@userName").Value = userName
            cmd.Parameters.Add("@passWord", OleDbType.VarChar, 25)
            cmd.Parameters("@passWord").Value = passWord


            ' Execute command and fetch pwd field into lookupPassword string.
            lookupPassword = cmd.ExecuteScalar()

            ' Cleanup command and connection objects.
            cmd.Dispose()
            conn.Dispose()
        Catch ex As Exception
            ' Add error handling here for debugging.
            ' This error message should not be sent back to the caller.
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Exception " & ex.Message)
        End Try

        ' If no password found, return false.
        If (lookupPassword Is Nothing) Then
            ' You could write failed login attempts here to the event log for additional security.
            Return False
        End If

        ' Compare lookupPassword and input passWord by using a case-sensitive comparison.
        Return (String.Compare(lookupPassword, passWord, False) = 0)

    End Function

    Private Sub cmdLogin_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLogin.ServerClick
        If ValidateUser(txtUserName.Value, txtUserPass.Value) Then
            Dim tkt As FormsAuthenticationTicket
            Dim cookiestr As String
            Dim ck As HttpCookie

            tkt = New FormsAuthenticationTicket(1, txtUserName.Value, DateTime.Now(), DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data")
            cookiestr = FormsAuthentication.Encrypt(tkt)
            ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr)
            If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration
            ck.Path = FormsAuthentication.FormsCookiePath()
            Response.Cookies.Add(ck)

            Dim strRedirect As String
            strRedirect = Request("ReturnURL")
            If strRedirect <> "" Then
                Response.Redirect(strRedirect, True)
            Else
                strRedirect = "Default.aspx"
                Response.Redirect(strRedirect, True)
            End If
        Else
            Response.Redirect("Logon.aspx", True)
        End If
    End Sub
End Class

Thread: ASP.NET Login Script authenticate and role based with MS Access DB and SQL

Thread Tools

Display

Threaded View

ASP.NET Login Script authenticate and role based with MS Access DB and SQL

Posting Permissions