2. ASPRunner.NET General questions
  3. [SOLVED]  Database security with Windows authentication
This topic is locked
[SOLVED]

 Database security with Windows authentication

 		5/10/2016 2:11:39 PM
ASPRunner.NET General questions
Pete K author

This is a very common scenario that I used to use all the time with apps built on Iron Speed: I want to use database security to take advantage of the flexibility it gives, but let Windows do the authenticating so my users can have a single sign-on. I know that going full-on Active Directory is an option, but there are some problems associated with that in our enterprise that I would prefer to avoid if possible.
The strategy I used in Iron Speed was to disable anonymous authentication in IIS, then add custom code to the app before the login event to check the value of

[color="#383838"]

[font="gotham, helvetica, arial, sans-serif"]
Admin 5/10/2016

You can find some ideas in this article:

http://xlinesoft.com/asprunnernet/docs60/implementing_single_sign_on_feature.htm
Pete K author 5/11/2016

Thank you for the reply, Sergey. That topic is not in the help file for the current version (8.1). Just wanted you to know that I did look. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=79336&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' />
This does achieve part of what I want to do. It gets me the Windows user name. The part that I am struggling with is, how do I get my app to authenticate that user against the user table?
Admin 5/11/2016

We removed this topic mostly because there is Active Directory support was added that does much more and we didn't want people to be confused.
I guess you can extend this code by executing a SQL query against users table to see if such username exists. If it does you can proceed with populating session variables and redirecting user somewhere.
Pete K author 5/17/2016



We removed this topic mostly because there is Active Directory support was added that does much more and we didn't want people to be confused.
I guess you can extend this code by executing a SQL query against users table to see if such username exists. If it does you can proceed with populating session variables and redirecting user somewhere.


I was thinking along those lines, but I wasn't sure about everything that needs to be done to ensure that the user is properly logged in. I want to make sure that everything works as expected. So you're saying that the ASPR generated code basically just does those three things then: verifies the user exists and checks the password, sets the appropriate session variables, and redirects the user.
Is that it?

Pete K author 6/7/2016

I realize that most people will go with the AD solution, but this my only option for various reasons having to do with our unorthodox AD setup and also to the fact that I want my users to be able to mange user groups and we are not able to grant them access to AD users and group administration. But just in case someone else reads this and is curious, I do have it working.
Please note that the following code is specific to my setup. Because we don't allow guest access to our apps, all actual data access pages are restricted to user groups than guest. But because I wanted to provide a nice page to which to direct failed logins, I set up an entry in a table called "Page" which is sort of like a very simplified CMS built into my app, providing ad hoc pages on which to display information, help, etc. (H/T to Jerry - jadach for that idea). Anyway, as you can see, I log in failed attempts as guest for that purpose. The session variable "DynMessage" is part of what gets displayed on that page.
Here then, is my code:

// Single sign-on code:
if (MVCFunctions.GetRemoteUser())

{

string RawAuth = MVCFunctions.GetRemoteUser();

string UserID =RawAuth.Replace("FAYETTE\\", ""); //Strip out our domain name

dynamic thisUser = GlobalVars.dal.Table("Portal_users");

XVar rs = thisUser.Query("username='" + UserID + "'", "");

XVar data = CommonFunctions.db_fetch_array(rs);
if(data) // user in database

{

if (data["active"])

{

XSession.Session["UserID"] = UserID;

XSession.Session["AccessLevel"] = Constants.ACCESS_LEVEL_USER;

XSession.Session["UserName"] = data["FullName"].ToString();

XSession.Session["GroupID"] = data["groupid"].ToString();

MVCFunctions.HeaderRedirect("menu");

}

else // user in database but inactive - log in as guest so we can view the info page

{

XSession.Session["DynMessage"] = "User " + UserID + " is marked as INACTIVE. ";

XSession.Session["UserID"] = UserID;

XSession.Session["AccessLevel"] = Constants.ACCESS_LEVEL_GUEST;

XSession.Session["UserName"] = data["FullName"].ToString();

XSession.Session["GroupID"] = "guest";

MVCFunctions.HeaderRedirect("page","view","editid1=not-authorized");

}

}

else // user not in database - log in as guest so we can view the info page

{

XSession.Session["DynMessage"] = "User " + UserID + " not found. ";

XSession.Session["UserID"] = "Unknown";

XSession.Session["AccessLevel"] = Constants.ACCESS_LEVEL_GUEST;

XSession.Session["UserName"] = "Unknown User";

XSession.Session["GroupID"] = "guest";

MVCFunctions.HeaderRedirect("page","view","editid1=not-authorized");

}

}
jadachDevClub member 6/7/2016

Thanks for sharing this. I am going to try and see how it works. Nice job Peter.
Pete K author 6/8/2016



Thanks for sharing this. I am going to try and see how it works. Nice job Peter.


Thanks Jerry. One thing I have not yet figured out how to do is hook into the action of the Logout button so I can actually log the user out. Maybe you can figure that one out. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=79528&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' />
Pete K author 6/8/2016



Thanks Jerry. One thing I have not yet figured out how to do is hook into the action of the Logout button so I can actually log the user out. Maybe you can figure that one out. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=79530&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' />


I got the logout button to work when I noticed that it directs to the loginpage with the querystring "a=logout". So adding the following code to the start of my single sign-on code works:


if (MVCFunctions.postvalue("a") == "logout")

{

XSession.Session["UserID"] = "";

XSession.Session["AccessLevel"] = "";

XSession.Session["UserName"] = "";

XSession.Session["GroupID"] = "";

XSession.Session["UserLoc"] = "";

XSession.Session["Active"] = "";

MVCFunctions.HeaderRedirect("logout.htm");
}


Now I have to figure out how the Log out button on the list page works.