Help with a database server

[RavenGuard]

I've tried google for a few hours and I haven't found any information on this.

Here's my setup: MS Access is running on client computers. A server running Windows Server 2008 is hosting the database, and Access (from the clients) reads the information from the server over the network.

Ideally, I would like to be able to give Access permission to read from this database, but not allow the users to see the files outside of Access. I.E. the files should be invisible to the user, but Access should be able to read, display, and change database files.

A co-worker of mine told me that Access should behave on a network as if it were a user, and it should be able to be assigned it's own username/password combination, but this is only what I've been told and not something I can confirm.

Any help with this would be greatly appreciated! Thanks in advance!

 

[yinan]

I do not think access can do this. When access is ran it is ran from the context of the user that is running it. Access does not run as a service. What exactly is the Access application accessin on the server?

 

[KB]

By "Windows Server 2008 is hosting the database" do you mean SQL Server?

Yinan is right, Access runs under the credentials of the user so any database access by MS Access, is accessed as the user.

You do however have options. The first and not recommended solution would be to create an ODBC connection to the database server, then have Access use the ODBC and hardcode in the DSN the username/password to use: http://groups.google.com/group...hread/356e800b0126f43c

This isn't recommended because the password is plain text and stored on the users PC.

If this is SQL server: the other choice is to change the security on the database so that the users no longer have access to the database tables and instead grant an application role access to the tables. Then have access connect and connect using the application role.

http://support.microsoft.com/kb/308312

 

[RavenGuard]

Unfortunately no, the database files are simply being stored on the server, the server is doing nothing but following the client's read and write commands. It's a back end it terms of storage but there is not back end software running on this server that has anything to do with the database.

Thanks for the help so far. I may start looking into alternative ways of doing this. It'll be less ideal but if it's so much trouble it's not worth it.

 

[Nothinman]

Ideally, I would like to be able to give Access permission to read from this database, but not allow the users to see the files outside of Access. I.E. the files should be invisible to the user, but Access should be able to read, display, and change database files.

You might be able to get that by allowing thet users access to the mdb files directly but not giving them access to list the parent directory contents. Of course that's an incredibly dumb hack and should be avoided. Any potential damage that they could do is available inside of Access. Being able to see the files is a minor point.

 

[RavenGuard]

Originally posted by: Nothinman

Ideally, I would like to be able to give Access permission to read from this database, but not allow the users to see the files outside of Access. I.E. the files should be invisible to the user, but Access should be able to read, display, and change database files.

You might be able to get that by allowing thet users access to the mdb files directly but not giving them access to list the parent directory contents. Of course that's an incredibly dumb hack and should be avoided. Any potential damage that they could do is available inside of Access. Being able to see the files is a minor point.

I do agree here, but it's the way my brother would like it to be set up. He does keep often backups so it's not a huge deal. The backups are kept in two locations that aren't accessible to his employees.

 

[JDMnAR1]

A potential workaround, albeit kludgy as hell - and not truly secure as a determined/knowledgeable user would still be able to circumvent it - is to have an Access "shell" for your backend that has the stored credentials for your true backend. Secure that mdb so that users can't get to the source VBA, configs, etc, and make the connections to your data repository within that one. The scenario would be Access client with linked tables, etc. to secured "middleware", which in turn has links to the real backend. Keep in mind, I haven't actually ever worked on a project using this approach, so it may not work as expected.

 

[Nothinman]

I do agree here, but it's the way my brother would like it to be set up. He does keep often backups so it's not a huge deal. The backups are kept in two locations that aren't accessible to his employees.

Then explain to him the futility of what he wants to do. And actually I think it might be impossible since Access tends to create temporary files for locking, I'd bet that if it can't find them it barfs.

 

[JDMnAR1]

Originally posted by: Nothinman

I do agree here, but it's the way my brother would like it to be set up. He does keep often backups so it's not a huge deal. The backups are kept in two locations that aren't accessible to his employees.

Then explain to him the futility of what he wants to do. And actually I think it might be impossible since Access tends to create temporary files for locking, I'd bet that if it can't find them it barfs.

Yes, no matter what he does the user will have to have write access to the directory housing the mdb file, as Access will error out if it can't create an ldb file when it opens. That is why I said my approach wouldn't be secure - it would just obfuscate things somewhat.

 

[RavenGuard]

Yeah, I'm simply going to have a network login button on the desktop, and have it open Access as well as logging in without actually opening up the network directory. Then I'm going to see about getting Windows Explorer disabled.

Thanks for the help guys!