Using VB.NET and SQL Server 2005
In my software I am using login page with UserName and Password. Suppose I am setting a username and password work for 20 days. After 20 days, when I try to login in software, the software should not to allow to login.
Is possible to make a SQL query or VB.NET code.
Can any one provide a sample code or query for the username and password should work for particular days?
Add a column in the member's table that specify an expiration date for the password..
When you query the database on login, you add a condition in the WHERE to check if the current time is not greater that the expiration date...
SELECT * FROM Member WHERE email = 'dfsdfsd' AND password = 'ddada' AND expiration > GETDATE()
This way, user wont be able to log in when the password expires
edit: the way you determine "expiration" is up to you... you could set GETDATE() + 20 on user insertion in the DB
Thanks
Not sure what technology you are using (ASP.NET/Windows forms), but if you are using the Membership Provider, you will find that has a LastPasswordChangedDate that you will find useful.
If you are using .NET 3.5 you can use this in Windows forms by using client application services as well as ASP.NET easily.
You could have a table that says:
CREATE TABLE dbo.Users
(
Username VARCHAR(128),
Password VARCHAR(16),
LastReset SMALLDATETIME
);
(Keeping it simple here, you probably want to store a hash of the password and compare a hash to the entry, rather than store the password in plain text.)
Now, when the user logs in, you check the LastReset column for that user. If it is more than 20 days ago, force them to reset the password, and when they do, update the LastReset column to DATEADD(DAY, 20, CURRENT_TIMESTAMP).
Sounds like you are using ASP .Net's builtin membership and roles functionality. Without modifying any of the existing tables, or extending that functionality, you could create a SQL Server Job that executes the following statement:
UPDATE [dbo].[aspnet_Membership]
SET [IsLockedOut] = 1
WHERE CreateDate <= DateAdd(Day, -20, GetDate())
This will lock out users that were created 20 or more days ago. You will need to have the SQL Server Agent service running to do this. This obviously doesn't provide flexibility to specify a different lockout time for different users. You could easily extend the where clause to exclude certain users from ever getting locked out.
A more flexible (though involved) alternative would be to define a profile property for your expiration date. If you are using the ASP .Net CreateUserWizard control, you could hook in to the UserCreated event and set the expiration profile property to whatever date you wanted. Then, it's just a matter of checking that property when the user logs in by hooking in to the Login control's LoggingIn or LoggedIn event(s). If the current date is greater than the expiration date then cancel the log in, and present the user with a message as to why they were denied.
I'll see if I can put together a sample project.
Hacked this together at lunch. Uses the profile to store expiration. Again, assuming you're using the ASP .Net and Sql membership provider.
Default.aspx
<%@ 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>
<asp:LoginView ID="LoginView1" runat="server">
<LoggedInTemplate>
Thanks for logging in. Your account will expire on:<br />
<asp:Label ID="lblExpiration" runat="server" Text="Label"></asp:Label>
<br />
<asp:LoginStatus ID="LoginStatus1" runat="server" />
</LoggedInTemplate>
<AnonymousTemplate>
You are not currently logged in. Log in or create a new user.<br />
<br />
</AnonymousTemplate>
</asp:LoginView>
<br />
<br />
<asp:Label ID="lblError" runat="server" Text="Label" Visible="False"></asp:Label>
<asp:Login ID="Login1" runat="server" BackColor="#EFF3FB"
BorderColor="#B5C7DE" BorderPadding="4" BorderStyle="Solid" BorderWidth="1px"
Font-Names="Verdana" Font-Size="0.8em" ForeColor="#333333">
<TextBoxStyle Font-Size="0.8em" />
<LoginButtonStyle BackColor="White" BorderColor="#507CD1"
BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em"
ForeColor="#284E98" />
<InstructionTextStyle Font-Italic="True"
ForeColor="Black" />
<TitleTextStyle BackColor="#507CD1" Font-Bold="True"
Font-Size="0.9em" ForeColor="White" />
</asp:Login>
<br />
<asp:CreateUserWizard ID="CreateUserWizard1" runat="server" BackColor="#EFF3FB" BorderColor="#B5C7DE"
BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana"
Font-Size="0.8em">
<SideBarStyle BackColor="#507CD1" Font-Size="0.9em"
VerticalAlign="Top" />
<SideBarButtonStyle BackColor="#507CD1"
Font-Names="Verdana" ForeColor="White" />
<ContinueButtonStyle BackColor="White"
BorderColor="#507CD1" BorderStyle="Solid" BorderWidth="1px"
Font-Names="Verdana" ForeColor="#284E98" />
<NavigationButtonStyle BackColor="White"
BorderColor="#507CD1" BorderStyle="Solid" BorderWidth="1px"
Font-Names="Verdana" ForeColor="#284E98" />
<HeaderStyle BackColor="#284E98" BorderColor="#EFF3FB"
BorderStyle="Solid" BorderWidth="2px" Font-Bold="True" Font-Size="0.9em"
ForeColor="White" HorizontalAlign="Center" />
<CreateUserButtonStyle BackColor="White"
BorderColor="#507CD1" BorderStyle="Solid" BorderWidth="1px"
Font-Names="Verdana" ForeColor="#284E98" />
<TitleTextStyle BackColor="#507CD1" Font-Bold="True"
ForeColor="White" />
<StepStyle Font-Size="0.8em" />
<WizardSteps>
<asp:CreateUserWizardStep runat="server" />
<asp:CompleteWizardStep runat="server" />
</WizardSteps>
</asp:CreateUserWizard>
</div>
</form>
</body>
</html>
Default.aspx.vb
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.lblError.Visible = False
If User.Identity.IsAuthenticated = True Then
'is the user's account expired? if so log them out.
If Profile.Expiration <= Now Then
Me.lblError.Visible = True
Me.lblError.Text = "Your account has expired. Please contact the administrator or create a new account."
FormsAuthentication.SignOut()
Response.Redirect(Request.Url.ToString)
Else
CType(LoginView1.FindControl("lblExpiration"), System.Web.UI.WebControls.Label).Text = Profile.Expiration.ToString
End If
End If
End Sub
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
Dim p As ProfileCommon
p = ProfileCommon.Create(CreateUserWizard1.UserName)
p.Expiration = Now.AddMinutes(2)
p.Save()
End Sub
Protected Sub Login1_LoggingIn(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LoginCancelEventArgs) Handles Login1.LoggingIn
'get the user's profile and check their expiration date
Profile.GetProfile(Login1.UserName)
If Profile.Expiration <= Now Then
Me.lblError.Visible = True
Me.lblError.Text = "Your account has expired. Please contact the administrator or create a new account."
'cancel the login attempt
e.Cancel = True
End If
End Sub
End Class
Web.config
<?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>
<configSections>
<sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
<sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
<section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
<sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
<section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere"/>
<section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
<section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
<section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
</sectionGroup>
</sectionGroup>
</sectionGroup>
</configSections>
<appSettings/>
<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">
<assemblies>
<add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
<add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
<add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
</assemblies>
</compilation>
<pages>
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Generic"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Linq"/>
<add namespace="System.Xml.Linq"/>
<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>
<controls>
<add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</controls>
</pages>
<!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.
-->
<authentication mode="Forms"/>
<!--
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">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
<profile defaultProvider="AspNetSqlProfileProvider">
<properties>
<add name="Expiration" type="System.DateTime"/>
</properties>
</profile>
<httpHandlers>
<remove verb="*" path="*.asmx"/>
<add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
</httpHandlers>
<httpModules>
<add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</httpModules>
</system.web>
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<providerOption name="CompilerVersion" value="v3.5"/>
<providerOption name="WarnAsError" value="false"/>
</compiler>
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<providerOption name="CompilerVersion" value="v3.5"/>
<providerOption name="OptionInfer" value="true"/>
<providerOption name="WarnAsError" value="false"/>
</compiler>
</compilers>
</system.codedom>
<!--
The system.webServer section is required for running ASP.NET AJAX under Internet
Information Services 7.0. It is not necessary for previous version of IIS.
-->
<system.webServer>
<validation validateIntegratedModeConfiguration="false"/>
<modules>
<remove name="ScriptModule"/>
<add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</modules>
<handlers>
<remove name="WebServiceHandlerFactory-Integrated"/>
<remove name="ScriptHandlerFactory"/>
<remove name="ScriptHandlerFactoryAppServices"/>
<remove name="ScriptResource"/>
<add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</handlers>
</system.webServer>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>
Hope that helps. Any questions just ask.