Radius look ups in external MS SQL DB

[CAWJames]

Morning all,

 

I am thinking about setting up RADIUS for a)our Wifi with a captive portal and b) so I can also have a portal on the PC's for anyone who doesn't pass SSO to authenticate before they use the internet.

 

Now the rub, We keep all our student's login details in an MS SQL database, usernames, hashes etc, so no student AD.

 

Is there anyway something like FreeRADIUS can look up those details in the MS SQL DB and authenticate?

 

I realise the password hash may be a can of worms, but need to see if this idea is a goer before I start looking more into it

 

Don't know much about RADIUS, so asking for your learned help

 

Thanks

 

James

[CAWJames]

Anyone got any insight into this? All I can find out is that I can use MSSQL as a database for RADIUS, but can I cross-link tables from another different database to pull over usernames and password hashes?

 

 

Thanks

[andydis]

To Confirm you have no active directory server?

then how do your students login to a pc?

 

if not then your wifi provider may support you querying a DB from a portal.

[CAWJames]

We have AD, but only for staff, the students are in a bespoke MIS system with an MSSQL backend. Currently the students log in with a generic student ID and access their VLE with the same backend as the MIS, everything is web based. This presents the problem in terms of safeguarding,PREVENT,etc, as the furthest we can track is the PC name and rely on the lecturer to know who was sitting where. We are a land based college and don't have any problem students hellbent on bad things(that we know of).

 

Every student also gets issued with an O365 credential as well, but using that would involve Azure MFA and its subs.

 

I envisage a system where the students use either their vle credentials or if someone has a bright idea, their O365 credentials, to auth to the wifi AND/OR I implement an "authwall"/portal on every web connection, that makes them login before they can use any web based services. Both options need some-kind of auth back end, and I want something zero admin, that syncs with our current back ends.

 

The other option of course, is we sync back the O365 credentials into on-prem AD, but I don't know if that's possible with cloud only accounts. OR I move them all on-prem keeping their O365 link, and then use standard AD link to routers and wi-fi

 

Lots of OR's, my head hurts!! When I started a mere 5 years ago, we had no wifi, and staff with laptops unplugged a computer and plugged their laptop in!! Now we have hundreds of netbooks and allow students to use their own devices, becoming a nightmare!

 

Thanks

 

James

Edited September 7, 2016 by CAWJames

[andydis]

CAW I'm sorry but I cant help you any further as I dont "trust" your setup you have .

For example, there is no way with "generic" logins you can be recording or tracking internet use-age correctly ,

You may want to look at just MAC filtering as opposed to RADIUS, or speak with your wifi provider/installer to see if it supports RADIUS from DB.

[pcstru]

I think if I was trying to do this I'd look at pfsense's captive portal, which has a local user database option I could tap into. That way I'd just have to write a script (or two) to export from your MS SQL user tables into the pfsense tables. It would need some configuration at the network side (routing/VLANs) but I'm not at all clear how your staff and students are logging into the PC in the first place and what is authenticating that (local or if not what), so I might be barking AND up the wrong tree.

[CAWJames]

I am well aware we are not tracking internet usage properly! This is why I am trying to find alternative ways of doing this that fits in with our current way of working. I would like to move away from a generic untrusted login, but tracking the browser is our option at the moment.

 

Thanks for your input

 

James

[CAWJames]

Thanks pcstru, the staff log in with AD, everything tracked on the SonicWall with GMS, the students log in with generic student AD account's one for each of our 4 centres. So currently we can track staff internet useage, and use CFS filters to block the usual, and the student we can track to generic login and computer, so the lecturers know who was sitting where and we can basically track it like that. I know that isn't enough, so captive portal is probably our way forward and then, as you say, make them login to the browser so we can track to actual student. We are changing our firewalls early next year on their 3 yearly cycle, so one of the prereq's will be ability to integrate and track students, which is where I am coming from with my original question. I think I am going to have to move the students into AD, so the firewall can use those credentials to track.

 

Thanks

 

James

[pcstru]

I think I am going to have to move the students into AD, so the firewall can use those credentials to track.

If you can do that, then IMO it would be the best way. I'd be reasonably confident that I could hack pfsense and a separate DB with user credentials together with fairly minimal effort (2 scripts max) so it might fit the bill as a solution but if you can use AD then you can skip such hacks altogether.