Access vs Comercially available databases (6 Viewers)

[Pat Hartman]

The problem with the subscription model when you are running on their servers is you have lost control over your data and you cannot connect any other application to the data so you are only able to create exports. At least when you run the software in house, you can get to your data directly (assuming it is using a standard RDBMS and not a proprietary one).

 

[The_Doc_Man]

It is exactly Pat's comment about "losing control of your data" that caused the U.S. Navy to set up the standby (alternate site) systems that ran the U.S. Navy Reserve personnel management database in 2005-2006 after Hurricane Katrina trashed New Orleans.

Oh, there were half-a-dozen commercial backup sites we could have used, but the problem was that with our security requirements, commercial backup sites were expensive. AND if it wasn't a true emergency, just a test run, their contract said they had the right to control the test dates to conform to their schedule.

("How expensive was it?" says the voice from the audience...) Expensive enough that to actually do a one week full-on test run once per year for the configuration we had, it was cheaper for us to buy another pair of servers for remote production and development. We bought two more Itanium server-class systems with 8 GB RAM and 4xdual CPU cores ("Longhorn" series, 8 threads each box) plus system disks, fiber-channel NAS network cards, and Gigabit Ethernet cards for less money than one test of our full configuration. AND... Congress, in their infinite wisdom, had mandated that ALL U.S. military servers would undergo remote-operation testing once per year.

 

[gemma-the-husky]

Normally any purchase decision is to choose between buying a commercially available system, with attendant purchase and licensing costs, compared with building your own, probably more expensive to build, but less expensive to run. The commercial version ought to be fancier. As already mentioned, I prefer my own General Ledger to that of Sage, and it took me very little time to build - but I haven't got lots of other systems integrated to my GL.

One important consideration is how you have to compromise with the commercial system. If you build your own, it will (should!) do exactly what you need. If you buy a packaged system you may lose some functionality, and you may have to work differently to use the new system.

Given you already have a system, presumably working well, then the equation rather changes. Maybe the powers that be are concerned with the ongoing viability of Access, and the threat of being reliant on just yourself.

If the "bosses" have made the decision there is little you can do, other than mention your concerns. Maybe someone new wants to solve a non-existing problem.

FWIW, I have been listening to various revenge stories on youtube, and I do enjoy "revengewithkaren" best, generally dealing with IT issues. Worth a listen. In several of these the old guard has managed to "own" the source that is being used, and things of that nature. In all of them he new guy gets his comeuppance.

I am in danger of seeing everything in my life as worthy of revenge though. Mind you, just because I'm paranoid doesn't mean they aren't trying to get me.

RevengeWithKaren

Help us reach our first 100K subscribers! Where loyalty dies, our stories begin. Subscribe for gripping true-style revenge stories where karma always finds a way. All stories on this channel are entirely fictional. While they may be inspired by real-life themes, characters, events, and...

www.youtube.com

 

[Isaac]

One important consideration is how you have to compromise with the commercial system. If you build your own, it will (should!) do exactly what you need. If you buy a packaged system you may lose some functionality, and you may have to work differently to use the new system

Right. And the heavy burden of adjusting entire business processes in order to satisfy one little thing in the software workflow should not be underestimated. Something as minor as when a dropdown can be adjusted could require the order of operations of the business process to be entirely reversed. Custom software is priceless!

 

[BlueSpruce]

I am being asked to possibly switch from my current Access database in which I have a great workflow and etc. to this commercially available Automated Laboratory Information System (ALIS).

I cannot tweak this [ALIS] app for customizations, we could be looking at time and cost of migrating everything correctly, even if data import is possible, re-building what we already have in Access could be expensive and disruptive.

Their main concern is that I am the only one who knows how to maintain the Access app...

So document your Access app well, including technical and end user documentation, and bring another Access developer, or firm, on board. Explain to management the pros of your Access app, and the cons of COTS software.

You can also integrate instruments with your Access app.

 

[KitaYama]

While all the the mentioned pros of an in-house solution is correct, you guys forgetting about the security.
If you own a business, security of your data stands ahead of everything else, and well....all of us know that Access is amoung the least secure apps.

 

[BlueSpruce]

While all the the mentioned pros of an in-house solution is correct, you guys forgetting about the security.
If you own a business, security of your data stands ahead of everything else, and well....all of us know that Access is amoung the least secure apps.

The native Access backend can be secured with Active Directory. The frontends with Windows Authentication. If additional security is wanted for the data, use SQL Server. I also prefer having the data on premise versus in the cloud, hosted by third parties.

@Pat Hartman said: The problem with the subscription model when you are running on their servers is you have lost control over your data and you cannot connect any other application to the data so you are only able to create exports. At least when you run the software in house, you can get to your data directly (assuming it is using a standard RDBMS and not a proprietary one).

 

[GPGeorge]

While migrating data from an accdb to a server database such as SQL Server is not a cost-free endeavor, it does address most of the security concerns for Access database applications. Comparing that cost to the cost of implementing a third-party application is an important part of the decision-making process. Given the likelihood that the third-party application will require modifications to existing business rules, the scales are tipped farther toward retaining an existing Access/SQL Server solution.

 

[KitaYama]

The native Access backend can be secured with Active Directory. The frontends with Windows Authentication. If additional security is wanted for the data, use SQL Server. I also prefer having the data on premise versus in the cloud, hosted by third parties.

If your FE is Access, you can open a blank database, write a simple code to return all tables and then theri fields in your FE. You can also run an update or insert query from a database outside your FE. You can hide/unhide tables in a FE using a remote database, or change FE's properties like shift+double click or .....

As long as your FE is Access, you have a big hole in your security and it doesn't matter your BE is sql server or cloud.

 

[BlueSpruce]

If your FE is Access, you can open a blank database, write a simple code to return all tables and then theri fields. You can also run an update or insert query from a database outside your FE. You can hide/unhide tables in a FE using a remote database, or change FE's properties like shift+double click or .....

As long as your FE is Access, you have a big hole in your security and it doesn't matter your BE is sql server or cloud.

Although what you mention is true, a hacker would need to login as one of the users in the AD group that has permission to access the directory where the backend lives. If SQL Server is used, then there's an additional layer of security.

 

[KitaYama]

Although what you mention is true, a hacker would need to login as one of the users in the AD group that has permission to access the directory where the backend lives.

Where I work, (like 90% other companies) most of PCs are only terminals. I'm sure you understand how a terminal in an Active directory work.

Anyone can sit in front of one of these terminals, and open one of our Access databases to work on it. And because mostly Windows Authentication is used to link tables in FE to sql server, you won't have any trouble to work on a database from any terminal.
And if you are able to open and work on a database from a terminal, it means you can do a lot more from a remote database to harm BE's data.

You can never make an Access database as secure as an out-of-box app.
I just think maybe OP's company tries to be more secure, hense accepting the cons of a third party app over pros of Access database.
Again it's just a wild guess.

 

[BlueSpruce]

Where I work, (like 90% other companies) most of PCs are only terminals. I'm sure you understand how a terminal in an Active directory work.

Anyone can sit in front of one of these terminals, and open one of our Access databases to work on it. And because mostly Windows Authentication is used to link tables in FE to sql server, you won't have any trouble to work on a database from any terminal.
And if you are able to open and work on a database from a terminal, it means you can do a lot more from a remote database from any of these terminals.

You can never make an Access database as secure as an out-of-box app.

I disagree. If setup correctly, an Access application can be sufficiently secure. The setup your company uses is not correct.

 

[KitaYama]

The setup your company uses is not correct.

Thanks. I'll pass the words to our IT.

 

[KitaYama]

@BlueSpruce One last question.
Do you know you can open your FE and while the FE is opened, launch another database and from there, read the connection string of your FE's linked tables to BE?

 

[RonPaii]

Yes you can easly read the connection string, so don't store login information in your connection strings.

 

[KitaYama]

o don't store login information in your connection strings.

No login info is saved in connection string. As I said Windows Authentication is used. So there's no login info at all.
But you can not hide the server name, and database in connection string. Can you?

 

[BlueSpruce]

Hey All, it would take an experienced Access developer to do all those hacks you mentioned. How many of those do you have at your company? Your average Access user is not going to know that. If you can't trust your own workers, then how can you operate? and I said "sufficiently secure", not totally. No system is totally secure! In the OP's case, this is not a concern, it's lab data at a gov agency.

 

[CJ_London]

Do you know you can open your FE and while the FE is opened, launch another database and from there, read the connection string of your FE

Yes - and knowing that, there are steps you can take to prevent it - not saying how as a potential hacker may be watching and it can start be hacked but further up the difficulty scale

How secure you want to make it depends on your security requirements. No system is totally secure - even web based solutions can and will be hacked if the data that can be obtained is deemed worthwhile. And they are a bigger target

Excel is rarely protected and the password easily found if required and can contain valuable information

Do you disable copy/paste from a spreadsheet or form/datSheet view? Do you disable screenshots or not allow anyone near a terminal with a camera or smart phone

 

[The_Doc_Man]

The U.S. Navy used an all-Access solution for several of its special-purpose databases and an Access FE/SQL Server BE for a major medical personnel scholarship database that served all U.S. Navy Reserve medical doctors-in-training. The project managers for all of the projects were concerned about security and settled on a method using Windows AD security and permissions based on a group identifier as a way to protect data that, if breached, would have led to coordinated security attacks on 80 different projects and 1500 computers. They implemented an in-house sub-net and protected the entry points with some nice firewall boxes. They had other steps in the process. I didn't implement the network portion but I did help in the design of the security methods. We were secure because we were careful about entry points and network accessibility.

To say that Access isn't secure? True, but that is because NOTHING is completely secure. There are things that cannot be broken in a particular way, but ... the only absolutely security computer is one that is never turned on. Of course, it is so secure as to be useless. When we got our security certificates through our studies with Navy instructors, we learned the basic principle of computer security. You can't perfectly block any computer from hackers. You can only slow down the hackers long enough to make them decide they can spend their time more effectively on a less protected machine. I.e. make it too expensive for them to continue their attack.

Since most hackers are of the "grab the low-hanging-fruit" class of threats, a little bit of judiciously implemented protection can get rid of a very large percentage of those computer threats.

 

[KitaYama]

How many of those do you have at your company?

Maybe zero. Maybe a lot (While a google reseach needs less than 30 minutes to bring up all sollutions)
If you go back to previous posts, I simply said Access is not secure enough (comparing to out-of-box apps) and maybe that's why the OP's company is thinking about another solution like ALIS.

To say that Access isn't secure? True, but that is because NOTHING is completely secure.

Security is a comparison. Nothing is completely secure. You're right. But it's a fact that Access is the least secure comparing to any pre-package system.

I find it very interesting that we are in topmost Access site and still have to discuss how Access is not half secure as other apps.
It seems that it's time for me to step back.
Thanks.