Avatar of fwsteal
fwsteal
 asked on

custom form login

asp.net 2.0 c# - login form to query a table in sql server 2005 via a stored procedure. I'm not sure how to wirte the c# code and the stored procedure. I need help writing the asp.net c# code and the stored procedure.

login.aspx file
<asp:TextBox ID="txtbxSiteCode" runat="server"></asp:TextBox>
<asp:TextBox ID="txtbxPassword" runat="server" TextMode="Password"></asp:TextBox>
<asp:Button ID="btnLogin" runat="server" Text="Log In" OnClick="btnLogin_Click" />

login.aspx.cs
    protected void btnLogin_Click(object sender, EventArgs e)
    {
        strUserId as String = txtbxSiteCode.Text.ToString();
        strPassword as String = txtbxPassword.Text.ToString();
        //qry for userid and password in sql server table
        //if the user has a userid and password then
            //does account have an access? access = 1; no access = 0
            //access = 1 then redirect to secure folder
            //else if no access = 0 then redirect to login.aspx page
        //else redirect to login.aspx
    }
--------------------------------------------------------------------

login table:
TABLE [dbo].[ClientLogins]
 [CSCID] [int] IDENTITY(1,1) NOT NULL,
 [CIDFK] [int] NOT NULL,
 [SiteCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [SitePassword] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [PortalAccess] [int] NOT NULL,

stored procedure:
uspGetClientLogin

ASP.NET

Avatar of undefined
Last Comment
fwsteal

8/22/2022 - Mon
Edwin_C

My suggestion for you is to make use the default membership provider in asp.net 2.0 because you can then use all of the existing security related controls and functions.  If you really need custom datatable for storing the users' details, you can implement a custom membership provider.  Check out the sample codes in http://msdn2.microsoft.com/en-us/library/f1kyba5e.aspx if you decided to write your own provider.
fwsteal

ASKER
I looked but those seem to work with just userid and password. I also need to see if the login is active or not.
Edwin_C

The built-in MembershipUser class has IsApproved and IsLockedOut properties for you to control whether the user can login or not.  Can they server your purpose?
Your help has saved me hundreds of hours of internet surfing.
fblack61
fwsteal

ASKER
possible; just not sure how to use them -- never tried.
fwsteal

ASKER
won't I have to use the default aspnet tables or is it possible to use my tables?
Edwin_C

Here is a walkthrough showing how to configure your web app to use membership.  http://msdn2.microsoft.com/en-us/library/879kf95c(VS.80).aspx  It is not difficult.

The basic membershipprovider uses a mdf file (Access DB) to store the user data.  If you want the user data to be stored in a SQL server, you just need a little bit more to do in the configuration.  See http://msdn2.microsoft.com/en-us/library/x28wfk74(VS.80).aspx

If you really need to have a custom data structure for your user data, then you need to write your own membership provider class.  The sample code in http://msdn2.microsoft.com/en-us/library/f1kyba5e.aspx is pretty and you can just modify the code according to your data structure.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
fwsteal

ASKER
I'm having troubles fitting a example into my current web.config.
fwsteal

ASKER
I've got a clientlogin table:

[ClientLogins](
 [CSCID] [int] IDENTITY(1,1) NOT NULL,
 [CIDFK] [int] NOT NULL,
 [SiteCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [SitePassword] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,


My current process is to create a client and my create client process automatically
creates my client userid and password and writes them to the above table and also
writes the clientid as a foriegn key.

Ex:
CIDFK - 30
SiteCode - tommy
SitePassword - gun


my current web.config:
---------
<?xml version="1.0"?>
<configuration>
      <!-- Begin appsettings for site -->
      <appSettings>
            <add key="SiteName" value="my.PORTAL"/>
            <add key="MailServer" value="local"/>
            <add key="ErrorLogEmail" value="me@me.com"/>
            <add key="EnableErrorLogEmail" value="true"/>
      </appSettings>
      <!-- End appsettings for site -->
      
      <!-- Begin dbconnection settings -->
      <connectionStrings>
  <clear />
  <add name="ConnectionStringD" connectionString="Data Source=myserver;Initial Catalog=db;Persist Security Info=True;User ID=123;Password=123;Trusted_Connection=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
      <!-- End dbconnection settings -->
      
      <!-- Begin ASP.Net Class Settings. -->
      <system.web>
            <compilation defaultLanguage="c#" debug="true" />
            <authentication mode="Forms">
                          <!-- begin the admin login process-->
                  <forms loginUrl="adminlogin.aspx">
                        <credentials passwordFormat="Clear">
                              <user name="admin" password="admin" />
                        </credentials>
                  </forms>
                        <!-- end the admin process-->
            </authentication>
            <authorization>
                  <allow users="*" />
            </authorization>
            <customErrors mode="Off" />
            <globalization requestEncoding="utf-8" responseEncoding="utf-8" culture="en-US" uiCulture="en-US" />
      </system.web>
      <!-- End ASP.Net Class Settings. -->
      
      <!-- Begin Secure admin Settings. -->
      <location path="secure/admin" >
            <system.web>
                  <authorization>
                        <deny users="?" />
                  </authorization>
            </system.web>
      </location>
      <!-- End Secure admin Settings. -->
      
      <!-- Begin Net Class Settings. -->
      <system.net>
            <mailSettings>
                  <smtp deliveryMethod="PickupDirectoryFromIis" from="me@me.com">
                        <network host="127.0.0.1" port="25" defaultCredentials="true"/>
                  </smtp>
            </mailSettings>
      </system.net>
      <!-- End Net Class Settings. -->
      
</configuration>
fwsteal

ASKER
my site is set up like this:

default.aspx
login.aspx
  /secure folder
       default.aspx, profile.aspx, files.aspx
       /admin folder
            default.aspx, clients.aspx

secure folder is to be protected from anonymous users and
is where the clients login to view thier information.

under the secure folder is another folder: admin. the
admin folder is for admins only.

right now I'm using a single admin account that is
hardcoded in the web.config.

to gain access to the secure folder, the user must have an account in the sql server 2005 db table: clientlogins.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Edwin_C

What is the problem now?

To secure the /Secure folder you need

 <!-- Begin Secure admin Settings. -->
      <location path="secure" >
            <system.web>
                  <authorization>
                        <deny users="?" />
                  </authorization>
            </system.web>
      </location>
      <location path="secure/admin" >
            <system.web>
                  <authorization>
                        <deny users="?" />
                        <allow users="admin" />
                  </authorization>
            </system.web>
      </location>

<!-- End Secure admin Settings. -->
fwsteal

ASKER
here is the web.config:

            <authentication mode="Forms">
                  <forms name="SqlAuthCookie" loginUrl="login.aspx" timeout="30">
                        <credentials passwordFormat="Clear">
                              <user name="me" password="me"/>
                        </credentials>
                  </forms>
            </authentication>

            <authorization>
                  <allow users="*" />
                  <allow users="me" roles="Admin"/>
            </authorization>


      <location path="secure" >
            <system.web>
                  <authorization>
                        <deny users="?" />
                  </authorization>
            </system.web>
      </location>

      <location path="secure/admin" >
            <system.web>
                  <authorization>
                        <allow roles="Admin" />
                        <deny users="?" />
                  </authorization>
            </system.web>
      </location>

Does that seem right?

However, if the client knows of the admin folder the client can get in.
fwsteal

ASKER
My client login page:

<table>
   <tr>
      <td>site code:</td>
      <td>
          <asp:TextBox ID="txtSiteCode" runat="server"></asp:TextBox></td>
      <td><ASP:RequiredFieldValidator ControlToValidate="txtSiteCode"
           Display="Dynamic" ErrorMessage="*" runat="server"
           ID="vUserName" /></td>
   </tr>
   <tr>
      <td>site password:</td>
      <td>
          <asp:TextBox ID="txtSitePassword" runat="server"></asp:TextBox></td>
      <td><ASP:RequiredFieldValidator ControlToValidate="txtSitePassword"
          Display="Dynamic" 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>
<p></p>
        <asp:Button ID="btnLogin" runat="server" Text="Login" OnClick="btnLogin_Click" />
        <br />
<asp:Label id="lblMsg" ForeColor="red" Font-Name="Verdana" Font-Size="10" runat="server" />
-----------------

    //sql connection
    string myConn = SiteConfiguration.DbConnectionString;

    protected void btnLogin_Click(object sender, EventArgs e)
    {
        if (ValidateUser(txtSiteCode.Text, txtSitePassword.Text))
        {
            FormsAuthentication.RedirectFromLoginPage(txtSiteCode.Text, chkPersistCookie.Checked);
            Response.Redirect("secure/default.aspx");
        }
        else
        {
            Response.Redirect("login.aspx", true);
        }
    }

private bool ValidateUser(string siteCode, string sitePassword)
    {
        SqlConnection conn;
        SqlCommand cmd;
        string lookupPassword = null;
        if (((siteCode == null)))
        {
            return false;
        }
        if (((siteCode.Length == 0) | (siteCode.Length > 15)))
        {
            return false;
        }
        if ((sitePassword == null))
        {
            return false;
        }
        if (((sitePassword.Length == 0) | (sitePassword.Length > 25)))
        {
            return false;
        }
        try
        {
            conn = new SqlConnection(myConn);
            conn.Open();
            cmd = new SqlCommand("Select sitepassword from clientlogins where sitecode=@sitecode", conn);
            cmd.Parameters.AddWithValue("@sitecode", siteCode);
            lookupPassword = Convert.ToString(cmd.ExecuteScalar());
            cmd.Dispose();
            conn.Dispose();
        }
        catch (Exception ex)
        {
            System.Diagnostics.Trace.WriteLine("[ValidateUser] Exception " + ex.Message);
        }
        if ((lookupPassword == null))
        {
            return false;
        }
        return (string.Compare(lookupPassword, sitePassword, false) == 0);
    }


So when the client lands on the secure/default.aspx page, how to I get the client's record id? I'll need it so I can put it into a label and use it as needed.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
fwsteal

ASKER
since the client table cid is 30, the clientlogin idfk is 30. how should I code to get the cidfk for the clientlogin and where should it go?
fwsteal

ASKER
problem now? I'm learning too slow.
Edwin_C

OK one question at a time

<location path="secure" >
            <system.web>
                  <authorization>
                        <deny users="?" />  <- deny anonymous and so user must login
                  </authorization>
            </system.web>
      </location>

      <location path="secure/admin" >
            <system.web>
                  <authorization>
                        <allow roles="Admin" />  <- change to users="admin" if you want just "admin" user can access
                        <deny users="*" />  <-  allow admin "role" but deny all others
                  </authorization>
            </system.web>
      </location>
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Edwin_C

If you are using MembershipProvider, Page.User returns you an IPrincipal object about the authenticated user.  Page.User.Identity returns the Id of the user.
Edwin_C

Also note that there are already built-in controls like Loginin, LoginName, LoginStatus, etc for you to use.  So you can simple put a LoginName control in your page instead of getting the name from DB and then display in a label.
fwsteal

ASKER
thanks for helping.

secure folder is correct

secure/admin -- I only have one user account hardcoded in the web.config and it is in the admin role; not tied to a db login table. I only want the admin account to access this folder and want to keep everyone else out.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
fwsteal

ASKER
i couldn't figure out how to get the 2.0 login controls to work with my current client login table so i didn't use them.
Edwin_C

>>since the client table cid is 30, the clientlogin idfk is 30. how should I code to get the cidfk for the clientlogin and where should it go?

Not sure about what you mean.  If you have cid from Page.User.Identity, then you can run a SqlCommand like

SqlCommand cmd = new SqlCommand("SELECT cidfk FROM clientlogin WHERE cid=@cid", myConnectionObject);
cmd.Parameters.AddWithValue("@cid", Convert.ToInt32(Page.User.Identity));
myConnectionObject.Open();
int cidfk = (int)cmd.ExecuteScalar();
myConnectionObject.Close();
Edwin_C

>>secure/admin -- I only have one user account hardcoded in the web.config and it is in the admin role; not tied to a db login table. I only want the admin account to access this folder and want to keep everyone else out.

Then
 <location path="secure/admin" >
            <system.web>
                  <authorization>
                        <allow users="admin" />  
                        <deny users="*" />
                  </authorization>
            </system.web>
      </location>

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Edwin_C

>>i couldn't figure out how to get the 2.0 login controls to work with my current client login table so i didn't use them.

If you are building a membershipprovider and it works correctly, the login control and other controls should work automatically!  
Edwin_C

>> problem now? I'm learning too slow.
If you are new to ASP.NET, then you should use standard functions and controls as much as possible.  I thought I spend more or less a week to finish my first custom membershipprovider.  Hence you should not expect this can be done in one day if you have no previous experience.
fwsteal

ASKER
Here is the check in the clientlogins table to verify the sitecode and sitepassword

conn = new SqlConnection(myConn);
conn.Open();
cmd = new SqlCommand("Select cid, cidfk, sitecode, sitepassword from clientlogins where sitecode=@sitecode", conn);
cmd.Parameters.AddWithValue("@sitecode", siteCode);
lookupPassword = Convert.ToString(cmd.ExecuteScalar());
Session["CID"] = ?
cmd.Dispose();
conn.Dispose();


the clientlogin id is the cid.
the clientid is the cidfk.

client table
CID = 30

clientlogin table
CLID = 1
CIDFK = 30
SiteCode = one
SitePassword = one


So when the client lands on the secure/default page I need to know the CIDFK so I can write the client company name on the page; the client company name is in the client table.
lblCID.Text = Session["CID"]; //now I can query the client table with the id
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
fwsteal

ASKER
secure/admin

<authentication mode="Forms">
                  <forms name="SqlAuthCookie" loginUrl="login.aspx" timeout="30">
                        <credentials passwordFormat="Clear">
                              <user name="me" password="me"/>
                        </credentials>
                  </forms>
            </authentication>

            <authorization>
                  <allow users="*" />
                  <allow users="me" roles="Admin"/>
            </authorization>

      <location path="secure/admin" >
            <system.web>
                  <authorization>
                        <!--<allow roles="Admin" />-->
                        <deny users="*" />
                  </authorization>
            </system.web>
      </location>

the user me can not access the secure/admin folder coded this way. I have to uncomment:
<!--<allow roles="Admin" />--> for it to work.
fwsteal

ASKER
i don't expect this to be done in a day; it has taken longer than a day since I began trying this.really appreciate your help.
ASKER CERTIFIED SOLUTION
Edwin_C

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
fwsteal

ASKER
yes, i forgot to add the <allow users="me" /> to the secure/admin location.

after the client authenticates, I'd like to generate a session variable with the cidfk so I can access it in the secure folder

code where i auth the client
conn = new SqlConnection(myConn);
            conn.Open();
            cmd = new SqlCommand("Select sitepassword from clientlogins where sitecode=@sitecode", conn);
            cmd.Parameters.AddWithValue("@sitecode", siteCode);
            lookupPassword = Convert.ToString(cmd.ExecuteScalar());
            cmd.Dispose();
            conn.Dispose();

code where i'd like to generate the session variable:
protected void btnLogin_Click(object sender, EventArgs e)
    {
        if (ValidateUser(txtSiteCode.Text, txtSitePassword.Text))
        {
            //create a session variable from sitecode to store cidfk

how is that handled in .net?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
fwsteal

ASKER
I also updated:

            conn = new SqlConnection(myConn);
            conn.Open();
            cmd = new SqlCommand("uspGetSitePasswordBySiteCode", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@sitecode", siteCode);
            lookupPassword = Convert.ToString(cmd.ExecuteScalar());
            cmd.Dispose();
            conn.Dispose();

to avoid sql injection
Edwin_C

conn = new SqlConnection(myConn);
conn.Open();
cmd = new SqlCommand("Select cidfk from clientlogins where sitecode=@sitecode", conn);
cmd.Parameters.AddWithValue("@sitecode", siteCode);
Session["CID"] = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
cmd.Dispose();
conn.Dispose();
fwsteal

ASKER
here is what i did:

    protected void btnLogin_Click(object sender, EventArgs e)
    {
        String strUserId = txtbxSiteCode.Text.ToString();
        String strPassword = txtbxPassword.Text.ToString();

        if (ValidateUser(strUserId, strPassword))
        {
            SqlCommand cmd = new SqlCommand("uspGetCIDFKBySiteCode", new SqlConnection(myConn));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SiteCode", strUserId);
            cmd.Connection.Open();
            Session["CIDFK"] = Convert.ToString(cmd.ExecuteScalar());
            cmd.Connection.Close();
            cmd.Connection.Dispose();
            FormsAuthentication.RedirectFromLoginPage(strUserId, false);
            Response.Redirect("audit/default.aspx");
        }
        else
        {
            Response.Redirect("login.aspx", true);
        }
    }
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
fwsteal

ASKER
i just don't where to assign the pts since you've helped out so much.
fwsteal

ASKER