Avatar of samgiuoco
samgiuoco
 asked on

Securing an Access Database

I have an Access database on a webserver and am using ADO to access it from an ASP app.  There are two ways I know of to secure this database.  One is to set a database password, and the other is to limit database access by restricting it with a username and account password.  Here are the problems I am having:


Username and account password route:

This is what im currently using.  It works fine, but if the database is downloaded or somehow separated from the MDW file, it just gives anyone access.  I have imported the data into my MDW so they cant be separated, but then if the MDW is simply renamed to something else the auth once again doesnt trigger and anyone has instant access.


Set Database password:

I have a feeling this is the proper route, but unfortunately, my code no longer works if I implement this.

   strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
   strconnectionstring = strconnectionstring & "Data Source= " & server.mappath(DATABASE) & ";"
   strconnectionstring = strconnectionstring & "Database Password=myPass;"  
   strconnectionstring = strconnectionstring & "Jet OLEDB:System database=" & server.mappath("../SYSTEM.MDW")
   Set cnn = Server.CreateObject("ADODB.Connection")
   cnn.Open strConnectionString, "Admin", "myPass"
   Set Clients = Server.CreateObject("ADODB.Recordset")
   strSQL = "SELECT * FROM Clients" & SQLsort
   Clients.Open  strSQL, cnn    



I get an ISAM error.

Error Type:
Microsoft JET Database Engine (0x80004005)
Could not find installable ISAM.
/secure/clients.asp, line 231

I've checked everything and my ISAM drivers are up to date.  The registry paths are also correct.  I find it odd that i only get this error when I add the "Database Password= " line.


Credit will be given for this question if you can solve either one of these problems, or come up with a third solution that secures the database successfully.
SecurityMicrosoft Forefront ISA Server

Avatar of undefined
Last Comment
MacRena

8/22/2022 - Mon
SunBow

Sorry, IMO the placement of data and or server on internet is inherrently insecure in the first place. The first thing I think to to is to remove data from webserver and get it on server that can only be located through another server. After architecture addressed, (see also DMZ) then the code to use architecture can be revisited.
SunBow

and if you want help with Access, this is wrong TA, try here:
https://www.experts-exchange.com/Databases/MS_Access/
samgiuoco

ASKER
ok

if this is in the wrong place id like to request a refund so i can repost it


As far as architecture goes, i want the file to be there and as accessable as it is.  I just want to lock the database with a password.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
donnyr10

* I just want to lock the database with a password. *

This should fit you right ..

About protecting a Microsoft Access database

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acdecSecureDatabaseS.asp

To Set the Password

Tools >> Security >> Set Database Password

However before this you need to open the Database in Exclusive Mode

Open the Database >>File >> Open
Find the Database in question, on the right Hand Open Button >> Click the Down Arrow and Choose >>
                                      Open Exclusive

You Can then set the password be performing the
First step..

This should fix your problem ..

Take Care
./Donny

samgiuoco

ASKER
thank you donny, but do you have any comment on the ISAM error i was having with that method?

my code is listed above
donnyr10

Got this off the Net..

a) Try the OLEDB Provider (if you not using it already):
www.adopenstatic.com/faq/whyOLEDB.asp

b) Also, have a look at the suggestion here:
http://www.aspfaq.com/show.asp?id=2142

c) If none of that works, then you may have corrupted MDAC components If you're using Win2k/XP you can boot
from the CD and choose to repair your existing installation. This will
revert your MDAC components back to MDAC v2.5 - then download and install
the latest components from www.microsoft.com/data/

You could (before trying the above), try installing the latest Jet Service
Pack (v6), just to see if that fixes the problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q282010&

d) Changing temp environment variables doesn't work (AFAIK) - Jet always
uses x:\winnt\temp\ so you'll need to give permissions to that folder, not
to whatever folder tmp and temp are set


**Credit will be given for this question if you can solve either one of these problems, or come up with a third solution that secures the database successfully. **

Glad we could help with the first part with the password
and hopefully this will fix your second problem as well :)

Hope this helps
Take Care
./Donny
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
samgiuoco

ASKER
Thank you, but once again, my problem has never been with setting up a database password or with setting up user based account security.

My problems are that

A.  User level security is incompetent because all you have to do to access the database is seperate it from the workgroup file or delete/rename it.. .basically its a joke.

B.  My code is not working with a database password.  I get an ISAM error.  


I was already fully up to date on these packages, but re-installed anyways to no avail.

The code string was working perfectly before I set the database-level password, and the "Database Password=myPass;" line.  Do you have any idea why this line of code would trigger an ISAM error?
samgiuoco

ASKER
ok well after some more google searching I found my problem.

I am going ot post it here since no answer was given in case someone else has this problem.

Apparently the "could not find an installable ISAM." error, does not always mean your ISAM drivers have anything to do with it.

You will also get the ISAM error if there is a syntax error in your connections string.

The line "Database Password=myPass;" was incorrect.
I needed to use "Jet OLEDB:Database Password= myPass;"

So for future reference.. ditch the user level security completely, set a database password, and use syntax like this:

  strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
  strconnectionstring = strconnectionstring & "Data Source= " & server.mappath(DATABASE) & ";"
  strconnectionstring = strconnectionstring & "Jet OLEDB:Database Password=myPass;"  
  Set cnn = Server.CreateObject("ADODB.Connection")
  cnn.Open strConnectionString
  Set Clients = Server.CreateObject("ADODB.Recordset")
  strSQL = "SELECT * FROM Clients" & SQLsort
  Clients.Open  strSQL, cnn  

this works perfectly
zenlion420

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ with points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

zenlion420
EE Page Editor
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
SpazMODic

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
MacRena

hello Sam,

thank you for stating "You will also get the ISAM error if there is a syntax error in your connections string."

i was getting this ISAM error on a line of code

     db.TableDefs.Append tdf

db was defined as

    Set db = OpenDatabase(gstrDatabaseDirectory & gstrDatabaseName)

so i played with it and changed the definition to

    Set db = DBEngine.Workspaces(0).OpenDatabase(gstrDatabaseDirectory & gstrDatabaseName)

and that solved it.  thanks for showing me the right tree to bark up!

mac