Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Secure communications in MS Access to SQL

Posted on 2011-09-06
17
Medium Priority
?
394 Views
Last Modified: 2012-05-12
Hey,

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

R
0
Comment
Question by:Romolo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +3
17 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36489532
You must *clearly* define/quantify "Secure".

Absolute security is impossible.
0
 
LVL 7

Author Comment

by:Romolo
ID: 36489788
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

R
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36490105
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.

JeffCoachman
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21
ID: 36490642
<<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.

0
 
LVL 9
ID: 36496435
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:

http://www.JStreetTech.com/downloads

Cheers,
Armen
0
 
LVL 7

Author Comment

by:Romolo
ID: 36496794
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.

Thanks
0
 
LVL 7

Author Comment

by:Romolo
ID: 36496901
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

Thanks

R
0
 
LVL 21
ID: 36577402
Have you considered a Cached Connection?

See a fellow  Access MVP's blog on the subject:
 http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

Boyd Trimmell aka HiTechCoach
Microsoft MVP - Access Expert
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 36578487
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.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 36578492
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
0
 
LVL 7

Author Comment

by:Romolo
ID: 36579100
Hi,

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

R
0
 
LVL 9
ID: 36580250
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 VB.net will be as secure as that.
0
 
LVL 7

Author Comment

by:Romolo
ID: 36592201
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

R
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 37083040
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
0
 
LVL 7

Accepted Solution

by:
Romolo earned 0 total points
ID: 37165340
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

Shame..

R
0
 
LVL 7

Author Closing Comment

by:Romolo
ID: 37187545
Didn't get what I was after.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question