Access vs Comercially available databases

[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!

 

[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.

 

[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.

 

[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.

 

[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?

 

[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.

 

[CJ_London]

But it's a fact that Access is the least secure comparing to any pre-package system.

Many years ago a client decided to implement a well known online crm system. I was involved with integrating it with finance, commissions and hr systems.

I discovered that if you knew the login details (of a sales person for example), you could not only access the data from excel, that access was not limited to the data the login allowed - you could see and edit pretty much anything.

The crm company initially reassured my client that it was not possible and that I was making it up (echoes of Horizon?) So (with the permission of my client) I went in and made some changes.

The crm company had to about face and took something like 3 months to come up with a solution

 

[AccessBlaster]

It's all moot, some of the most robust systems on earth get hacked. I'm part of a large class action law suit right now. Keenan & Associates was hacked in 2023. Millions of people effected, potentially billions of dollars on the line. Security is an illusion like insurance.

 

[gemma-the-husky]

It's good to see a lot more answers on this, but I thought the real question the OP was asking is whether the functionality of a commercial system would be better than the system that was already in place.

 

[The_Doc_Man]

The problem I have with 3rd party packages is that they were probably developed by a senior designer and a bunch of junior coders. I.e. without knowing the details of how it was actually developed and implemented, you have no way of knowing whether it is better, the same as, or worse than Access for security details.

 

[RonPaii]

"But you can not hide the server name, and database in connection string. Can you?"

You can't hide the server with or without Access. The server handles data security, not the FE.

BTW, I know of at least 1 very large account / payroll / buisness system with a base option of a file based db, just like Jet which makes it just as hackable. You can build passthough queries to that db usable with the corrent login form an Access FE.

 

[Isaac]

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.

Keep in mind that most of these implementations are already behind the safety of a network. If you trust your users to give them access to the data then trust them. If someone gained access to your network and your file servers, they would have access to everything not just Microsoft Access. Microsoft Access is not any specialer than any other file nor more dangerous

 

[Isaac]

That is why you don't use Windows to secure your SQL Server. My users never have their credentials for the server database. While it is true that you can view linked tables, as long as you don't store the password with the link, you can't retrieve data that way or use the table names and server name to log into the server.

In my opinion it's the opposite, using Windows active directory groups to secure sql server access is a great option

 

[The_Doc_Man]

If you can't trust your own users, then what's the sense in conducting business?

Trust is a continuum just like lots of other things. It isn't "do you trust user X?" it is "how much do you trust user X?"

That's why role-based security is so important.