[sims] Dropping support for Direct SQL data extracting

[RLR]

I just had a phone call from our Hosted SIMS supplier (Scomis) and have said that Capita have announced they will no longer allow Data Extraction from SIMS using Direct SQL.

 

Does anyone know which products might use Direct SQL to get the data? We have a few 3rd party products that run on servers but not sure what it might use Direct SQL. Examples: Wisepay, schoolpost, salamander.

[PhilNeal]

It's never been supported. Some suppliers may have ignored that.

[Jamman960]

Wisepay uses GroupCall exporter which pulls everything out via reports, SchoolPost pulls straight from sql. The vast majority of apps seem to use groupcall in my experience - MLS, Show my homework, Doddle, Wisepay do at least.

 

 

Phil: is Sql extraction going to be blocked in some way? and will it affect onprem servers? We’re not planning to move away from SchoolPost anytime soon, I’ve always understood that it’s ok so long as it’s not writing to the server in which case all support would be invalid.

[bigdannyb79]

Though shalt not get your data our of your MIS . . .

[PhilNeal]

I'm no longer an operational director so I can't be sure but I don't think it is possible to block direct SQL on your servers - it isn't supported by Capita and there are legitimate ways for third parties to access the data. By allowing direct SQL calls you are by-passing all permissions in SIMS and it isn't something I would do.

[nicholab]

I'm no longer an operational director so I can't be sure but I don't think it is possible to block direct SQL on your servers - it isn't supported by Capita and there are legitimate ways for third parties to access the data. By allowing direct SQL calls you are by-passing all permissions in SIMS and it isn't something I would do.

 

But at some stage if you migrate MIS you must need to be able to export all data that it yours out of SIMS? I do not like this kind of behavior and I would suggest that it is abusing their dominant market position if this is all true.

[Jamman960]

I'm no longer an operational director so I can't be sure but I don't think it is possible to block direct SQL on your servers - it isn't supported by Capita and there are legitimate ways for third parties to access the data. By allowing direct SQL calls you are by-passing all permissions in SIMS and it isn't something I would do.

 

Thanks Phil,

 

I suspected that would be the case unless Capita were to implement some form of monitoring app/agent outside of MS SQL server. I hadn't really thought about the ignoring permissions side of things.

 

I think its fair enough for Capita to say they don't support this method of data extraction, putting technological methods in to stop it would be another matter though although I imagine they could do it under the guise of data integrity as anything capable of extracting data via direct SQL could easily change data with a minor query tweak. Its not like there aren't other ways to pull the data via commandline reporter etc

[RLR]

It's our Hosting provider that has agreed with Capita that this method should not be allowed:

 

https://faq.scomis.org/kb21287/

 

Will have to contact our 3rd party vendors to find out how they're extracting the data.

[PhilNeal]

But at some stage if you migrate MIS you must need to be able to export all data that it yours out of SIMS? I do not like this kind of behavior and I would suggest that it is abusing their dominant market position if this is all true.

 

If you are moving from Capita the fact that direct access is not supported is not an issue as you won't want Capita to manage your data any more. The approach taken that requires access via APIs is normal if you want to be supported!!

[vikpaw]

Thanks Phil,

 

I suspected that would be the case unless Capita were to implement some form of monitoring app/agent outside of MS SQL server. I hadn't really thought about the ignoring permissions side of things.

 

I think its fair enough for Capita to say they don't support this method of data extraction, putting technological methods in to stop it would be another matter though although I imagine they could do it under the guise of data integrity as anything capable of extracting data via direct SQL could easily change data with a minor query tweak. Its not like there aren't other ways to pull the data via commandline reporter etc

 

Data integrity is one thing, and also it will help with GDPR compliance. Particularly if it's hosted off the school site.

I think it makes sense to do this. All companies should be properly registered as third-party partners and use supported legitimate methods, i.e. the API (e.g. Salamander) or Reports (e.g. Groupcall).

Schools also should not want this method because you have to be really careful to ensure you are aware of what is accessible and how it's extracted.

For Engage, one of the methods this is done is to create views/stored procedures and create an SQL user that can only access those.

But at some stage if you migrate MIS you must need to be able to export all data that it yours out of SIMS? I do not like this kind of behavior and I would suggest that it is abusing their dominant market position if this is all true.

 

I think it's fair to do this to protect data and system integrity. The issue is when you do want to move away, they should allow you to take it all and ideally assist in extracting it in formats you require. It would not be fair if the system you are moving to was expected to become a partner or have to integrate with the API, get their heads around reports. The data is yours and you have a right to get it all, however, you don't need this level of exposure permanently open.

 

I'd hope for new contracts this sort of thing would be covered.

[SimonHooker]

I work on a third party product which extracts data from SIMS using direct SQL. I thought I'd just post up our point of view on why we do this instead of "approved" methods. The opinions below are my own and do not reflect that of my employer.

 

Where possible when integrating with a new MIS we always look for an API route. With Progresso we have only ever had API access, likewise Schoolbase. iSAMS started out as being direct SQL only, however following cooperation from iSAMS we have almost completed a full migration to be able to get the same data points via their APIs. This is great. When working with Capita we were asked to pay £thousands and in return we got nothing usable. In short we were told that to access the APIs we would need to pay £thousands more and then maybe we'd get something. This is a simplification, but in short from my position as a developer it felt like a con.

 

As a result, in order to get sufficient data to power our product we require SQL access. We work with schools which are able to provide this access. It does mean that we cannot work with schools where the SIMs database is externally hosted. It also means we have to work to ensure that we do not get data which we should not have. Additionally I always request repeatedly that we are never ever ever given any write-access. We do not need the ability to modify data or structures, so all we ever request is read-only access.

 

We accept that there is the very real likelihood that over time database structures will change as SIMS updates, and that we will get no warning nor support about this. This is fine. We are aware of that and work with that expectation. Additionally we are not a competitor for the SIMS MIS and so our needs to access the data are to help schools get more out of their MIS instead of supplanting it and so cooperation from the MIS would be in everyones interests. Ultimately it means that our integration with some MIS' are better than others due to cooperation from the development teams involved.

 

In an ideal world I would love nothing more than to be able to drop direct SQL access, but whilst Capita have such a high paywall in front of getting any information regarding their API that will be impossible. Without paying £thousands I do not have any way of confirming what APIs exist, how to use them, what data points they return, and what can be done if a specific datapoint which we currently rely on is no longer available.

 

It just feels like Capita trying to prevent anyone without a huge budget to burn being able to help schools. There's not a lot I can do about it, so we do the best we can.

Edited May 24, 2018 by SimonHooker

[David_Grashoff]

Hi Simon, feel free to contact me at [email protected] so we can provide you with an update on our partner engagement.

[matt40k]

Hey @SimonHooker - have you looked at Assembly ?

[SimonHooker]

Hi @matt40k - we have not.

 

We have previously looked at other similar products though without success. There's always so far a stumbling block, usually in compromises as each attempts to normalise the data. The problem is that we also normalise data and so it's easy for data to be corrupted between MIS -> extractor -> us. If it's just MIS -> us then we are able to debug and fix that. If there's the step in between then we are left debugging whether the issue is between MIS and extractor, extractor and us, and then potentially having to request changes in the extractor as well as the MIS.

 

I'll put a task into our to-do pile to take a look at Assembly though - what is your relationship with Assembly?

[matt40k]

I've got zero connection with Assembly. It's company that's formed off Ark - who's one of those names that keep cropping up when it comes to innovation in the education space. Highly recommend speaking with Joshua @ Ark if you're doing anything with data and education.

[SimonHooker]

I've got zero connection with Assembly. It's company that's formed off Ark - who's one of those names that keep cropping up when it comes to innovation in the education space. Highly recommend speaking with Joshua @ Ark if you're doing anything with data and education.

 

Cool will have to do that. Just was curious whether you'd actually used Assembly at all - many of the similar products look good on paper but then seem to have shortcomings as mentioned! That's not to say that there will never be one that can do what we would like, and maybe Assembly will be a good fit

[matt40k]

Ahhh yer can't say I've heavy used it to find those (weird) bugs. My main positive things about it are that they are driven to improve it, they want it to succeed and its cheap! No massive paywall JUST to get started.

[RLR]

I probably used the wrong wording for this. It's not that SIMs are dropping support for direct SQL access. It's our hosting provider that doesn't want 3rd party providers using it for security reasons which is understandable. I don't think SIMS has ever supported it as they would rather people use the available APIs. I emailed round our suppliers and found Schoolpost are using direct SQL. They are already in the works for setting up Wonde which we'll move over too once it's ready.