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

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

Now to the Default page, here i have used 3 ways to show diffrent parts to the user, depending on the role the user have, i have user User.IsInRole in 2 ways, Response.write() and then somethingid.Visible = True.
On that way i can see things only for Managers, Admins og Users.

Then i have another way to show things its with the asp:LoginView RoleGroups. here i can make maybe a link that users cant see, but Managers and Admin can see the link.

So 3 diffrent ways to show sensitiv things.

Default.asp

Code:

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

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <input type="submit" Value="SignOut" runat="server" id="cmdSignOut">
        <br />
        <asp:Label ID="LegendInfo" runat="server" Text="Label"></asp:Label>&nbsp;</div>
        <br /><br />
        <asp:LoginView ID="LoginView1" runat="server">
        <RoleGroups>
            <asp:RoleGroup Roles="User, Admin, Manager">
                <ContentTemplate>
                You are loged in and is a Normal User: <asp:HyperLink ID="hlGroup1" NavigateUrl="#" runat="server">UserLink</asp:HyperLink> Kan ses af Manager/Admin/User<br />
                </ContentTemplate>
            </asp:RoleGroup>
        </RoleGroups>
    </asp:LoginView>
    <asp:LoginView ID="LoginView2" runat="server">
        <RoleGroups>
            <asp:RoleGroup Roles="Admin, Manager">
                <ContentTemplate>
                You are loged in and is a Admin: <asp:HyperLink ID="hlGroup1" NavigateUrl="#" runat="server">AdminLink</asp:HyperLink> Kan ses af Manager/Admin<br />
                </ContentTemplate>
            </asp:RoleGroup>
        </RoleGroups>
    </asp:LoginView>
    <asp:LoginView ID="LoginView3" runat="server">
        <RoleGroups>
            <asp:RoleGroup Roles="Manager">
                <ContentTemplate>
                You are loged in and is a Manager: <asp:HyperLink ID="hlGroup1" NavigateUrl="#" runat="server">ManagerLink</asp:HyperLink> Kan ses af Manager<br />
                </ContentTemplate>
            </asp:RoleGroup>
        </RoleGroups>
    </asp:LoginView>
    <br /><br />
    <asp:Label ID="text1" runat="server" Text="Label" Visible="false">tekst nr 1</asp:Label><br />
    <asp:Label ID="text2" runat="server" Text="Label" Visible="false">tekst nr 2</asp:Label><br />
    <asp:Label ID="text3" runat="server" Text="Label" Visible="false">tekst nr 3</asp:Label><br />
    </div>
    </form>
</body>
</html>

Default.asp.vb

Code:

Imports System.Web.Security

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim htmlString As New StringBuilder()
        ' Has the request been authenticated?
        If Request.IsAuthenticated Then
            ' Display generic identity information.
            ' This is always available, regardless of the type of
            ' authentication.
            htmlString.Append("<h3>Generic User Information</h3>")
            htmlString.Append("<b>name: </b>")
            htmlString.Append(User.Identity.Name)
            htmlString.Append("<br><b>Authenticated With: </b>")
            htmlString.Append(User.Identity.AuthenticationType)
            htmlString.Append("<br><br>")
        End If
        ' Was forms authentication used?

        If TypeOf User.Identity Is FormsIdentity Then
            ' Get the ticket.
            Dim ticket As FormsAuthenticationTicket = (DirectCast(User.Identity, FormsIdentity)).Ticket
            htmlString.Append("<h3>Ticket User Information</h3>")
            htmlString.Append("<b>Name: </b>")
            htmlString.Append(ticket.Name)
            htmlString.Append("<br><b>Issued at: </b>")
            htmlString.Append(ticket.IssueDate)
            htmlString.Append("<br><b>Expires at: </b>")
            htmlString.Append(ticket.Expiration)
            htmlString.Append("<br><b>Cookie version: </b>")
            htmlString.Append(ticket.Version)

            ' Display the information.
            LegendInfo.Text = htmlString.ToString()
        End If

        'Display the sensitive material depending on role 1 way
        If User.IsInRole("Manager") Then
            ' Display sensitive material
            Response.Write("Du er Manager")
        ElseIf User.IsInRole("Admin") Then
            ' Display sensitive material
            Response.Write("Du er Admin")
        ElseIf User.IsInRole("User") Then
            ' Display sensitive material
            Response.Write("Du er Bruger")
        Else
            ' Display only bland material
        End If

        'Display the sensitive material depending on role another way
        If User.IsInRole("Manager") Then
            ' Display sensitive material
            text1.Visible = True
        ElseIf User.IsInRole("Admin") Then
            ' Display sensitive material
            text2.Visible = True
        ElseIf User.IsInRole("User") Then
            ' Display sensitive material
            text3.Visible = True
        Else
            ' Display only bland material
        End If

    End Sub

    Private Sub cmdSignOut_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSignOut.ServerClick
        FormsAuthentication.SignOut()
        FormsAuthentication.RedirectToLoginPage()
    End Sub

End Class

web.config (SQL)

Code:

<?xml version="1.0"?>
<!-- 
    Note: As an alternative to hand editing this file you can use the 
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in 
    machine.config.comments usually located in 
    \Windows\Microsoft.Net\Framework\v2.x\Config 
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
  <connectionStrings>
    <add name="SQLConnstr" connectionString="Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\LogonDB.mdf;User Instance=true;"/>
  </connectionStrings>
  <system.web>
    <!-- 
            Set compilation debug="true" to insert debugging 
            symbols into the compiled page. Because this 
            affects performance, set this value to true only 
            during development.

            Visual Basic options:
            Set strict="true" to disallow all data type conversions 
            where data loss can occur. 
            Set explicit="true" to force declaration of all variables.
        -->
    <compilation debug="true" strict="false" explicit="true" targetFramework="4.0"/>
    <pages controlRenderingCompatibilityVersion="3.5" clientIDMode="AutoID">
      <namespaces>
        <clear/>
        <add namespace="System"/>
        <add namespace="System.Collections"/>
        <add namespace="System.Collections.Specialized"/>
        <add namespace="System.Configuration"/>
        <add namespace="System.Text"/>
        <add namespace="System.Text.RegularExpressions"/>
        <add namespace="System.Web"/>
        <add namespace="System.Web.Caching"/>
        <add namespace="System.Web.SessionState"/>
        <add namespace="System.Web.Security"/>
        <add namespace="System.Web.Profile"/>
        <add namespace="System.Web.UI"/>
        <add namespace="System.Web.UI.WebControls"/>
        <add namespace="System.Web.UI.WebControls.WebParts"/>
        <add namespace="System.Web.UI.HtmlControls"/>
      </namespaces>
    </pages>
    <!--
            The <authentication> section enables configuration 
            of the security authentication mode used by 
            ASP.NET to identify an incoming user. 
        -->
    <authentication mode="Forms">
      <forms name="MyCookieName" loginUrl="Logon.aspx" protection="All" path="/" timeout="30" />
    </authentication>
    <authorization>
      <deny users ="?" />
      <allow users = "*" />
    </authorization>
    <!--
            The <customErrors> section enables configuration 
            of what to do if/when an unhandled error occurs 
            during the execution of a request. Specifically, 
            it enables developers to configure html error pages 
            to be displayed in place of a error stack trace.
        -->
    <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
    </customErrors>
  </system.web>
</configuration>

If u need this to work with MS Access then u need to changes this
..
<connectionStrings>
<add name="SQLConnstr" connectionString="Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\LogonDB.mdf;User Instance=true;"/>
</connectionStrings>
..

With this
..
<connectionStrings>
<add name="AccessConnStr" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\LogonDB.mdb;Persist Security Info=True"/>
</connectionStrings>
..

Remember to add authorization for folders or pages that need to be used with the login script, here i think on CSS files in a folder like "ADD/CSS/" u will not use the folder in the header of ur login/default page if u havent added the folder to ur web.config.
<location path="FOLDER or site.aspx">
<system.web>
<authorization>
<allow users="John"/> // allow John ..note: you can have multiple users seperated by comma e.g. John,Mary,etc
<deny users="*"/> // deny others
</authorization>
</system.web>
</location>

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

Now to the last page that handels ur Roles.
Global.asax (MS Access)

Code:

<%@ Application Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Security.Principal" %>

<script runat="server">

    Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs on application startup
    End Sub
    
    Sub Application_End(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs on application shutdown
    End Sub
        
    Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs when an unhandled error occurs
    End Sub

    Public Sub Application_AuthenticateRequest(ByVal sender As Object, ByVal e As EventArgs)
        If Request.IsAuthenticated Then
            Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("AccessConnStr").ConnectionString)
            conn.Open()
            
            'Dim cmd As New SqlCommand("SELECT [Role] FROM [AccountRoles] WHERE [UserName] = @UserName", conn)
            Dim cmd As New OleDbCommand("Select Groups.Name FROM ((Roles INNER JOIN Groups ON Groups.GroupID = Roles.GroupID) INNER JOIN Users ON USERS.UserID = Roles.UserID) WHERE Users.Username=@UserName", conn)
            cmd.Parameters.AddWithValue("@UserName", User.Identity.Name)
            Dim reader As OleDbDataReader = cmd.ExecuteReader()
            Dim roleList As New ArrayList()
            While reader.Read()
                roleList.Add(reader("Name"))
                End While
            
            Dim roleListArray As String() = DirectCast(roleList.ToArray(GetType(String)), String())
            HttpContext.Current.User = New GenericPrincipal(User.Identity, roleListArray)
            
            reader.Close()
            conn.Close()
        End If
    End Sub
    
    Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs when a new session is started
    End Sub

    Sub Session_End(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs when a session ends. 
        ' Note: The Session_End event is raised only when the sessionstate mode
        ' is set to InProc in the Web.config file. If session mode is set to StateServer 
        ' or SQLServer, the event is not raised.
    End Sub
       
</script>

Global.asax (SQL)

Code:

<%@ Application Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Security.Principal" %>

<script runat="server">

    Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs on application startup
    End Sub
    
    Sub Application_End(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs on application shutdown
    End Sub
        
    Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs when an unhandled error occurs
    End Sub  
    
    Public Sub Application_AuthenticateRequest(ByVal sender As Object, ByVal e As EventArgs)
        If Request.IsAuthenticated Then
            Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLConnStr").ConnectionString)
            conn.Open()
            
            'Dim cmd As New SqlCommand("SELECT [Role] FROM [AccountRoles] WHERE [UserName] = @UserName", conn)
            Dim cmd As New SqlCommand("SELECT Groups.Name FROM Roles INNER JOIN Groups ON Roles.GroupID = Groups.GroupID INNER JOIN Users ON Roles.UserID = Users.UserID AND Users.Username=@UserName", conn)
            cmd.Parameters.AddWithValue("@UserName", User.Identity.Name)
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            Dim roleList As New ArrayList()
            While reader.Read()
                roleList.Add(reader("Name"))
            End While
            
            Dim roleListArray As String() = DirectCast(roleList.ToArray(GetType(String)), String())
            HttpContext.Current.User = New GenericPrincipal(User.Identity, roleListArray)
            
            reader.Close()
            conn.Close()
        End If
    End Sub
    
    Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs when a new session is started
    End Sub
    
    Sub Session_End(ByVal sender As Object, ByVal e As EventArgs)
        ' Code that runs when a session ends. 
        ' Note: The Session_End event is raised only when the sessionstate mode
        ' is set to InProc in the Web.config file. If session mode is set to StateServer 
        ' or SQLServer, the event is not raised.
    End Sub
       
</script>

I hope u can use this, im new to this so i cant make a description on why and why not, bc. i will not write something thats not true, but this is working 100% and again some of the things can be maked better, and if u see something and have feedback then PLZ. come with it, so we can make a better code.

But this way my add/Code Snip to this page, about making a Login Script using MS Access DB or SQL and then still can use Roles, so u can show diffrent things on the page depending on the user Role.

PLZ. Rate it or give me feedback and changes that can improve the code and then again SRY my english.

**gep13** · Aug 1st, 2012, 01:11 AM

siraero, I have merged our three threads together into one so that all the information is in one place.

Gary

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

Thread Tools

Display

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

[RESOLVED] ASP.NET Login Script authenticate and role based, with MS Access DB and SQL - PART 2.

[RESOLVED] ASP.NET Login Script authenticate and role based, with MS Access DB and SQL - PART 3.

Re: [RESOLVED] ASP.NET Login Script authenticate and role based with MS Access DB and

Posting Permissions