Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    8

    Unanswered: ACCPAC ERP200 + Pervasive 8.6

    I'm trying to make a sort of lock down for a network with ACCPAC ERP 200 + Pervasive v8.6
    Being not an expert of these product I downloaded some info from their website and learned how ACCPAC is a multi-tier product and Pervasive a full Database Server, fine.
    When doing the tests I realized that the real world was a little different; I found a share on the server pointing to ACCPAC installation being open as "Everyone: Full Control"; this share includes ACCPAC application files, license files and also Pervasive DB files. When I pointed out that this could be a dangerous open security hole (anyone can delete the database files) the people who have done the installation told me that this is the ONLY way to make it work.
    Checking again I found that on the workstations there is a Pervasive Client software who can "talk" trough some network protocol (named pipes?) with a Pervasive service running on the server; this architecture make me think that the client DOES NOT need to directly see the database files on the server; so I did some test; I just removed the permissions from the DB files folders for the domain user that is logged on the client; then I tried to use some Pervasive client tool (PCC?) and I was able to access the database on the server, see the tables and even add records, fine!
    BUT when running ACCPAC, who should access the database through the Pervasive Client (an ODBC driver?), I always got an error (could not access...313).
    So we called the support and after a lot of talking we realized that ACCPAC is NOT using Pervasive Client BUT is actually accessing the database file directly through the files share!!!!!!!!!!!!! How can they call ACCPAC a multi-tier architecture? This is not even a client-server, this is exactly like a MS-Access database: the ODBC driver on every workstation is doing all the job and the server just share the file....
    The support said this will be theoretically "fixed" with the next release ACCPAC ERP 4.5, coming in october 06.
    My guess is that ACCPAC was built a long time ago using some old approach and being a very popular application (many customers around the world) any major update in the software architecture can be very expensive.
    I didn't have the opportunitu yet to see any installation of ACCPACC with SQL Server but, as far as I know this database, I think that there is no way for ACCPAC to directly access SQL Server database files; the only way is to "talk" (TCP1433, named pipes..) with the MSSQL service.
    Anyone knows about this subject? Any suggestion?

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    With Pervasive, there are two main access methods: Btrieve and SQL. Both methods communicate from the client to the server through a proprietary communication format using TCP or SPX. The default setup for PSQL does require the user to have appropriate rights to the file. This is not a new situation and has been the case since 1982 when Btrieve was first introduced. Now,that doesn't mean the the files need to be flagged "Full Control" for "Everyone". If a specific group of people need those files, then they can be added and other users can be removed.
    Beginning with PSQL 8.5, a new authentication/authorization mechanism was introduced that would allow an application developer to remove OS rights from the data files but this typically requires a change to the application to support it.
    Your statement "the ODBC driver on every workstation is doing all the job and the server just share the file." is not accurate. The PSQL ODBC driver sends requests to the server engine which does all of the disk I/O. The same is true for Btrieve requests. In a Client/Server environment with the PSQL Server Engine and PSQL CLient, the client does NOT directly access the data files. The client requests the data through the server engine and the server engine uses the client credentials to verify access rights.
    MS SQL does have the same default behavior as PSQL but a savvy user could use Access or Enterprise Manager or some other ODBC enabled application to access the SQL Server data and cause havoc as well.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    8

    This helps but...

    Thanks Mirtheil for the detailed description of the communication, I liked it:
    The ODBC client talks to the server through some proprietary protocol on TCP or SPX and then the server engine does the actual I/O on phisical files, perfect! This makes a lot of sense for me...except from the fact that you say that the user (I assume you are talking about the user logged on the windows client) needs to have "any rights" on the physical DB files located on the server; considering the way the client communicate with the server I believe that the only windows user who needs to have those rights is the user that is actually running the Server Engine (Pervasive Windows Service); in the installation I've checked this user was "Local System Account" (not even a real user but a sort of system super user).
    As a proof of this I've run a test: user1, a windows user, is logged on the client machine, the Server Engine is running on the server machine as "local system account" and is "managing" the DB files located on a server folder on which user1 doesn't have any permissions, the foder is not even shared; Result: user1 can successfully access the database using a pervasive client tool (PCC): I mean user1 logs on the database using a valid database account with enough "pervasive rights" so, for example, he can see the tables, create new objects, run queries, insert records....whatever the Pervasive accouont he is using is allowed to do.
    This is the way ANY windows service works, not only Pervasive or SQL Server; a service running on any windows machine NEEDS to impersonate a user and, by design, cannot easily impersonate a user logged on another windows machine and calling on a network socket; to do this it would need the username and password to be sent over the communication; but this is exactly what you don't want to do when you are building any client/server product; the goal is to isolate any tier in the archiecture (like the database or the business rules) from the other tier (the user interface).
    This is the only way to implement a safe managed data access service; the example you have done about SQL Server is not accurate as well; with SQL server the end user doesn't need to directly access the database physical files so, like Pervasive, you have to use a client program (ODBC, OLEDB..) and a valid SQL account to see the data; the database security (not windows security) will allow or not the user to see/change any object in the db (tables, views, ...); usually an end user doesn't have SA (System administration) rights so he cannot really create any big mess; actually, on a well designed system, the end users don't even need to logon to the database, they just logon into the application (i.e. ACCPAC ) and perform only those operations featured by the product and enabled for their application account (application security); is the application that connects though a network protocol to the database to read/write data.
    I believe Pervasive is built to enable this kind of software architecture but for some reasons ACCPAC, who is supposed to use Pervasive as its database service, just by-pass everything and make the end user go straight to the database files.
    In fact in the previously mentioned test, while I was able to access the database through PCC, I could not open the company file (located in the same database) in ACCPAC; that's why I believe ACCPAC is just by-passing the ODBC driver and the Server Enginie too; and this behavior has been condifrmed by a SAGE-ACCPAC support technician!

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    First off, is this really an issue? ACCPAC and Pervasive have been working the same way for many years. I know for a fact that Pervasive is used by a large number if "secure" industries including health care, banking, and finance without any problem.
    Second, you cannot "bypass everything and make the engine user go straight to the database files." An application must use a Pervasive engine to read a Pervasive data file. This can be done with either Btrieve or SQL (ODBC, OLEDB, JDBC, etc). Not all applications use or need SQL/ODBC. In most cases, the overhead of SQL/ODBC is not practical. For example, in a data entry screen where one record is inserted at a time, the Btrieve interface will out perform any SQL/ODBC interface (even MS SQL). ACCPAC uses Btrieve for most of its operations. It does not use ODBC for most of the operations it performs. That is what the Sage/ACCPAC support technician confirmed.
    Third, the Pervasive Control Center (PCC) uses ODBC so it's not a fair test of how ACCPAC does things. THe PErvasive FUnction Executor (WBEXEC32.EXE) is a much more accurate test of how ACCPAC does it's Pervasive operations.
    Fourth, if a user has access rights to an MS SQL database problems can occur. This does not mean the user has SA rights but if a disgruntled user has DELETE rights to the database he/she could delete all of the records in the database.
    Why is this an issue?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    8

    This IS an issue

    I don't understand why my security concern is so difficult to get.

    Scenario A: a Windows User running a program needs to have write/read permissions on the database files (btrieve) to make it works; the effect is that even if the database internal security is limiting the Database User to some restricted operations (like: he can see table A but cannot insert record or wathever else) the Windows User can still access the whole database folder and files; so he can eventually open those files using, for example, notepad, change anything and save the stream back ... surely breaking the database; in addition he can steal the business information in it copying the files, delete the files, etc. etc.

    Scenario B: a Windows User is running a program that accesses a database server through some network protocol (i.e. like any application written for SQL Server); even if the Windows User cannot access the actual database files the application works fine; the actual security on data is garanteed by the Database Security; the user, or even better the application logs on into the database using a Database User and will be able to perform only those operations granted the DB user itself; the end user cannot by-pass the DB security.

    Can you see a little difference in the network security level for the above two scenarios? In the first one THERE IS NO WAY to guarantee that an end user cannot play around with the data (files); you can set whatever Database Permissions you want but since the Windows User has read/write permissions on the files he can do what he wants, is so simple.

    I didn't know how Pervasive worked, but know, thanks to you, is very clear and the test I've done makes a lot of sense for me, is just demostrating that ACCPAC, although is advertised as a multi-tier system, is actually not even a client-server; maybe this is the reason why they are soon releasing a new version (5.4) that will fix this security hole; the current implementation is very similar to the one used by QuickBooks (all the clients access a database file on a shared folder...=anyone can destroy the database)

    I don't really understand what you mean about SQL Server security, I'm not sure when are you talking about Windows Users or Database Users and also when you talk about Database I don't know if you mean the DB Service or the Database files; in both cases these things are a lot different.

    This "PErvasive FUnction Executor" is running on the client or on the server?

  6. #6
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    8
    I forgot something: you said "An application must use a Pervasive engine to read a Pervasive data file" that means that the database phisical are accessed by a service running on the server, correct ?
    This service is running as "Local System Account" (does this make any sense for you ?) so, once again, if the process running on the client (ACCPAC) must access these files through the Pervasive Engine WHY the windows users on the client needs to have access permissions on those files on the server? The process on the client should not open any file on the server, the Pervasive engine is doing it for him....and this engine (service) has enough rights to do it.
    This is basic Windows Security, is nothing weird.

  7. #7
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    What I'm trying to say is that this is EXPECTED behavior for PSQL. Period. This is how PSQL HAS ALWAYS WORKED. The application DOES NOT HAVE ANY EFFECT on how PSQL works.
    if the process running on the client (ACCPAC) must access these files through the Pervasive Engine WHY the windows users on the client needs to have access permissions on those files on the server?
    Why? Because that's the way Pervasive works. The Pervasive server takes and uses the rights of the client process. That's how it works. It has NOTHING to do with ACCPAC other than ACCPAC uses the PSQL engine.
    The process on the client should not open any file on the server, the Pervasive engine is doing it for him....and this engine (service) has enough rights to do it.
    The process on the client does not open any file on the server directly. THe process on the client opens a TCP or SPX connection to the PSQL server communication module and then communicates with a PSQL specific network packet format. The server engine does all of the actual file access but uses the client's user credentials to access the file.

    The Pervasive Function Executor can run on both the client or the server. It is a Btrieve application and can perform the same types of operations as what ACCPAC does.

    I don't understand why my security concern is so difficult to get.
    What I don't understand is why this is an issue. What industry are you in where you don't trust your users? I've been supporting PSQL for 11 years and this has never been a problem for any one I've dealt with.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  8. #8
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    8

    Saturday night....

    Don't be mad at me, I'm just trying to know all the details, and you are giving it to me..step by step :-)

    I'm sorry if you don't understand why this IS a well-known real security issue; even at Pervasive they know this IS a problem, check this:

    I just found a document from Pervasive about the announcement of the v8 of the database; I'm sure this is an old version and this makes it even more interesting; this document (see the link below) is listing all the new features of the "new" V8; the #2 feature in the front page is:

    "No Btrieve file-level OS rights required - Tight security, easier deployments"

    the fourth item in the second page titled "Great Reasons to upgrade to V8" is:

    "Pervasive.SQL V8 delivers complete data security to both new and existing applications through a new, unified common security model across all access methods. Now your existing applications no longer have to use file-level permissions for Btrieve, and new applications can leverage the powerful new Btrieve Database Login. V8 also features strong encryption ..."

    For sure ACCPAC is not one of those "new applications" but as far as I can read it says that for those old applications still using Btrieve access there is no more need to set any file-level permission.
    If this was true with V8 I don't think that they could have gone back on V9, correct?

    In your last message you finally gave me the key information: the server process is impersonating the client windows user to access the data files, this looks to me the result of a partial porting (I/O optimization through the use of a server process, the engine) of an ISAM database to some kind of RDBMS; the second part of the porting (security) has been clearly announced with V8 (see above)...then I still don't understand why with v9 we are still struggling with file permissions...

    So, or Pervasive is not telling the true or ACCPAC is really doing some weird trick, at this point it doesn't matter for me; in order to obtain the requested level of security my customer will have to wait for ACCPAC 4.5 or he wil have to migrate to SQL Server (MSDE, the free edition should work fine).

    About "trusting my users", despite this is not really the best security practice, the risk is not only related to intentional user actions (btw they are supposed to make mistakes) , for example a end user machine can be infected by a virus who scans all the network shares and affects files, this is not so difficult to happen.

    read yourself at:

    http://www.pervasive.com/documentati...ution_Sheet.pd

    About the the performances of an ISAM compared to a RDBMS.. I know you were joking ;-)

  9. #9
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    8
    sorry, the link was broken, here it is

    http://www.pervasive.com/documentati...tion_Sheet.pdf

  10. #10
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I know all about the new features of PSQL V7, V8, and V9 because I've beta tested each and every version.
    For sure ACCPAC is not one of those "new applications" but as far as I can read it says that for those old applications still using Btrieve access there is no more need to set any file-level permission.
    The default install still uses OS credentials because that's what most of Pervasive's customers want. Most customers do not require the level of security you are looking for.

    for example a end user machine can be infected by a virus who scans all the network shares and affects files, this is not so difficult to happen
    How many viruses infect Btrieve data files? None. A good antivirus system on the client would also prevent a network infection.
    About the the performances of an ISAM compared to a RDBMS.. I know you were joking ;-)
    Actually, I was not joking. There is significantly less overhead issuing a Btrieve API insert than there is issuing a SQL INSERT statement. Less overhead means faster speed. I heard from a Pervasive support person of one customer process that was issuing 50,000 Btrieve operations per second. Can you issue 50,000 SQL operations in a second? Now, there are always variables. Network congestion, CPU power, Disk I/O subsystem, and application architecture all play a role in performance.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  11. #11
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    BTW, I would suggest waiting for ACCPAC to support PSQL V9 as PSQL V8 is going unsupported by Pervasive in December 2006.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  12. #12
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    8

    Cannot sleep :-)

    "Most customers do not require the level of security I'm looking for": then my question is why is this security aspect so important for Pervasive marketing to put it at the top of his features list? And also most of the customers may not be really aware about the risks, they usually care if the application works...

    We need to close this open door mostly because we are going to enable remote desktops to let some employees work from home or from other countries; we need to do this because the actual ACCPAC web solution is too expensive; opening the a full Remote Desktop Access solves the ACCPAC access problem but expose the company network to new and bigger risks.

    I don't think a virus can infect a Btrieve file but eventually a virus could delete files...and if you (the user) can do it ... the virus can do it as well.

    I think you are right for the btrieve performances in inserting records; maybe you can get that throughput in some circunstances (single user, no indexes to update, files are local, etc.); by the way 50.000 is still a big number.

    thank you for your time, good night.

  13. #13
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Have you read through the Pervasive Advanced Operations guide? Specifically the topic of PSQL Security (http://www.pervasive.com/library/doc...vops-08-1.html).

    If you've confirmed with ACCPAC that they don't support the new security features, then you'll have to wait until they do or move to the SQL Server version of ACCPAC. If you choose the SQL Server version, good luck.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  14. #14
    Join Date
    Feb 2004
    Location
    Barbados
    Posts
    8

    SQL Server

    Why did you say "If you choose the SQL Server version, good luck" ?

    Please, if you know about any problem with ACCPAC + SQL Server, tell me before we spend other money.

    thanks

  15. #15
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I've never used ACCPAC at all.
    I only wish you good luck for a fast and easy install of ACCPAC and SQL Server.
    I've seen some comments about MSDE and how it can be cumbersome to install. I can't speak from experience though. I've limited my use of SQL Server to various web projects where PSQL couldn't be used.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •