Secure communications in MS Access to SQL


I need to create a database system for a large organisation and will be using MS Access.

My thoughts are to use MS Access Front End and MSSQL server back end. This I have done with linked tables and as database projects.

My question is... I need to ensure the communications between MS Access and MS SQL server are secure.

If I use ODBC then they are NOT so much.. If I use Database Projects then again...unsure of exactly how secure this is.

Can I have recommendations using these technologies please what would others approaches be to keep sensitive information securely or encrypted communications or whatever.

Many thanks

RomoloIT Pro, Projects, Mentor, TrainerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
You must *clearly* define/quantify "Secure".

Absolute security is impossible.
RomoloIT Pro, Projects, Mentor, TrainerAuthor Commented:
I want what would be acceptable as "secure communications" like any other software. I.e. a VB App connecting to SQL.

So I cannot be specific.. as I need to know whats possible.

I hope that makes sense

Jeffrey CoachmanMIS LiasonCommented:
Then without any specifics, I am at a loss.

Again, there are many aspects to "Security"

This is too big a topic to speak in broad terms like "secure communications"
Perhaps another Experts is willing to take this on.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
<<If I use ODBC then they are NOT so much>>

Or do you really mean DSN?

You can go DNS-Less.

The only way I now that any front end  can work with MS SQL Server is via the ODBC Driver.

Are you using Windows/NT Authentication to connect to  SQL Server?

If you use Windows Authentication then is the logged on Windows user as has permissions  to the SQL Server  data then so will the  Access front end.

Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Hi R,

An Access front-end application is not the most secure way of using SQL Server data.  A web application like ASP.NET is more secure.  That's because the web app and the database are connected server-to-server, and the user sees only the UI via their browser.  In the case of Access, the user has a front-end that is directly connected to SQL Server, so it is inherently less secure.

However, there are ways to make an Access-SQL Server application *more* secure.  For example, use Windows Authentication - then you don't need to store passwords in Access.  Use a VPN (or an internal LAN) so that you aren't sending unencrypted packets through the Internet.  Turn off the AllowBypassKey property and special keys.

I discuss a few of these security issues, and many others, in a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

RomoloIT Pro, Projects, Mentor, TrainerAuthor Commented:
Thanks for the responses..

To put some meat on the bones...

I can secure a MS Access DB down from a user twiddling point of view. Hiding everything, bypass etc.... MDE ..

If I give you an example as ArmenStein has helped me to word.

ASP or PHP pages and Database server is great and secure because the database data does NOT touch the user... just output.. so thats great. Nice and secure.


IN a VB app... unsure how secure the data exchange with the server is as this is viewable on the VB app client side.

Comments on VB App and SQL vs. MSAccess App (secured front end as above) and SQL.

RomoloIT Pro, Projects, Mentor, TrainerAuthor Commented:
And VB App on client has to transit through LAN to server.. so inherently more unsecure.

But I am really interested in how secure does

MS Access secured FRONT END and SQL server .... stack up against a ... VB App and SQL server...

Are they the same or is there a major security difference that I could implement in MS Access to make them as secure as each other.

Hope I have explained well enough now


Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Have you considered a Cached Connection?

See a fellow  Access MVP's blog on the subject:

Boyd Trimmell aka HiTechCoach
Microsoft MVP - Access Expert
Steve BinkCommented:
Just my two cents:

I have not worked with MSAccess since 2003, but my experience says you cannot secure it.  Sure, you can hide things and so on, but any user with a little curiosity and an overabundance of time can get in there.  Access is meant for smaller, more informal projects, not enterprise-level deployments.  If security is one of the core concerns of your project, Access is not the way to go.

If you're using a web application, as you noted, you have a great abstraction to keep the user away from the database.  In my view, this is an ideal situation.  The database, its credentials, and the applications connection are all protected by virtue of being server-side.  One of the disadvantages would be performance - a local application will obviously out-perform.

For local applications, using .NET or other compiled language will create a stand-alone application for deployment.  Outside of someone with experience in reverse-engineering, there is not too much exposed on that side, but that really depends on how you approach it.  You can always hard-code the credentials into the app, which is more security at the cost of greater management whenever changes have to be made.  You also have to consider the communications between the app and the server.  Is unencrypted connections over TCP/IP going to meet your project requirements?  If not, you'll need to configure the server and the application to use encryption, which increases your project requirements.  

As far as end-user data exposure, each approach is going to protect the data through abstraction.  With MSAccess, there is the potential for direct access through linked tables and whatnot.  With a web application or compiled project, you know the user is only going to have access to what the interface allows, and you create the interface.

Hope this helps.
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
I would say that a VB and Access are equvialent safe, i e this depends on how they were programmed.

They both can store accounts and passwords in their respective programs, so I suggest you look at it from a different perspective.

The SQL Server itself can help secure, i e:
Only allow windows authentication (application has no need for storing passwords, the AD takes care of that)
AD groups differenting the rights between admins and users
the user only has rights to stored procedures, not tables and views (mostly applicable in a vb program)
Demand ipSec communications to this server only
Distribute the ipSec certificate with the program, and set rights to the sql utilizing the AD groups

And for convinience
use the AD groups to distribute the application (needs to be a msi package for this)

Set sql server to log failed logons and check this log with some automation.

Thats a good list for a secure application but as always we can add:
encryption of the data
Physical security
security of backups
backup strategy of database on sql
backup strategy of server
if passwords needs to be stored in sql database use salted hashs, not passwords
network segmentation and firewalls design
and so forth

Hope this straightens out some question marks of yours!

Regards Marten
RomoloIT Pro, Projects, Mentor, TrainerAuthor Commented:

Thank you for your comment. These are things I am aware of, but really good that you bring up in this string.

Do you have any direct comments about MSAccess as an application and VB when transmitting data.

I.e. the sniffing of data as it transmits and encryption of that.

The local application can be secured and the datastore also. Datastore being SQL server as opposed to the file as a datafile.
But what about transit.

I ask from a developing an MSAccess application and comparing its security to that of a VB application. i.e. MSAccess is as secure.. but how secure is that when writing and reading.

Thanks very much in advance

Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
As far as I know, there is no built-in encryption for the normal transmission of data in ODBC with Access.  However, if you can connect to the back-end database using a VPN, then the whole conversation is encrypted.

We have developed techniques for transmitting data between Access and SQL Server using ASP.NET web services running under SSL, but this takes extra effort and might be overkill for many scenarios.

Ultimately the most secure architecture is a web application running under SSL.  Neither Access nor will be as secure as that.
RomoloIT Pro, Projects, Mentor, TrainerAuthor Commented:
Thanks for the comments.

I know web gives an access level of security.

However PC desktop apps have their space, so was curious what more are people doing to secure the applications these days

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
In an AD you can use a CA to issue ceritficates to the clients. And then demand IPSEC when communicating with the SQL Server in question. It should take care of the network transmission issue.

Regards Marten
RomoloIT Pro, Projects, Mentor, TrainerAuthor Commented:
hmm... still based on WHO can access rather than secure communications.

Thanks everyone. Going to close rather than leave q to float about based on little response



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RomoloIT Pro, Projects, Mentor, TrainerAuthor Commented:
Didn't get what I was after.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.