private int CheckStaffNameExist(string displayName, int staffNameId, int staffId)
{
RunStoredProcedure rsp = new RunStoredProcedure();
// check if staff name exists in db
int exist = rsp.StoredProcedureReturnInt("Proc_CheckStaffNameExist", "staffName", displayName, "count");
// if staff name exists, activate the correct staff name from db
if (exist > 0)
{
// activate staff name on logged in user
rsp.StoredProcedureUpdateBool("Proc_UpdateStaffName_Varchar", "active", true, "staffName", displayName);
// get staff name id
staffNameId = rsp.StoredProcedureReturnInt("Proc_GetStaffNameId_StaffName", "staffName", displayName, "staffNameId");
}
// staff name does not exists in db, create a new staff name id and make it active
else
{
// add a new staff name and return staff name id
staffNameId = rsp.StoredProcedureInsertRow("Proc_AddStaffName", "staffName", displayName, "staffNameId");
}
// update staff name id in staff table
rsp.StoredProcedureUpdateInt("Proc_UpdateStaffNameId", "staffNameId", staffNameId, "staffId", staffId);
return(staffNameId);
}
// upon login, check staff details and update necessary details
private void Staff(string displayName, string group)
{
// check whether the staff logged in has the same credentials compared from the previous credentials logged in - if not, update db
RunStoredProcedure rsp = new RunStoredProcedure();
var staffId = 0;
var staffNameId = 0;
try
{
staffId = rsp.StoredProcedureReturnInt("Proc_GetStaffId", "username", txtUsername.Text, "staffId");
}
catch
{
// set staffId to zero to add the staff details to db
staffId = 0;
}
// if staff exist in the db - next step is to compare from previous login and update staff details
if (staffId != 0)
{
staffNameId = rsp.StoredProcedureReturnInt("Proc_GetStaffNameId_Staff", "username", txtUsername.Text, "staffNameId");
string staffName = rsp.StoredProcedureReturnString("Proc_GetStaffName", "staffId", staffId, "staffName");
// staff name is different from ad compared to db
if (!displayName.Equals(staffName))
{
// disable active staff name on logged in user
rsp.StoredProcedureUpdateBool("Proc_UpdateStaffName_Int", "active", false, "staffNameId", staffNameId);
// add or update staff name from db
staffNameId = CheckStaffNameExist(displayName, staffNameId, staffId);
}
}
// staff does not exist in db - create a new staff id
else
{
// add or update staff name from db
staffNameId = CheckStaffNameExist(displayName, staffNameId, staffId);
// add a new staff name and return staff name id
staffId = rsp.StoredProcedureInsertRow("Proc_AddStaff", "staffNameId", staffNameId, "username", txtUsername.Text, "staffId");
}
UserCredentials.StaffId = staffId;
UserCredentials.StaffNameId = staffNameId;
// upon login, update user's role and group access
UpdateStaffDetails(group, staffId);
}
public string GetGroups(string username)
{
DirectorySearcher search = new DirectorySearcher(path);
search.Filter = "(cn=" + filterAttribute + ")";
search.PropertiesToLoad.Add("memberOf");
StringBuilder groupNames = new StringBuilder();
using (HostingEnvironment.Impersonate())
{
try
{
SearchResult result = search.FindOne();
int propertyCount = result.Properties["memberOf"].Count, equalIndex, commaIndex;
string dn, grp;
for (int propertyCounter = 0; propertyCounter < propertyCount; propertyCounter++)
{
dn = (string)result.Properties["memberOf"][propertyCounter];
equalIndex = dn.IndexOf("=", 1);
commaIndex = dn.IndexOf(",", 1);
if (-1 == equalIndex)
{
return(null);
}
grp = dn.Substring((equalIndex + 1), (commaIndex - equalIndex) - 1); // Retrieves the Group Name in Active Directory
if (grp.Contains("MRBanking") || grp.Contains("CUBanking")) // if group name contains the following conditions, add it to group names and append a back slash
{
groupNames.Append(grp);
groupNames.Append("|");
}
}
groupNames.Append(filterAttribute); // append the user's display name after the list of groups
}
catch (Exception ex)
{
// exception existed - user must've been using the local db
// get user group access from local db
RunStoredProcedure rsp = new RunStoredProcedure();
string group = rsp.StoredProcedureReturnString("Proc_GetGroup", "username", username, "group");
if (!string.IsNullOrEmpty(group))
{
// get staff id and staff name respectively
int staffId = rsp.StoredProcedureReturnInt("Proc_GetStaffId", "username", username, "staffId");
string staffName = rsp.StoredProcedureReturnString("Proc_GetStaffName", "staffId", staffId, "staffName");
// add group access value with backslash delimiter - last value is staff name
groupNames.Append(group);
groupNames.Append("|");
groupNames.Append(staffName);
}
else
{
throw new Exception("Error obtaining group names. " + ex.Message);
}
}
return(groupNames.ToString());
}
}
public bool IsAuthenticated(string domain, string username, string password)
{
string domainAndUsername = domain + @"\" + username;
DirectoryEntry entry = new DirectoryEntry(path, domainAndUsername, password);
using (HostingEnvironment.Impersonate()) // Provides application-management functions and application services to a managed application within its application domain. Impersonates the user represented by the application identity.
{
try
{
DirectorySearcher search = new DirectorySearcher(entry);
search.Filter = "(SAMAccountName=" + username + ")";
search.PropertiesToLoad.Add("cn");
// we were having issues with search.FindAll() method listed below and it takes 15 seconds to load
// below is the error message that is displayed
// ExtendedErrorMessage = "8009030C: LdapErr: DSID-0C0904DC, comment: AcceptSecurityContext error, data 52e, v1db1"
// possible solution reference : https://social.technet.microsoft.com/Forums/windowsserver/en-US/2786da89-3dc7-43d9-8a75-3db54825ff36/solved-ldap-authentication-error-code-49-80090308-comment-acceptsecuritycontext-error-data?forum=winserverDS
// solution implemented: create an exception for local users not found in active directory
// reference: https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/exceptions/creating-and-throwing-exceptions
if (username.ToLower().Equals("malb"))
{
throw new SystemException("user is not found in active directory");
}
foreach (SearchResult result in search.FindAll())
{
if (null != result)
{
path = result.Path;
filterAttribute = (string)result.Properties["cn"][0]; // Picks up the display name from Active Directory
break;
}
}
}
catch (Exception ex)
{
// check whether the username exist in the database
RunStoredProcedure rsp = new RunStoredProcedure();
int exist = rsp.StoredProcedureReturnInt("Proc_CheckStaffExist", "username", username, "count");
// if statement to check whether the username exist in the database
if (exist > 0)
{
string encryptedPassword = rsp.StoredProcedureReturnString("Proc_GetPassword", "username", username, "password");
string decryptedPassword = rsp.DecryptPassword(encryptedPassword);
// if it is false, check if there is any password stored and match if exist return true
if (string.Equals(decryptedPassword, password))
{
return(true);
}
else
{
return(false);
}
}
else // else throw the exception
{
throw new Exception("Error authenticating user. " + ex.Message);
}
}
return(true);
}
}
