Direct access to Microsoft SQL Database?

Epicor ERP 10
1

Hi All,

We are preparing to upgrade to E10 and we have a few technical people in house that are excited to get an SQL database so they can write small web apps to directly pull data out of the DB. Currently we use a Progress DB and we export some CSV reports for them, so simple web apps can parse the data and display it. We have several people that won’t use Epicor but need access to the data to do their jobs more effectively so these web apps allows them to do this(e.g. BOM reports that show qty and cost info).

I’ve been pretty agressive we can not give write access to the SQL DB, but I’m also a little leary of read access. With REST APIs now available, I’m of the opinion this is what we should be using even for read requests(our Epicor devs currently use SOAP APIs - we have the expertise in house). SQL is a lower barrier of entry than REST APIs, and that’s what scares me. I’m a little worried about potential problems that could result(e.g. could they lock a table as part of a read and cause performance problems for Epicor clients?). For context we’ve been running Epicor 9 and have a long history of performance problems and strange weird issues we don’t fully understand, so I’m a bit paranoid to lock down E10 so we ideally have a faster and more reliable system.

Am I being paranoid with arguing against giving these technical people read access to our Microsoft SQL DB? If not please help me to understand what types of problems can result so we have a better understanding.

2

Wide open Read-Only access to all tables would be something I would be concerned about as well. I would recommend the REST API as it respects the rights the user has in Epicor, and ensures you only need to maintain security in Epicor, rather than also maintaining it on a Schema level in SQL.

6 Likes
3

There’s nothing inherently wrong with allowing database querying, especially if they are technical enough to be doing so. You’d know more than I would about that.
The problem comes in two flavors. If you are UPDATING/CHANGING records on Epicor tables from SQL, you’re playing with fire. If you’re simply querying, it’s different.
Secondly, if they are intent on developing apps that go after the SQL tables directly, it should be carefully considered why they are doing that over REST API endpoints. Direct SQL bypasses the business logic layer whereas REST/SOAP does not.

4

REST all the way I’d stay away from SQL all together. If they want to run queries use baqs through rest

7 Likes
5

Thanks @josecgomez . The counter argument I’m hearing is why not empower them, as they’ll build more tools that bring value by giving them read access to SQL. What problems could result or what’s the downside to giving them access?

6

All your security would be moot if you go straight to SQL. Going with REST ensures that only the right people have acceas to the right data.

They would have to create and entire custom security framework which is not easy task to do right. You get this for free if you go with REST and there is almost nothing I can think of you can do with SQL that can’t be done with REST

3 Likes
7

@josecgomez what if we’re OK with the security risks - we trust them and if they wanted we’d give them pretty much full access to Epicor. Are there any other concerns?

Is it possible they can cause problems for other users via buggy code when reading?

8

As long as they are doing read only the risks are minimal… A poorly written query can bring your server to it’s knees but it takes a lot to do that.

I don’t see the downside of using REST though. What can’t they do in REST that they can do in SQL?

1 Like
9

@josecgomez There argument is REST adds unnecessary complexity. To be honest, I think they lack experience w/ REST so what they are really arguing for is the technical barrier to be as low as possible.

10

sounds like a learning opportunity to get better at this integration technique :slight_smile:

3 Likes
11

Sounds like a who moved my cheese issue. There’s nothing bad with SQL you are giving up security and a proven framework but it’ll work

1 Like
12

@josecgomez thanks that’s what I was after. It sounds like if we understand they will bypass security(which I think we’re OK with) then it should be fine to give direct read access to SQL. Sounds like I may have been being a bit overly cautious, thanks for helping me to better understand the risks :slight_smile:

13

@Aaron_Moreng thanks so much for your feedback. I feel like I have a better understanding of the risks now!

1 Like
14

Over the years I’ve been in IT: structured programming, object oriented programming, relational databases, client-server programming, network programming, and privacy/security have all added “unnecessary complexity”.

If you don’t know it by now, you will. I’m a bit of a jerk about going directly to the database. Why? I listen to Epicor. The E10 application is faster than E9 because the system is written to take advantage of caching. What does this mean to a read-only user? It means that the database may not have the most up-to-date information in it. Your guys might be returning stale data in their apps. So I will argue that there IS something inherently wrong with direct database querying.

At a minimum, they should use the Business Objects so at least those get the most recent data since they read from the cache.

They are used to parsing CSV files today. Calling a BAQ from the BAQSvc has got to be the easiest solution ever. There is just no magic to it. Seriously, what’s difficult about:

https://server/epicor10/api/v1/BaqSvc/{BAQName}?param='2016-12-01'

which returns data in tabular form with no parsing required? That’s got to be the lowest technical barrier I’ve ever seen.

Know what else is cool with REST? Upgrades. If you do things the same way as 9.05 and direct access, it’ll be another five years before you upgrade again. With business object programming, you’ll have to recompile your programs with every upgrade. With the BAQ REST service, you may never have to recompile that program again.

I also like the finer control of security that you will already have in the application at your disposal instead of recreating a whole new model. Which of course, they probably won’t. They’ll use one or two users and open everything wide open - because of the “unnecessary complexity”. Also, at least at this version of Epicor, creating VIEWs directly in the database has caused several users on this list to crash the upgrade process.

And finally, so much of the programming done today involves REST. Microsoft has exposed all of O365 as REST services. Most of the new apps in O365 are just REST orchestrations. For those who want to integrate Epicor into the rest (oddly, no pun intended) of the world, I would highly recommend expanding one’s horizons.

(How was that @markdamen?) :smirk:

Mark W.

9 Likes
15

REST v SQL? Show me all employees hired on in 2013 that have last names that end in the same first three letters of any active customer with orders more than $10k and in any 3 month span since company inception.

But seriously, it’s trivial to access sensitive info – direct access puts another layer of risk you need to consider with compromised accounts, more so if you’re a public company. Don’t be a headline. J

- Steve

16

BAQ + REST == DONE :slight_smile:

Also that’s a very weird report LMAO

3 Likes
17

A little bit of devil’s advocate: Are BAQs in E10 better than E9? I ask as we’ve had what I thought were some pretty basic queries that multiple consultants told us couldn’t be done as we were trying to group more than three things(e.g. show all products released in the last 3 years with sales data by quarter where the class is not x,y, or z). Not only did they say it wasn’t possible but watching the level of complexity as they tried to implement it was crazy(we need a cube to group things and we need multiple executive business queries that feed into it, and 45 minutes later someone who does this professionally for years said it couldn’t be done). In SQL we could have implemented it in 15 minutes(to be fair we have experience with SQL and BAQs are still new to us).

18

WAAAAAAY better. E10 has subqueries and has exposed standard “sub query” types like UNION, INNER, and CTE along with TOP and OFFSET modifiers.

Mark W.

2 Likes
19

A little bit of devil’s-devil’s advocate:

15 minutes to develop queries that might return stale data, add weeks or months to upgrades, and bypass security?

Are you really saving time here? If it were me, I would at least try building some BAQs in the Education Database so at least you’re making an informed decision.

:smiling_imp:

1 Like
20

Eddie,

A few thoughts. My background is in web development with lots of SQL, SOAP & Rest situations. About two years ago I also began working part-time on Epicor. The transition has been painful for me… so much complexity that I wasn’t used to. My usual solution consisted of writing some SQL, solving the issue, and moving on.

Over time, however, the shaming from @Mark_Wonsil gave me pause. His suggestion to avoid direct SQL interaction as much as possible (always?) was solidified when I led the upgrade from E9 to E10.2. That process helped me realize that forcing myself to stay in Epicor’s ecosystem as much as possible will ultimately make for much easier future upgrades.

Also, Epicor has a fairly aggressive upgrade schedule once you get to 10. The more you can hook external apps to their framework the better. Rest is not the much different from SOAP, so I’m surprised to hear that you are getting push back on that.

As for BAQs in 10, waaaaay better is right. Subqueries have solved all of my issues so far, even though they sometimes take a bit of work to get what I want them to do.

Finally, Epicor seems to be going in the direction of utilizing Angular JS (from what I know, starting with the rewrite of Active Home Page, then to MES). Because of this, your team could take a proactive approach and familiarize yourself with that framework and align custom development to match the core framework to come.

3 Likes