Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Secure communications in MS Access to SQL

Posted on 2011-09-06
17
Medium Priority
?
395 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
  • 7
  • 2
  • 2
  • +3
16 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

772 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