this.Pose() as Expert

Monday, July 25, 2005

Adding auditing capabilities to SqlMembershipProvider

On Friday, it was my turn as speaker at the Community Bootcamp 2005 aka CBC05. One of the topics at hand was "The Provider Model", with a focus on Membership providers. Those are the ones most likely being extended / written from scratch, and we did an exercise in that very area: Extend the SqlMembershipProvider to audit successful and failed logins similar to *nix. The solution I present today is a streamlined solution programmed by Alexander Schoeppl, one of the attendees.

Step 1: Create the table

CREATE TABLE [dbo].[myLoginAuditing](
 [username] [varchar](255) NOT NULL,
 [numberofSuccessfulLogins] [int] NOT NULL,
 [numberofFailedLogins] [int] NOT NULL,
 [lastFailedLogin] [datetime] NOT NULL,
 [lastFailedLoginIP] [varchar](15) NOT NULL)

Step 2: Create the stored procedure

create procedure myLogUserVisit(
  @username as Varchar(255), 
  @success as int,
  @lastfailedLoginIP as varchar(15))
as
   IF ( EXISTS ( SELECT username
                  FROM   dbo.myLoginAuditing
                  WHERE  username = @username ) )
    BEGIN
    if (@success = 1)
    Begin
      update myLoginAuditing set 
        numberofSuccessfulLogins = numberofSuccessfulLogins + 1 
        where username = @username
    End
    else
    begin
      update myLoginAuditing set 
                    numberofFailedLogins = numberofFailedLogins + 1,
                    lastFailedLogin = GetDate(),
                    lastfailedLoginIP = @lastFailedLoginIP
        where username = @username
    end
  END
  ELSE
  BEGIN
    if (@success = 1)
    Begin
      insert into myLoginAuditing (username, numberofSuccessfulLogins, 
         numberoffailedlogins, lastfailedlogin, lastfailedloginip) 
      values (@username, 1, 0, '01.01.1900', '')
    End
    else
    begin
      insert into myLoginAuditing (username, numberofSuccessfulLogins, 
         numberoffailedlogins, lastfailedlogin, lastfailedloginip) 
      values (@username, 0, 1, GetDate(), @lastfailedLoginIP)
    end    
  END

Alexander did a smart thing - he looked at the various aspnet* sp's.

Step 3: Write the Membership provider

The class skeleton looks like this:

public class MyMembershipProvider : SqlMembershipProvider
{
public override bool ValidateUser(string username, string password)
{
}

public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
{
}

public override MembershipUser GetUser(string username, bool userIsOnline)
{
}
}

Initialize is the easy but essential part - we need the connection string name for later:

private string connectionStringName;

public override void Initialize(string name, System.Collections.Specialized.NameValueCollection config)
{
connectionStringName = config["connectionStringName"];
base.Initialize(name, config);
}

Now we can validate the user - well, the base class does that. We only do the auditing part:

public override bool ValidateUser(string username, string password)
{
HttpContext.Current.Trace.Write("ValidateUser:entry");

bool bSuccess = base.ValidateUser(username, password);

string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("exec myLogUserVisit @username, @success, @IP", conn);

cmd.Parameters.AddWithValue("@username", username);
if (bSuccess)
cmd.Parameters.AddWithValue("@success", 1);
else
cmd.Parameters.AddWithValue("@success", 0);

cmd.Parameters.AddWithValue("@IP", HttpContext.Current.Request.UserHostAddress);

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

HttpContext.Current.Trace.Write("ValidateUser:exit");

return bSuccess;
}

Step 4: Set it up - web.config

  <appSettings/>
  <connectionStrings>
    <add name="MyNWind" connectionString="Data Source=cbc05vpc\cbc05;Initial Catalog=Northwind;User=sa;Password=P@ssw0rd"/>
  </connectionStrings>
  
  <system.web>
    <membership defaultProvider="SuperDuperMSProv">
      <providers>
        <clear/>
        <add name="SuperDuperMSProv" connectionStringName="MyNWind" type="MyMembershipProvider"/>
      </providers>
    </membership>

Step 5: View the auditing information - default.aspx.cs

The final "UI" looks like this:

The source code is rather simple:

protected void Page_Load(object sender, EventArgs e)
{
MyMembershipUser currentUser = (MyMembershipUser)Membership.GetUser();

Label1.Text = currentUser.FullName;
string lcConnection = ConfigurationManager.ConnectionStrings["MyNWind"].ConnectionString;

SqlConnection conn = new SqlConnection(lcConnection);

SqlCommand cmd = new SqlCommand("select * from myLoginAuditing where username=@username", conn);
cmd.Parameters.AddWithValue("@Username", currentUser.UserName);

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

GridView1.DataSource = reader;
GridView1.DataBind();

reader.Close();
conn.Close();
}