​YES – Microsoft Access Can be Used Securely

​One of the biggest misconceptions about MS Access, is that it isn’t a secure database. To be fair, the misconception has some grounding in fact. If the MS Access database is using native tables, then it isn’t any more secure than an Excel Spreadsheet. But, this is a simplistic understanding of the issue. Following are some things to take into consideration, about data security and the use of Microsoft Access.

Many organizations fault MS Access Security, but have no problem with storing sensitive data in Excel spreadsheets, created by end users and stored in folders that may (or may not be) secure. For example – and I see this on a regular basis – users will download sensitive data from a secure enterprise software solution. They’ll dump their export into Excel, save the Excel file to a personal folder (or worse yet their desktop). This isn’t even logical. On the one hand, it’s o.k. for end users to copy/paste spreadsheets of sensitive data to in-secure locations (like their desktops). But, for some reason storing the same data in an Access database (with more controls than a spreadsheet) is forbidden. Even if security in Access was limited to native Access tools, one would have to look long and hard at a policy which allows so much exposure to data through Excel, but then limits the use of Access.

Beyond the obvious problem described above, what most people don’t know – and never get to in their assessment of Access as an option for sensitive data – is that Access can be upsized to incorporate the use of SQL Server. You may want to read this Microsoft Knowledge Base article on upsizing Access Data.
​
From the article:

Benefits of upsizing a database to SQL Server
​

• ... Improved security Using a trusted connection, SQL Server can integrate with Windows system security to provide a single integrated access to the network and the database, employing the best of both security systems. This makes it much easier to administer complex security schemes.

In short, when you upsize an Access Database to SQL Server, you’re moving all Access tables and data to the more robust Microsoft SQL Server database. Then you link the SQL Server tables back to the MS Access frontend file. This is a very common way to use Access. Most of my clients are using a SQL Server backend database with Access as the frontend file.

In the linked article, Microsoft lists many benefits of upsizing Access data to a SQL Server backend. The linked article is well worth your read. The benefit of Improved Security is highly important. By moving all of your Access data to a SQL Server backend database, you can immediately take advantage of the robust SQL Server Security capabilities. Specifically you can use SQL Server Active Directory Security. This means Active Directory security can be used to:

1. Determine which user is logged into the database
2. Create roles to designate permissions for logged in users. Permissions can restrict which tables users are allowed to see, to edit, etc…
3. In addition – by using Active Directory - ODBC Data Connectors are more secure, because you can get rid of saved login strings with user names and passwords. With Active Directory controls at SQL level, all Access requires is the use of Windows NT authentication – SQL will do the rest.

Beyond using Active Directory Security, there are other ways that one can control access to data. SQL Views can be used to limit which datasets a user sees. For instance users may only need to view customer records for their district. In a situation, such as this, it is possible to write a query at SQL level, which limits the record-set to a specified district. These queries are called SQL Views. Once the SQL View is written and stored it can be linked back to an Access frontend file. This is a subtle way to limit which records a user can view, but it is effective. Where Active Directory settings can be used to determine which tables a user is allowed to edit and which tables they are even allowed to read, SQL views can filter the data a user is allowed to view &/or edit down to a specific subset of records (such as all customers in one district).

By upsizing Access data to a SQL Server database, and incorporating the robust security capabilities of SQL Server, the security problems one would expect in a stand-alone Access go away.

Of course it only makes sense to continue to create restricted run-time versions of your Access frontend file and store these files in a secure folder, with limited access, by qualified users. In addition, when creating ODBC data connectors, you should use machine data sources, stored in secure folders and restricted to qualified users. That way only qualified users; logged into the specified machine, will be able to get to the data source.

Once all of these things are taken into consideration, Microsoft Access can be used very successfully with sensitive data.

More Articles About Microsoft Access​​

• Yes – Microsoft Access works in a Multi-User Environment
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.