Solved

Secure communications in MS Access to SQL

Posted on 2011-09-06
17
385 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:roycasella
  • 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:roycasella
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
 
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:roycasella
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:roycasella
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 50

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:roycasella
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:roycasella
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:
roycasella 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:roycasella
ID: 37187545
Didn't get what I was after.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now