Security Flaw
By Richard Rost
2 years ago
Fixing Security Flaw in Linked Tables in Microsoft Access
In this Microsoft Access tutorial, I will show you how to identify and fix a significant security flaw with linked tables. Learn how passwords stored in frontend files can compromise your data and explore effective methods to secure your backend, such as setting passwords on the frontend and using VBA for dynamic linking.
Sam from Murrieta, California (a Gold Member) asks: I have noticed that even with a password protected back-end MS Access database file, it's still possible for someone to import my data into a blank new database by connecting to the front-end file. How can I prevent this?
Prerequisites
Links
Up Next
Recommended Template
-
I also have a much more comprehensive Access Unbound Forms Template which has many more features including simple procedures you can use for your forms, it handles Subforms, Combo & List Boxes, Reports & Temp Tables, and it includes a complete explainer video, template, and source code
Keywords
TechHelp Access, security flaw, linked tables, password protection, Access backend, front-end database, connection vulnerability, locked database, split database, database security, network sharing, password on frontend, separate databases, ACCDE file, VBA to link tables, SQL Server
Subscribe to Security Flaw
Get notifications when this page is updated
Intro In this video, we'll talk about a serious security flaw in Microsoft Access involving linked tables, where even a password-protected backend can allow unauthorized users to import data through the front end. I'll demonstrate how the flaw works, show what happens when you try to secure your backend, and discuss ways to protect your data, such as setting passwords on both front and back ends, using Windows security, creating ACCDE files, and organizing sensitive information into separate databases. We'll also go over alternatives like linking tables dynamically with VBA and using unbound forms for added security.Transcript Welcome to another TechHelp video brought to you by accesslearningzone.com. I'm your instructor, Richard Rost. Today we're going to talk about a security flaw that exists in Microsoft Access. Whether you are connecting to an Access back end or even a database server, like SQL Server, if you're using linked tables, then you want to know about this security flaw.
Today's question comes from Sam in Murrieta, California, one of my gold members. Sam says, I've noticed that even with a password-protected back end MS Access database file, it's still possible for someone to import my data into a blank new database by connecting to the front end file. How can I prevent this?
Well, yes, Sam, this is a security concern that's been around in Access since I can remember. If you have a password-protected backend and you link to it from your frontend, that password is stored in that linked table connection. And all someone really has to do is import the tables from your frontend and they can get your data. I know, it's crazy and I wish Microsoft would do something to prevent this, but they can't. I'm going to show you exactly how it works in just a second.
Now as I've shown in several of my other videos, if you want to share your database on your network or even if you're sharing it online with SQL Server, you have to split your database. Your front end is where all your forms and reports and stuff are and your back end is where you can put your tables and queries and the data. Now in this back end database, you can set up a database password. And this is a single password that you can use so that to make a connection to this database to open the database, basically you have to have the password. Now it's a one-size-fits-all, so it's one password for everybody to get into that database.
Now an easy fix for the problem that Sam brought up is to simply put a password on your front end as well. Again it's just one password and everybody who gets in the database has that one password but it will prevent a completely unauthorized user, someone who shouldn't be in the database at all, from opening your front end as well.
Now you can set up different databases in different folders and using Windows security on your network you can prevent different users from getting into different parts of your database by just simply making different database files. You've got your customer information, you've got maybe some secure information, credit card numbers, that kind of stuff, accounting information. You might have a separate folder with a separate database set up for those users and so using Windows security you can keep those people out of those databases.
And of course, there are some simple security things you can do like making an ACCDE file and locking down your database front end so that people can't get in there and see sensitive information in your code or in your form design. Right, making an ACCDE file is also important if you're distributing the front end to different users. You don't want them getting into your code.
I got lots of different free videos. I just showed you a bunch of them. I'll put some more links to some down below that cover different ways you can secure your Access database. Now for the demonstration today, let me show you what the security flaw entails.
I've got a back-end database that has my data, my tables, and my queries in it, and I've got a front-end database that has my forms and reports and all that stuff in it. Now in my back-end database, I have a password. So if I try to open this, or if anyone tries to open it, they get, you know, asked for the password. So the password is 599cd, I'll enter that, and now you can see that I'm in here and I've got access to the tables and queries in this backend. Okay. And those are linked to my frontend. So if I open up this frontend, you can see there I have access to the data because when I created these links, I specified the password. Okay. So that password is stored in the connection between this database and the backend.
Now, obviously, you could put a password on your frontend and that will prevent people from getting into here. But let me show you what happens if someone takes another blank database. Here I'll just copy my TechHelp free template into here and I'll try to use this guy to connect to that backend. All right. Open it up. I'm going to delete the tables and the queries from this database. All right, let's try to connect to that back end. All right. New data source from database, access. Let's link to the tables. All right. Browse. Browse to the folder, pick the back end, hit open, and go. And it's asking for the password. OK, so I can't link to it. Let's try importing those tables. All right? External data, new data source, from database, access, browse. Let's try to import. Import the tables. Hit OK. And it wants the password again. OK. All right. So I can't do that. Let's try linking to the tables in the front end. All right. So new database, front access, blah, blah, blah. Let's link to browse. Let's try pulling them in from the front end. OK, all right, there's nothing there. OK, all right. Now, here's where the security flaw is. External data, new data source from database, access. We're going to import tables from the front end file. Ready? There they are. Watch this. Select all those guys, select all the queries, hit OK. Takes a second. OK. Now, I got the data. There it is. Without even specifying the password. Why? Because the password is saved in the frontend. Microsoft fix this loophole. Don't let people do this. Sammy, add this to the list. This is a pain.
All right. So, how do you get around this? Well, the easy way, obviously, is to put a password on your frontend. OK? Because if I get rid of this stuff here. Basically, what's happening here is it's importing the connection. Since this connection exists in the front and it pulls in the connection, even without the password, it pulls the password in too. Microsoft, all you guys got to do is just say, hey, if this is a password-protected table, don't import it. Don't import that linked table. That will fix the security problem. You can put a password on the front end itself. OK, but again then everyone is gonna have to have that password to open the database. All right, password on your front end. That's one solution. Another one, create separate databases for sensitive data like I said before and use Windows network folders with different passwords. OK, another way is to use VBA to link to the tables as needed.
So in other words, when you open up the customer form, create that link, link to the customer table, supply the password, and then when the customer form is closed, you can close that table. You're still technically vulnerable because while you've got the table open, someone else might pull this trick. So when the database is not in use, or if it's after hours and everyone's logged off, your database is fine. But if it's in the middle of the day and someone else sees the database, oh, someone has got it open, they can technically pull that table in. I cover how to link two tables dynamically with VBA code in the extended cut for my relink tables video.
Now the other option and the one that I like to use, it takes a little more work to set it up, but I'm going to show you how to do it. That's basically using unbound forms throughout your database. And what we'll do is when we open a form, we'll create a record set that will connect to the data source on the fly, pull in the data that you need for that form, and then when you close the form, it just closes the record set. You don't need any linked tables at all. You can link to tables, you can link to queries, they can be in secure to Access backend files, they can be on the web, they can be on an SQL server, they can be in a local SQL server file, you can connect to any ODB source that you want. And that's what I'm going to show you how to do. Well I'm not going to say today because we're going to cover it in part two in tomorrow's video, but I'm going to show you how to do this.
Now if you are using SQL Server or any ODBC data source, MySQL or any of those guys, I haven't done this myself, but you should be able to do it with them. You can use Windows authentication with SQL Server. That means that if it's a local server on-premises, you can set up Windows authentication so the database server knows who the Windows user is and you can give them permissions that way. I'm going to cover that in my upcoming SQL Server courses series that I'm planning. It's not super hard to set up, you just have to do it right.
Another thing you can do is use pass-through queries. All right, a pass-through query essentially sends the connection information to the server, the server runs the query and just sends you back the data that you requested. You can specify the password to the database in the pass-through query. OK, and I do cover pass-through queries in my existing SQL Server online seminar.
And the next two options are the ones that I also mentioned before for the Access back end. You can link to the tables as needed. In other words, when you open a form, you can link to its table and treat it like a linked table. Again, potentially vulnerable while it's in use. Or you can use the same unbound form trick that I'm going to show you when we connect to a table or query using VBA code.
I will show you an example of how to do this in tomorrow's video, but before tomorrow, it's going to be a pretty advanced VBA lesson. So if you don't know VBA and you want to learn, start here with this and then go through the lessons here. These are free. They're on my YouTube channel. They're on my website. Watch this and also be sure to understand my record sets video. Watch this as well so you know what record sets are and how they work. Consider this homework before tomorrow's video. Go watch this brush up on this stuff now.
So tune in tomorrow, same bad time, same bad channel or if you remember you can watch it right now because I'm gonna record it in just a few minutes but that is going to be your Tech Help video for today. I hope you learned something live long and prosper my friends and now that we've identified the problem tomorrow we're going to see how to fix it so I'll see you tomorrow for part two.
A special thank you and shout out to our diamond sponsor Juan Soto with Access Experts software solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check them out at accessexperts.com.
TOPICS:
Microsoft Access security flaw with linked tables
Setting a password for Access backend database
Password protection for Access front end
Using Windows security for database folders
Creating ACCDE files to secure Access front end
Demonstration of importing data from the front end
Creating separate databases for sensitive data
Using VBA to link to tables dynamically
Using unbound forms to connect to data sources
Windows authentication with SQL Server
Using pass-through queries in SQL Server
Linking tables as needed with VBA
Using VBA to\tconnect to tables or queries
COMMERCIAL:
In today's video from Access Learning Zone, I'm tackling a major security flaw in Microsoft Access. Whether you're using Access or SQL Server linked tables, this issue could expose your data. Sam from Murrieta has noticed that even with a password-protected backend, data can be imported into a new database through the front end. I will show you step-by-step how this loophole works, demonstrate how hackers can exploit it, and discuss methods to secure your database, like adding passwords to the front end and using Windows security. You don't want to miss this vital lesson. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary security flaw discussed in this video regarding Microsoft Access?
A. Users can bypass front-end passwords easily
B. Passwords for back-end databases are not encrypted
C. Linked table passwords in the front-end can be exploited
D. User permissions are not configurable
Q2. What is one way to mitigate the security flaw mentioned in the video?
A. Install a firewall on the database server
B. Use pass-through queries exclusively
C. Apply a password to the front-end database
D. Use Google Sheets for data storage
Q3. In the context of this video, what is an ACCDE file used for?
A. Encrypting back-end data
B. Preventing users from accessing VBA code
C. Enabling multi-user access to the database
D. Connecting to an SQL Server
Q4. What is one disadvantage of using a front-end password mentioned in the video?
A. It renders the database files unreadable
B. It restricts the database to single-user access
C. Everyone must know the same password to access the database
D. It increases the database file size significantly
Q5. According to the video, what is one simple method to secure sensitive data in different parts of your database using Windows security?
A. Use different Windows user accounts for different databases
B. Install a third-party encryption software
C. Place databases in different network folders with restricted access
D. Regularly change your Windows login passwords
Q6. What solution does the video suggest for creating connections dynamically to enhance security?
A. Using encryption keys stored in separate files
B. Creating record sets to pull data as needed
C. Incorporating multi-factor authentication
D. Using Excel spreadsheets for front-end forms
Q7. What is the primary benefit of using unbound forms as discussed in the video?
A. Easier to audit user activities
B. Support for complex queries
C. Enhanced security by not using linked tables
D. Improved database performance
Q8. How can one ensure that SQL Server knows the identity of the Windows user accessing the data?
A. Store user credentials in the database
B. Use pass-through queries with embedded passwords
C. Enable Windows authentication for SQL Server
D. Set up a VPN for all database connections
Q9. Which method is mentioned as a way to run queries directly on the database server and get data back for the application?
A. De-normalized queries
B. Simplified stored procedures
C. Pass-through queries
D. Indexed lookups
Q10. Which tool or resource is recommended for learning about VBA and record sets before attempting advanced VBA lessons?
A. AccessLearningZone YouTube channel
B. Microsoft Office official documentation
C. Stack Overflow forums
D. TechCrunch articles
Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-B; 7-C; 8-C; 9-C; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone addresses a longstanding security vulnerability found in Microsoft Access. As your instructor, I want to review what this flaw is, why it poses a risk, and the steps you can take to protect your Access databases, especially if you work with linked tables - whether in Access back ends or on servers such as SQL Server.
The concern was raised by a student who observed that, even with a password-protected Access back end, it is still possible for someone to import all of the data into a blank new database merely by connecting through the front end. The question was, how can this unauthorized access be prevented?
First, let me confirm that this is indeed a real issue. When you set up your Access database in a split configuration, with a front end for forms and reports and a back end for tables and data, and you then password-protect the back end file, that password becomes embedded within the connection information stored by the linked tables in your front end. As a result, anyone with a copy of your front end can in turn import the linked tables into their own database, bypassing the password. Surprisingly, Microsoft has not addressed this vulnerability over the years.
Access databases are often split for multi-user environments, both on networks and with remote database servers. The front end holds the user interface elements while the back end stores the actual data, sometimes protected by a shared password. The main point to understand is that this password only provides a single layer of security, as everyone uses the same password to access the back end.
A straightforward temporary fix is to set a password on your front end file as well. This way, at least no fully unauthorized user would be able to open the front end and thus connect to the back end data. Still, all users will have the same password, so this should not be considered a complete solution.
For stricter access control, you can organize different front end and back end databases in separate folders and then rely on Windows folder permissions. By placing, for example, sensitive customer or financial data in its own backend file and storing that in a restricted-access Windows folder, only certain users can get to that data. This approach provides an additional layer of security through your network infrastructure.
Another measure is to convert your front end into an ACCDE file. This prevents users from viewing or altering the design of your forms, reports, or VBA code. If you distribute your front end to other users, making it an ACCDE is crucial to avoid exposing sensitive logic or code.
Let me walk you through the specific flaw. If you have a password-protected back end, and the front end stores the linked tables (having specified the password during the linking process), then all someone needs to do is create a blank new database and use Access's import data tools to pull those linked tables in from the front end. Access will silently bring over the data via those linked connections, passing along the password behind the scenes. The result: they instantly have all of your back end tables and queries, password or not, just because they obtained the front end.
Unfortunately, Microsoft has made it possible for this process to work, and as of now has not provided a way to block it. Ideally, Access should prevent importing linked tables when those connections require a password.
In the meantime, here are several ways you can minimize risk:
1. Add a password to your front end as well as the back end.
2. Separate highly sensitive data into its own back end files and use Windows security to limit access through folder permissions.
3. Distribute your front end as an ACCDE file to prevent unauthorized users from viewing forms, code, or other design elements.
Beyond these more common methods, you can take things further by adopting a dynamic linkage strategy. Instead of relying on persistent linked tables, use VBA code to establish or remove links as needed. For instance, only connect to a sensitive table when a form that needs it is open, and then immediately remove or close the link when that form is closed. This reduces, but does not completely eliminate, the window in which this import trick can be exploited. For those interested in learning this more advanced approach, there is an extended lesson available in my relink tables video where I explain how to use VBA to create and destroy table links on the fly.
The most secure method I recommend is shifting entirely toward using unbound forms that connect directly to your data sources using recordsets. With this technique, forms do not rely on Access's linked tables at all. Instead, each time a form is opened, it creates a connection and defines a recordset to fetch only the required data. Once the user is finished, the recordset and connection are closed. This method can connect to any data source, whether it is a secured Access database file, SQL Server, MySQL, or any ODBC-compliant server. I plan to show you step-by-step how to deploy this recordset-based approach in my upcoming tutorials.
If you are using SQL Server, you might consider Windows Authentication, where the server identifies users through their network credentials and grants permissions accordingly. This method truly separates who can access what, and I will discuss this in my forthcoming SQL Server classes.
You may also think about pass-through queries, which allow your Access front end to run SQL directly on the server. These queries can specify their own connection information, including passwords, and return only the data needed. Details on using pass-through queries are available in my SQL Server seminar.
Finally, whether you dynamically link tables as needed or use unbound forms with VBA, it is vital to understand the basic VBA skills involved. If you are not yet comfortable with VBA or with creating and working with recordsets, you should start with some of the free introductory lessons available on my website and YouTube channel.
To review, the major security problem in Access is that anyone with a copy of your front end, even if your back end is password-protected, can import the linked tables and get to your data. Stopgap solutions include passwords on both front and back end, Windows folder security, and ACCDE files. More robust methods include dynamic linking with VBA and building your app entirely with unbound forms and recordsets.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Microsoft Access security flaw with linked tables
Setting a password for Access backend database
Password protection for Access front end
Using Windows security for database folders
Creating ACCDE files to secure Access front end
Demonstration of importing data from the front end
Creating separate databases for sensitive data
Using VBA to link to tables dynamically
Using unbound forms to connect to data sources
Windows authentication with SQL Server
Using pass-through queries in SQL Server
Linking tables as needed with VBA |
