[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

question about Securing Access Database

Posted on 2011-04-20
12
Medium Priority
?
512 Views
Last Modified: 2012-05-11
We have a commercial application which is going to be distributed to customers.

it's split front end/back end using SQL server as the back end. We have encrypted server traffic and hashed passwords etc so that a user cannot connect to the dbms using external tools and their password and inspect data. Some of the database contents are quite confidential.

When our app is not running the table links are destroyed (last thing it does before it shuts down).

However, I am disturbed by the fact that it is possible to create an access database and link to a system/hidden table in the running application accde file and discover the encrypted passwords in the connection strings of msysobjects.

We need persistent connections. We have a database design with >300 tables and it's highly integrated so we use tables all over the place. With north of 100,000 lines of code I don't think we can re-architect to connect and disconnect dynamically and in any event that would comprise a significant performance overhead.

Any advice from anyone for ways to lock this down? I've been searching but no luck so far.

Thanks , rj8820
0
Comment
Question by:rj8820
  • 6
  • 3
  • 3
12 Comments
 
LVL 58
ID: 35434854
<<Any advice from anyone for ways to lock this down? I've been searching but no luck so far.>>

  The use of Trusted Connections for SQL Server security would remove the need to embed a username/password in the connection string.

JimD.
0
 
LVL 1

Author Comment

by:rj8820
ID: 35435126
Sorry but we have kept away from this for two reasons:

most importantly using trusted connections means that any user can either write their own data access databases against the server, connect to the database and play, or they can download tools like SSMS and connect to the server using a trusted connection and inspect the database from their own windows account. This is a clear security problem we have to avoid, and

second, not every client technical environment would support trusted connections for other security reasons (i.e. in our banking clients)

any other thoughts? Why would microsoft allow this at least without providing the ability to block it..?
0
 
LVL 1

Author Comment

by:rj8820
ID: 35435157
So I have a corollary question: if in access you set the open mode to exclusive does that affect linked sql server tables>? or just the local  tables? i.e. if I open in exclusive mode does that prevent external users from connecting or does it mess up the linked table connections too by making them exclusive??
0
 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 35435218
You could password protect the accde file, and then users would have to know that password before they could link to the tables. You can also build a "launcher" app that will fire off that app, so your users don't need to know the password. To do that, build an Access db that does nothing more than open the database, using code like this:

Dim objAccess As Object
Set objAccess = New ("Access.Application")

objAccess.OpenCurrentDatabase "FUll path to your password protected db", , "YourPassword"
objAccess.Visible = True

'/you can then Quit the launcher Access process
Application.Quit

0
 
LVL 58
ID: 35436071
<<most importantly using trusted connections means that any user can either write their own data access databases against the server, connect to the database and play, or they can download tools like SSMS and connect to the server using a trusted connection and inspect the database from their own windows account. This is a clear security problem we have to avoid, and >>

  While that's certainly true, SQL Security is also in place; they can only do the things you allow them to do.  If they don't have access to a table, then they still won't get access even if they by-pass the app.

<<second, not every client technical environment would support trusted connections for other security reasons (i.e. in our banking clients)>>

  I may be naive, but I thought trusted connections were pretty secure.  If I'm authenticated to the domain, and I can trust that, well that doesn't seem to say much then for windows security.

<<any other thoughts? Why would microsoft allow this at least without providing the ability to block it..? >>

  LSM has given you a couple, but Access is inherently insecure.  considering that you've already encrypted the passwords, I'm not sure how much further you can go.

<<When our app is not running the table links are destroyed (last thing it does before it shuts down).>>

  I'm wondering what happens on an abnormal disconnect and your DB doesn't have a chance to delete the links...

JimD.
0
 
LVL 1

Author Comment

by:rj8820
ID: 35436508
to LSMConsulting: thanks, that's an interesting line and I'll test that one out.

to JDettman:  yes the point on abnormal terminations is one that occurred to us but I figure is you can find some fix for the remote system table connections then it actually wouldn't matter too much if you lef tthe links in tact. Our testing revealed that you cannot connect to a linked table from the outside. You can connect to a local Access Jet managed table but not to one which is linked to a server.

We will do two things tomorrow: first test the exclusive access idea, second to test the password encrypted DB with a launcher.

Does anyone know how secure accde files are generally in terms of the code: is it reasonable to assume that the code cannot realistically be traced or decompiled in any way?? I just want to know if we write convoluted code to supply the password wether it can easily be cracked. I realise most things can eventually be cracked given enough time and brain power but how about accde?
0
 
LVL 1

Author Comment

by:rj8820
ID: 35436552
By the way JDettman, your point about data security and giving database roles is taken but we tried that and we ended up with more than 150 database roles with all the permutations and combinations and access needs to be manageable by a user admin and on a functional basis not data basis as we have an extremely integrated data model. The other things are, 1. the database structure is an important part of our IP: we don't want anyone having casual access, and 2. ANY ability to dick around with the data in the database potentially creates significant support issues for us so we just wanted to slam the door shut - which our customer DBA's would prefer too.
0
 
LVL 58
ID: 35437499
<<Does anyone know how secure accde files are generally in terms of the code: is it reasonable to assume that the code cannot realistically be traced or decompiled in any way?? >>

  Only one outfit has done it to my knowledge and you must prove ownership before they will provide source from a MDE.

JimD.
0
 
LVL 85
ID: 35437688
I'll go out on a limb and say that choosing Access + linked tables for this sort of application was probably not a wise choice. I realize that these things tend to outgrow their initial intended use, but at some point you've got to say "I've done all I can with the Access platform, might be time to move to a more secure one where I can do away with links and such." If you are truly concerned with data security it would be time to do away with linked tables and handle your data entirely via classes or some similar concept. This does away with a LOT of data security issues, at the expense of a LOT of programming time.
0
 
LVL 1

Author Comment

by:rj8820
ID: 35438443
LSMConsulting, of course you're right and I agree - but I always agree with 20/20 hindsight. We have  a plan to migrate off Access on to a web-browser based platform as soon as we are able to afford the effort but in the meantime we are where we are and I'm grateful for your pragmatic assistance.
0
 
LVL 1

Author Comment

by:rj8820
ID: 35438530
So thanks to all.. we have test two things: the password encryption idea with a launcher and opening the database exclusively. We have arranged for the launcher to derive the password algorithmically so it is never stored in the launcher anywhere (other than in memory) and the launcher is also password encrypted itself. The launcher also makes sure that Allowbypasskey, openmode, and other options are actually set correctly in the main app too in case anyone figures out how to change them without the password.

Opening the accde in exclusive mode does not impact linked tables. Concurrency for linked tables is defined and managed by the server of course.

So we will be using both these two things together and I think that will be pretty tight.

Thanks for your comments guys...

Cheers, RJ
0
 
LVL 85
ID: 35439482
< off Access on to a web-browser based platform >

web-based apps are inherently less secure than desktop-based solutions, so you might be jumping from the frying pan into the fire. Be careful and review thoroughly before making a decision to do that.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

834 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