VBA client-server architecture security

Hi,

I have another architecture-related problem I'm trying to solve.

I'm creating an Excel VBA client application that connects to an Access server database via ADO. The app will be used by multiple users and each one will have a unique username/password. The database will be on a web server and must therefore be password-protected. If the username/password is stored in the database, it means that the Excel VBA login form must query the database to authenticate the user. However, to query the user table, I must access the database, which is password protected. My question then is how can I access the databse without storing the database password in the VBA code because VBA code is simply not secure, even if password-protected. What's an alternative solution for this process?

Thanks,
Sylvan

Since your connection string to the database is going to include the username/password within it, Excel might not be the best solution for you. If it is your only option, there are some third party apps for securing it. A google search should reveal some of those products.

However, I suppose my question would be:
Why go through all the trouble of creating each client a unique username/password to access the application, when the 'secure' data which resides in a database has a single sign-on setup for all clients? I would think the whole 'architecture' needs some additional thought put behind it vs thought placed around securing the VBA.

If you set up a database sign-in for each user, then you would not have to store the login/password within VBA. You could dynamically build a string from a login form when they go to access the application. The variable data would then be destroyed either at the end of a routine or upon closure of the app, thus no data would be hard-coded in the app.

Just my thoughts...

I like the idea of creating a different sign-in password to the database file for each user but it seems that would require creating accounts to the database. Do you know if Access supports that? Currently, there seems to be only one password for the .accdb file and that's the password I want to avoid hard-coding in the VBA code.