Results 1 to 6 of 6

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

Threaded View

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    48

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

    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
    Last edited by Hack; Oct 11th, 2012 at 05:40 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width