Multi-User Data App Architecture

[SavantMan]

I'm working on converting my application from a single user, to a multi-user. The current architecture is with an MSDE data back end (switching to SQLExpress soon, but not quite yet). Each user connects to the database directly - the connection string is stored encrypted in a local file, and all settings etc. are stored in the database. The problem is it seems that this could rapidly run into problems with scalability and security. The other issue is there are some files such as image files that are stored in the local file system and what is stored in the database is a path to the file. For example, if UserA add an image to a record, then UserB opens that record, they won't be able to see the image as the file is actually on UserA's machine. Any direction in where to go with this would be appreciated.

 

[RiverGuy]

1. Install the database on a server.
2. Adjust your application to use a dynamic security policy. Possibly creating another small app for the administrator to add/remove user's SQL permissions.
3. Store the images on the server itself.

 

[SavantMan]

Thanks for the response. As this is something I havent' done before, I'm not sure what you mean in step 2 -- I am certainly open to storing all images on the server itself - it's just getting to that point that is a little tricky. There are obvious problems with using shared folders on the server for this. I'm open to suggestions.

 

[RiverGuy]

What I mean by number 2 is that if you are encrypting a user name and password in the connection string - dont do it, but allow the user to type or it (you want each user to have a separate login), or use windows authentication (not sure if SQL Express supports that). Futhermore, its also better to keep settings to build a connection string that will allow you to change the name/IP address of the server and the database should the database be one day move or upgraded to a SQL Server proper installation.

If you can't allow all users access to all the images, then another option is to store them in the database itself-but I'm not sure if SQL Express has database size limitations which image datatypes could sure chew up.