X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Getting Started - Access & Encrypted SQL, Access 2013    
post Jan 2 2018, 08:31 PM

Posts: 45
Joined: 23-August 14

Happy new year everyone! I'm sure I've missed it, but I need some guidance of where to start:

We currently have a 2013 Access Database used to host what I believe, should be PPI. Access to this application is protected on an isolated VM in which users currently authenticate via Active Directory using Windows Server Remote Apps and multi-factor authentication using 3rd party software. There is no current way for the user to authenticate to the Access DB itself.

I'd like to further protect (and expand) this database as follows:

Authenticate the users to the Access DB via Active Directory/LDAP (if possible and recommended per information in this post).

Convert the database to SQL. I have a license for SQL 2014 Standard, or suppose I could use SQL Express. I'm assuming I could use the instructions for SSMA at https://docs.microsoft.com/en-us/SQL/SSMA/a...ess-accesstosql, just not sure this is the best or easiest way.

Encrypt the SQL database (in rest and motion, if possible) using any recommended method. I'm willing to pay for software to handle this, to ensure best results. I found this link https://netlibsecurity.com/products/SQL-server-encryption/. Again, not sure this is the preferred way. If necessary, I could get SQL 2016, as I've heard this has a built-in way of encrypting the DB.

My following questions would be:
Once the DB is moved to SQL, would I still have access to the VBA code?
Once the DB is encrypted, how do my users access the encrypted DB through Access? Is there something special they'd need to do?

Thanks in advance for your guidance and support.
Go to the top of the page
post Jan 3 2018, 02:00 AM

Posts: 852
Joined: 26-January 14
From: London, UK

Transparent Data Encryption (TDE - the ability to encrypt a whole database) is only available in SQL Server Enterprise Edition. However, TDE isn't necessary for most applications and is relatively uncommon in my experience. Usually it's sufficient to put your server in a secure location and properly control access to it. In SQL Server you can authenticate users and apply role-level security without using encryption features and so data encryption is rarely needed and usually only applied to a few select bits of data in a database.

Migration tools will only take you so far and they won't implement your security for you. I suggest you need to get help from someone with SQL Server expertise to design / redesign your database and application for you.

VBA code would always reside in Access not SQL Server so access to it isn't controlled by SQL Server security.

Access to your SQL Server database (whether encrypted or not) is via one of two mechanisms: Windows authentication (highly recommended) or SQL Server logins (not recommended by Microsoft for any application where security is a priority).
Go to the top of the page
post Jan 3 2018, 02:49 AM

UtterAccess Editor
Posts: 9,922
Joined: 7-December 09
From: Staten Island, NY, USA

To be picky, TDE is available for SQL Azure as well: https://docs.microsoft.com/en-us/SQL/relati...ption-azure-SQL

That said, I agree with what nvogel mentions.

You might be interested in a book by SQL Server MVP John Magnabosco called Protecting SQL Server Data. It's a bit dated (unless there's been a new edition in the years since I've purchased it), but covers a lot of ground. ISBN 978-1-906434-27-4


Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th July 2018 - 11:28 PM